CODEWITHSUNDEEP
excelcomboboxuserform
Stildawn
Stildawn

Reputation: 31

Excel Userform Combobox Properties Rowsource box issues?

I have a userform in Excel that works as a calculator.

In this userform I have two ComboBoxs (1 & 2)

In VBA editor, with ComboBox1 selected, In Properties, under Rowsourse I have: Sheet1!a4:a5

In Sheet1, A4 = Auckland and A5 = Christchurch

This is fine and when I run the userform there is a drop down arrow with the two options (Auckland or Christchurch).

However my problem is that when you open this workbook I have a VBA command to hide it from the users sight, leaving them only the userform to work with which is what is desired.

The issue is that if you have another workbook open then open this calculator workbook (which automatically hides itself). Then the combobox list is populated by Sheet1!a4:a5 on the other workbook that was already open, not the workbook that actually contains "Auckland" & "Christchurch" from which the userform is from.

I have tried making the Rowsource for the comboboxes more specific by putting the following in the rowsource box in properties: [book1.xlsm]sheet1!a4:a5 but this comes up with a "Invalid Property Value" error message.

I have also tried making a:

Private Sub Userform1_Initialize()

ComboBox1.Additem "Auckland"
ComboBox1.Additem "Christchurch"

End Sub

And also tried this:

Private Sub Userform1_Initialize()

ComboBox1.RowSource = Workbooks("book1.xlsm").Sheets("Sheet1").Range("a4:a5").Value

End Sub

However with both codes when it opens and runs now the comboboxes are empty and there is no list.

I think the easist solution would be to somehow put the full path (including workbook name) into the rowsource box under properties. But I must be missing something as its coming up with that error for me?

All help would be greatly appreciated.

Thanks

Upvotes: 3

Views: 75452

Answers (3)

Asthon John
Asthon John

Reputation: 1

You may try adding this code on userform:

Private Sub UserForm_Initialize()
ComboBox1.list = Array("Auckland","Christchurch")
End Sub

Then set Combobox propert "MatchEntry" to "1".

Upvotes: 0

L42
L42

Reputation: 19737

You are missing ' in your full path row source.
It should be like this:

Me.ComboBox1.RowSource = "'[book1.xlsm]Sheet1'!$A$4:$A$5"

I have similar question that can be found HERE.

Upvotes: 3

user2641468
user2641468

Reputation: 21

Set the row source property of the combobox as: SheetName!$Col$Row:$Col$Row, e.g.: Location!$A$1:$A$3.

Upvotes: 2

Related Questions