How to populate a userform combobox from an excel worksheet

jrooney7 · #1 09-09-2018, 03:19 PM

Hello all,

I am been researching this topic, and being new to vba, I'm having trouble figuring out how to modify existing examples to my purposes. I have a document template containing a userform: "UserForm1". This userform has two comboboxes: "HowCleanedComboBox" and "CaliberComboBox". I would like to populate these comboboxes from an Excel worksheet "Dropdowns.xlsm" that contains the named ranges "HowCleaned" and "Caliber" at columns C and I, respectively.

I have inserted the following code, but I have a suspicion that I am barely scratching the surface of what I'll need to do :/

Code:

Private Sub UserForm_Initialize()

xlFillList ListOrComboBox:=Me.HowCleanedComboBox, _
iColumn:=3, _
strWorkbook:="C:\Users\Public\Documents\FA worksheet changes\Word Worksheets\Dropdowns.xlsm", _
strRange:="HowCleaned", _
RangeIsWorksheet:=True, _
RangeIncludesHeaderRow:=True

xlFillList ListOrComboBox:=Me.CaliberComboBox, _
iColumn:=9, _
strWorkbook:="C:\Users\Public\Documents\FA worksheet changes\Word Worksheets\Dropdowns.xlsm", _
strRange:="Caliber", _
RangeIsWorksheet:=True, _
RangeIncludesHeaderRow:=True

End Sub

When I try to run the userform, I get this error: "Compile error: Sub or function not defined." at the very beginning (the Private Sub line). Any help (in small words) would be greatly appreciated!!

jrooney7 · #2 09-09-2018, 03:24 PM

I should also have mentioned, I call the userform with a macro button in the Quick Access Toolbar. That macro is simply:

Code:

Sub Table_Test()
'
' Table_Test Macro
'
'
    UserForm1.Show
    
       
End Sub

Guessed · #3 09-09-2018, 04:11 PM

Do you have another Sub or Function called xlFillList ? That appears to be missing in the code you posted.

If you don't have that macro, you could try a more direct method, assuming the userform is contained in the workbook where the data is sitting.

Me.CaliberComboBox.RowSource="Sheet1!A1

10"
or
Me.CaliberComboBox.RowSource="Caliber"

jrooney7 · #4 09-09-2018, 04:16 PM

I don't have that function - is that the "third box" in this link?:

http://www.gmayor.com/Userform_ComboBox.html

But I don't know where to put that code. Also, the userform is in my Word document, and the data for the combobox is in an excel file.

macropod · #5 09-09-2018, 07:53 PM

The link you posted has a Section titled Populate a list box or combo box from an Excel worksheet. Use the instructions there and adapt the code to your "HowCleanedComboBox" and "CaliberComboBox" needs. If you need it, they include a link with advice on how to install a macro.

jrooney7 · #6 09-10-2018, 06:24 AM

Thanks, but I have tried to follow the instructions in the link. My original post has some of that code modified for my userform, but I am having trouble with the specifics. Where do I put the function code found in the third box. I have tried various spots, but can't get it to work...

macropod · #7 09-10-2018, 02:45 PM

Have you included the function that fills the list of combo box with the columns from the worksheet/range? I can't see any indication of that from your previous posts. It's in the last code box on that page. It pays to read the entire article...

jrooney7 · #8 09-10-2018, 03:50 PM

Quote:

Originally Posted by macropod

Have you included the function that fills the list of combo box with the columns from the worksheet/range? I can't see any indication of that from your previous posts. It's in the last code box on that page. It pays to read the entire article...

As I stated, I have read that article, and tried multiple times, but I don't know WHERE to insert that function code. I came here looking for help and could do without the snark.

gmayor · #9 09-10-2018, 08:37 PM

The web page contains a link to http://www.gmayor.com/installing_macro.htm

the xlfilllist function goes in a new ordinary module in the same document/template as the userform.

macropod · #10 09-10-2018, 10:26 PM

Quote:

Originally Posted by jrooney7

As I stated, I have read that article, and tried multiple times, but I don't know WHERE to insert that function code.

The article says clearly enough:

Quote:

Copy it to a new vba module and call it from the userform initialisation or from the macro that calls the userform with a command line similar to the following.

jrooney7 · #11 09-15-2018, 01:43 PM

OK, I think I have the code pasted in the right areas. When I try to run the userform, I get an error that I don't understand. I modified your code like this:

Code:

xlFillList ListOrComboBox:=Me.LabMarkingsComboBox, _
iColumn:=1, _
strWorkbook:="C:\Users\Public\Documents\FA worksheet changes\Word Worksheets\Dropdowns.xlsm", _
strRange:="LabMarkings", _
RangeIsWorksheet:=True, _
RangeIncludesHeaderRow:=True

This references a named range in my excel workbook called "LabMarkings". But the error I get is "Run-time error '-2147467259 (80004005)': 'LabMarkings$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long."

I'm not sure where it's adding the $ symbol? I assume that's the problem?

gmayor · #12 09-15-2018, 08:03 PM

If you are using a named range rather than a worksheet you need to tell the macro.

You have RangeIsWorksheet set as True, when it should be False and if the named range doesn't include a header row you need to also set RangeIncludesHeaderRow to False.

This was incorrectly shown on the web site and has been fixed.

jrooney7 · #13 09-16-2018, 12:51 PM

Gotcha! I made the change, but now I'm getting this error:

"Run-time error '-2147217865 (80040e37)': The Microsoft Access database engine could not find the object 'LabMarkings'. Make sure the object exists and that you spell its name and the path name correctly. If 'LabMarkings' is not a local object, check you network connection or contact the server administrator."

I verified the named range and its spelling, so I'm not sure why it's having trouble finding it. I tried the using a named worksheet method and did get that to work! I would like get the named range method to work, if nothing else, to not feel like I've been beaten!

Thank you so much for your help!

jrooney7 · #14 09-16-2018, 07:12 PM

With the comboboxes populating correctly from my excel worksheet, I am now running into a different issue. I posted this on another thread, and I'm sorry for the redundancy, but now I'm wondering if it may be related to or interrupted by this excel population code.

I have a userform with multiple textboxes and comboboxes. I have set some to be hidden in the userform initialize event, and then want them to become visible if certain selections are made in the userform comboboxes. So if the user selects "Other/Multiple" from this dropdown, which has been populated from the excel file, a corresponding textbox should appear. But nothing is happening. The example below is from the same userform in Excel, and it works there, but it is not working in Word. Any thoughts?

Code:

Private Sub SafetiesComboBox_Change()
    Select Case SafetiesComboBox.Value
    
    Case "Other/Multiple"
        FirearmPreFireUserForm.SafetiesTextBox.Visible = True
        
    Case Else
        FirearmPreFireUserForm.SafetiesTextBox.Visible = False
        
End Select

End Sub

gmayor · #15 09-16-2018, 08:52 PM

Rather than try and second guess what is happening, send the template and the workbook to the contact link on my web site and I will see what is happening ... or not.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Populate Combobox from Excel into a Word UserForm	ferguson4848	Word VBA	3	10-28-2016 09:05 PM
Userform calls other userform, then populate worksheet	Lehoi	Excel Programming	0	02-03-2016 02:58 PM
Using combobox to pass data from an Excel spread sheet to text boxes on userform	Stewie840	Word VBA	14	12-17-2015 10:13 PM
Trying to populate Word bookmarks with excel UserForm	smd1112	Excel Programming	7	09-03-2014 09:42 PM
VB code: populate combobox from columns in Excel file	billybeach	Outlook	2	04-27-2013 04:38 AM