VB for Drop Down list in Excel spreadsheet

InMyWoodenHut · November 3rd, 2004, 10:24 am

Hi, I have a drop down list in my spreadsheet, and I would like to get the selected value. I associated the following macro to it, but it does not get the "DropDown1.value " : Sub DropDown1_change()MsgBox ("Index1 changed")Sheets("Sheet1").range("A1").Value = "Index : " & DropDown1.valueEnd Sub Any help on the syntax appreciated.

GEL · November 3rd, 2004, 12:01 pm

is this what you mean?Private Sub Listbox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)Dim BoxVal As String BoxVal = ListBox1.Value Sheets("Sheet1").Cells(1, 1) = BoxVal Unload MeEnd Sub

InMyWoodenHut · November 3rd, 2004, 12:32 pm

Thanx, that is the idea but mine is not a listbox, it is a dropdown box. Besides, the box is directly in my excel sheet, not in a userform, does that change anything ?

GEL · November 3rd, 2004, 1:50 pm

I do not think that a dropdown box is an object that can be "seen" in VBA.However, the worksheet is:In the VBA Editor, select Sheet1, click the left downbutton and select 'Worksheet', and the right downbutton click Change and the following Private Sub appears. All you have to do is add my lines of text. Assume the dropdown box is at C3Private Sub Worksheet_Change(ByVal Target As Range))If Target.Address = "$C$3" Then Sheets("Sheet2").Cells(1, 1).Value = Target.TextEnd IfEnd SubHow's that?

InMyWoodenHut · November 3rd, 2004, 2:21 pm

Thanx, Does not work on my PC... I have to check a few other possibilities.

InMyWoodenHut · November 3rd, 2004, 2:26 pm

QuoteOriginally posted by: InMyWoodenHutThanx, Does not work on my PC... I have to check a few other possibilities.actually it does work : GREAT ! BUT not if the range that is changed contains a Scroll down list (using data / validation ...) which is my case... oh well...

GEL · November 4th, 2004, 7:10 am

Well, I checked it with a dropdown box made with data/validation, and it DOES work. have you checked the Target.Value is absolute ?You can see what is returned by testing with a Debug.Print Target.Value to make sure that your test "If... Then... Else" works correctly.