Using Combo Box to modify existing data in Excel?

Question

Using Combo Box to modify existing data in Excel?

Answer 1

Rainbows didn't exist here on Earth till there were enough Water Molecules in the Atmosphere to bend the Sunlight into a Rainbow. Plus it is common scientific knowledge that Earth didn't ALWAYS have water on it. So theoretically if the atmosphere before the Flood didn't have enough water in it to produce a Rainbow visible to the Human Eye, and the water that caused the Flood was introduced to Earth by Comets, there would then be enough water to produce a visible Rainbow.

Answer 2

First, make show the Control toolbar then draw a ComboBox to the appropriate place.

Then click the Control toolbar's View Code button.

You must populate the ComboBox first:



Private Sub Workbook_Open()

Sheet1.ComboBox1.AddItem ("Default")

Sheet1.ComboBox1.AddItem ("$'000")

End Sub



Then you can enter the code:



Private Sub ComboBox1_Change()

If ComboBox1.Value = "$'000" Then

For i = 1 To 50

For g = 1 To 5

Cells(i, g).Activate

ActiveCell.Value = ActiveCell.Value / 1000

Next

Next

End If

End Sub



Have a lot of fun!

Answer 3

You can do this in two ways:

One: Using Macro.

Two: Using functions



I prefer using the option Two as a start.

Now, I need you to focus with me...



- Enter your two values "Default" and "1000" in two cells (Say Y1:Y2)

- Create a Combo Box using "Forms Toolbar", that is go to View > Toolbars > Forms

- The toolbar will show and you can create a combo box from it.

- Right click on that combo box and go to Properties > Control, select two cells that have your values (Default and '1000') Y1:Y2 as the Input range, select a cell to link this combo box to (Say Z1).

- Now click ok

- Where ever you select a value from this combo box, Z1 cell will change to 1 or 2.

- Your A1:E50 cells need to have this function, need to be changed. I have a question here (Do these cells have a numbers (A) or coming from a formulas (B) ?)

A) If the A1:E50 cells have all constant numbers, I prefer you choose another range to put this formula in.

Say G1:K50, put in G1 this formula and copy and paste to the others:

= CHOOSE( $Z$1, A1, A1/1000)

B) If they have a formula, just change it to this formula:

= CHOOSE( $Z$1, [your old formula], [your old formula] /1000)

don't forget to change the value [your old formula] to your old formula on each cell.



Need more help? or would like to see the Macro section?

mail me here



Enjoy my profile, I am VBAXLMan