Userform listbox reading from external excel

austria130 · #1 12-22-2022, 03:24 AM

Quote:

Originally Posted by gmayor

You seem to be doing OK so far. Populate Userform Combo Box demonstrates how to fill a listbox from Excel and Create a userform demonstrates how to populate a bookmark or content control from a userform selection.

Hey thank you for your links. but i am still stuck.

So i started with a new document as i was confused with all the trys and files i had haha.

What i have achieved so far:

I saved the document which would serve as a template for other people to use as .dotm
I put in the bookmarks at the place where i want the multiselect options to be printed after the user has chosen them

I have created a userform in this dotm document with 2 buttons and a listbox.
This is the code i have gotten from your links for my 2 buttons. As far as i understood the cancle button just hides it and closes it as nothing is happening. The ok button "would" (the listbox is not populated yet) print the chosen options to the bookmarks i have set "Prozess1bookmark" and "Prozess2bookmark ".

Code:

Option Explicit
Private Sub UserForm_Initialize()
cmdOK.Caption = "OK"
cmdCancel.Caption = "Cancel"
End Sub
Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdOK_Click()
'Hide the userform
Me.Hide
'Assign the values of the three text boxes to the three bookmarks
'Using the Function FillBM
FillBM "Prozess1bookmark", Me.TextBox1.Text
FillBM "Prozess2bookmark", Me.TextBox2.Text
'Unload the form
Unload Me
End Sub
End Sub

Private Sub ListBox1_Click()

End Sub

Also i put codes in the module section:

Code:

Option Explicit


Sub ShowMyForm()
UserForm1.Show
Unload UserForm1
End Sub
Public Sub FillBM(strBMName As String, strValue As String)
Dim oRng As Range
With ActiveDocument
On Error GoTo lbl_Exit
Set oRng = .Bookmarks(strBMName).Range
oRng.Text = strValue
oRng.Bookmarks.Add strBMName
End With
lbl_Exit:
Exit Sub
End Sub

As far as i understood, the first part shows me my userform when the document is opened and the 2nd part prints the bookmarks i have set before.

Not sure if everything until now is correct.

Unfortunately i dont understand the the next part in the guide which explains how to populate the listbox from an excel.

I have tried to put the following code from the guide to several places but cant find out where to put it. I also tried as said in the guide to install a macro via the link but this confuses me as i have never done this before.

Code:

xlFillList ListOrComboBox:=Me.ListBox1, _
iColumn:=2, _
strWorkbook:="C:\Path\WorkBookName.xlsx", _
strRange:="SheetName", _
RangeIsWorksheet:=True, _
RangeIncludesHeaderRow:=True

strWorkbook:="C:\Path\WorkBookName.xlsx", _ i had changed to the path where my excel is located at
i probably need to set the "sheetname" as well but that should be less a problem once i know where i paste this code

PS: i did not upload the file in here yet as i want to understand the proces with your explanations before doing so

thank you !

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Yet another Multi-Select Listbox in Userform question	Javir	Word VBA	4	09-24-2019 01:01 AM
Dynamic Userform multiselect listbox populate	trevorc	Excel Programming	1	12-03-2018 02:49 PM
How do I code ListBox and DatePicker in UserForm? - Word 2013	ickelly	Word VBA	4	08-05-2015 04:07 PM
This is a Userform LIstbox queston: A variable does not set to the value of a listbox	CatMan	Excel Programming	14	08-18-2014 08:14 PM
This is a Userform LIstbox queston: A variable does not set to the value of a listbox	CatMan	Excel	1	08-08-2014 09:41 AM