I have an excel spreadsheet that is using the Forms Dropdown box and it gets
its data from a range of cells. I would like to know how to capture the
value of the text when it changes to something else. I would like to use VBA
to do this. I know if I used the Controls dropdown box that would be easier
but this is how this one is.

If there is no way that I can do that can someone tell me how to set the
Controls Dropdown to accomplish the same thing the form control is doing.

Assign the dropdown macro to

Msgbox ActiveSheet.DropDowns("Drop Down 1").Value

will work as long as the value is changed by clicking the dropdown, not by
changing the linked cell that changes the value in the control.


"Larry Dodd" wrote in message
...
I have an excel spreadsheet that is using the Forms Dropdown box and it
gets
its data from a range of cells. I would like to know how to capture the
value of the text when it changes to something else. I would like to use
VBA
to do this. I know if I used the Controls dropdown box that would be
easier
but this is how this one is.

If there is no way that I can do that can someone tell me how to set the
Controls Dropdown to accomplish the same thing the form control is doing.

Assuming your Forms dropdown has its Link cell set to A1, and the input range
is set to "myList",

then in cell B1 enter:
=INDEX(myList,A1)

This will convert the number value of cell A1 back into the actual value
from your named range.

The controls dropdown bypasses this step. You set the Linked Cell in the
control Properties to A1 and the actual value from the list is returned to
the LinkedCell.


"Larry Dodd" wrote:

I have an excel spreadsheet that is using the Forms Dropdown box and it gets
its data from a range of cells. I would like to know how to capture the
value of the text when it changes to something else. I would like to use VBA
to do this. I know if I used the Controls dropdown box that would be easier
but this is how this one is.

If there is no way that I can do that can someone tell me how to set the
Controls Dropdown to accomplish the same thing the form control is doing.

You could always set the linked cell of the control, and read that value.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Larry Dodd" wrote in message
...
I have an excel spreadsheet that is using the Forms Dropdown box and it
gets
its data from a range of cells. I would like to know how to capture the
value of the text when it changes to something else. I would like to use
VBA
to do this. I know if I used the Controls dropdown box that would be
easier
but this is how this one is.

If there is no way that I can do that can someone tell me how to set the
Controls Dropdown to accomplish the same thing the form control is doing.

The Forms dropdown returns its listindex (1-based) into the linked cell, not
its text. So this requires either an INDEX formula in a helper cell or a VBA
macro assigned to the dropdown:

Sub DropClick()
ActiveSheet.Range("B2").Value = _
ActiveSheet.DropDowns(1).List(ActiveSheet.DropDown s(1).Value)
End Sub

HTH. Best wishes Harald

"Bob Phillips" skrev i melding
...
You could always set the linked cell of the control, and read that value.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Larry Dodd" wrote in message
...
I have an excel spreadsheet that is using the Forms Dropdown box and it
gets
its data from a range of cells. I would like to know how to capture the
value of the text when it changes to something else. I would like to use
VBA
to do this. I know if I used the Controls dropdown box that would be
easier
but this is how this one is.

If there is no way that I can do that can someone tell me how to set the
Controls Dropdown to accomplish the same thing the form control is
doing.