Userform listbox reading from external excel

[austria130]

Hi guys,



unfortunately i do not know how to code and never worked with VBA. I spent several hours of reading and watching documents/tutorials/videos about this but cant figure out how to get the results i wish for.

I tried to follow the tutorial here: Create & Employ a Userform

I also clicked through and found exactly a guide on populating from external excel worksheet but still failed...

What i want:

I have a word template saved as .dotm which has several parts and is filled from many different people.
In one part i want a dropdown field where people can choose multiple selection (eg. item1 and item2 etc.). This multiselection should be printed into the spot in the document (i heared that it is easiest setting a bookmark to the spot i wish the output being printed to). The population of the listbox (dropdown) should be automatically retreived from an external excel with 1 column but many rows (n=100-300 entries).



What i managed so far:

Set a bookmark in the document i want my output at (bookmark name= bookmark1)
Created a userform with an "ok" and "cancel" button and a listbox

My Cancel command button

Private Sub cmdCancel_Click()
Me.Hide
End Sub

Module code to show the userform

Sub CallUF()
Dim oFrm As frmSurvey
  Set oFrm = New frmSurvey
  oFrm.Show
  Unload oFrm
  Set oFrm = Nothing
lbl_exit:
  Exit Sub
End Sub

And the rest was just trial and error by copying from multiple sources and nothing worked for me

Can anyone do a a step by step guide in plain language for non coders. Also i would like to understand the code for future adaptation

Thank you very much !

[Guessed]

Can you post Word and Excel docs if you want help that applies directly to what you have already done?

[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.

[austria130]

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 ".

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:

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.

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 !

[gmayor]

I think the attached will explain how the various bits go together. Put the two files from the zip in the same folder then create a new document from the template.

[austria130]

Originally Posted by gmayor

I think the attached will explain how the various bits go together. Put the two files from the zip in the same folder then create a new document from the template.

Thank you. Unfortunately im kinda lost on using templates and vba...after all i am very confused.

Therefore i am uploading a test-document in here as a docx file.


Notice: this document will be uploaded to a server where several users can download the word and should be able to see the processes in there. Is that even possible as the excel will not be on their machine? (however, this is not a big issue)

What i want to achieve is that where i have my "BOOKMARK1" in the document the user can open a dropdown list and choose multiple processes which are in a column of an external Excel. As i found out, word does not offer multiple selection via the simple dropdown therefore i need to implement a userform into that word document?

All the code lines make no sense for me as a beginner as they are not comented one by one :S

Maybe there is another simpler solution to this?

[gmayor]

Templates are an essential part of using Word. VBA and userforms less so, but easy enough to follow when you have the code provided. See again Create a userform and Populate Userform Combo Box

I have modified the example to use your document content, which apart from the table is almost identical to the original I posted..
Put the contents of the zip in the same folder then create a new document from the template (File > New). I have also annotated relevant parts of the code.

[austria130]

Originally Posted by gmayor

Templates are an essential part of using Word. VBA and userforms less so, but easy enough to follow when you have the code provided. See again Create a userform and Populate Userform Combo Box

I have modified the example to use your document content, which apart from the table is almost identical to the original I posted..
Put the contents of the zip in the same folder then create a new document from the template (File > New). I have also annotated relevant parts of the code.

Hey it was much clearer now.

so i got back to my document and i adapted the userform (only a listbox, okbutton and cancel button), also i activated multiselect for the listbox.
I adapted the code in the module and populating from the excel also works.

I feel like i ALMOST got it (due to your help and patience!).


The only problem i have is when i select my options from the listbox and click the ok button i get an error. Debugging highlights this line of code for me:

If .Tag = 1 Then 'OK button pressed

it is a part of this code in the module:

Sub ShowMyForm()
Dim oFrm As UserForm1
Dim i As Integer
Dim strWorkbook As String
    'the workbook path and name
    strWorkbook = "C:\Users\censored\Desktop\Testen\Tabellemitprozesse.xlsx"
    'Give the userform a name the macro will use
    Set oFrm = New UserForm1
    With oFrm
        'Call the function to fill the listbox with the workbook sheet 1 column 1
        xlFillList .ListBox1, 1, strWorkbook, "Tabelle1", True, True
        With .ListBox1
            .MultiSelect = fmMultiSelectExtended
            .ListIndex = -1
        End With
        .Show
        If .Tag = 1 Then 'OK button pressed
            FillBM "BM1", .ListBox1.Text 'Fill bookmark BM1 with the text box content
            With .ListBox1
                'locate the selected item from the list box and enter it in bookmark BM1
                For i = 1 To .ListCount - 1
                    If .Selected(i) = True Then
                        FillBM "BM1", .List(i)
                        Exit For
                    End If
                Next i
            End With
        End If
    End With
    Unload oFrm
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

Public Function xlFillList(ListOrComboBox As Object, _
                           iColumn As Long, _
                           strWorkbook As String, _
                           strRange As String, _
                           RangeIsWorksheet As Boolean, _
                           RangeIncludesHeaderRow As Boolean, _
                           Optional PromptText As String = "[Select Item]")

'Graham Mayor - http://www.gmayor.com - Last updated - 20 Sep 2018
'ListOrComboBox is the name of the list or combo box to be filled
'iColumn is the column in the sheet (or the range) to be displayed in the list/combo box
'strWorkbook is the full path of the workbook containing the data
'strRange is the name of the worksheet or named range containing the data
'RangeIsWorksheet - set to True if the range 'strRange' is a worksheet
' or False if it is a named range
'RangeIncludesHeaderRow - Set to True is the Worksheet or named range contains a header row
'PromptText - Use a text string here to add your preferred prompt text to a combobox.
'The PromptText is not used for ListBoxes.

Dim RS As Object
Dim CN As Object
Dim numrecs As Long, q As Long
Dim strWidth As String

    If RangeIsWorksheet = True Then
        strRange = strRange & "$]"
    Else
        strRange = strRange & "]"
    End If

    Set CN = CreateObject("ADODB.Connection")


    If RangeIncludesHeaderRow Then
        CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                  "Data Source=" & strWorkbook & ";" & _
                                  "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    Else
        CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                  "Data Source=" & strWorkbook & ";" & _
                                  "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""
    End If


    Set RS = CreateObject("ADODB.Recordset")
    RS.CursorLocation = 3

    RS.Open "SELECT * FROM [" & strRange, CN, 2, 1    'read the data from the worksheet

    With RS
        .MoveLast
        numrecs = .RecordCount
        .MoveFirst
    End With

    With ListOrComboBox
        .ColumnCount = RS.Fields.Count
        If RS.RecordCount > 0 Then
            .Column = RS.GetRows(numrecs)
        End If

        strWidth = vbNullString
        For q = 1 To .ColumnCount
            If q = iColumn Then
                If strWidth = vbNullString Then
                    strWidth = .Width - 4 & " pt"
                Else
                    strWidth = strWidth & .Width - 4 & " pt"
                End If
            Else
                strWidth = strWidth & "0 pt"
            End If
            If q < .ColumnCount Then
                strWidth = strWidth & ";"
            End If
        Next q
        .ColumnWidths = strWidth
        If TypeName(ListOrComboBox) = "ComboBox" Then
            .AddItem PromptText, 0
            If Not iColumn - 1 = 0 Then .Column(iColumn - 1, 0) = PromptText
            .ListIndex = 0
        End If
    End With
lbl_Exit:
    'Cleanup
    If RS.State = 1 Then RS.Close
    Set RS = Nothing
    If CN.State = 1 Then CN.Close
    Set CN = Nothing
    Exit Function
End Function

It might be a problem as i removed the textbox which was initially set up in your userform. i adapted the code of line with fillbm "bm1" to listbox1 instead of messagebox1.


thank you !

[austria130]

thx for your patience!

[gmayor]

Unfortunately you can't adapt the code as you have done, with regard to the list box. If you want to insert multiple values from the list box then you must add each selected value to the bookmark. There are a number of ways of doing this, but the simplest is to add them to a text string and then add the text string to the bookmark e.g. as follows.

Don't forget to declare the string strText in the DIM statements at the top of the macro!

I assume that you removed any reference to the text box from the userform code.Debug > Compile Project should reveal any glaring coding errors.

Have a good Christmas.

.Show
        If .Tag = 1 Then    'OK button pressed
            With .ListBox1
                'locate the selected items from the list box and add to a string
                For i = 0 To .ListCount - 1
                    If .Selected(i) = True Then
                        If strText = "" Then
                            strText = strText & .List(i)
                        Else
                            strText = strText & vbCr & .List(i)
                        End If
                    End If
                Next i
            End With
            FillBM "BM1", strText
        End If

[austria130]

Originally Posted by gmayor

Unfortunately you can't adapt the code as you have done, with regard to the list box. If you want to insert multiple values from the list box then you must add each selected value to the bookmark. There are a number of ways of doing this, but the simplest is to add them to a text string and then add the text string to the bookmark e.g. as follows.

Don't forget to declare the string strText in the DIM statements at the top of the macro!

I assume that you removed any reference to the text box from the userform code.Debug > Compile Project should reveal any glaring coding errors.

Have a good Christmas.

.Show
        If .Tag = 1 Then    'OK button pressed
            With .ListBox1
                'locate the selected items from the list box and add to a string
                For i = 0 To .ListCount - 1
                    If .Selected(i) = True Then
                        If strText = "" Then
                            strText = strText & .List(i)
                        Else
                            strText = strText & vbCr & .List(i)
                        End If
                    End If
                Next i
            End With
            FillBM "BM1", strText
        End If

As far as i know i deleted everything that belonged to the text box.
I declared the strText as string in the start with Dim strText as String. When i run the macro i can choose and multiselect the options in the populated listbox. But once i click ok i get an error. When i click the debug button this line of code is highlighted:

If .Tag = 1 Then    'OK button pressed

My Whole Code in the module:

Option Explicit
Sub ShowMyForm()
Dim oFrm As UserForm1
Dim i As Integer
Dim strText As String
Dim strWorkbook As String
    'the workbook path and name
    strWorkbook = "C:\Users\censored\Desktop\Testen\Tabellemitprozesse.xlsx"
    'Give the userform a name the macro will use
    Set oFrm = New UserForm1
    With oFrm
        'Call the function to fill the listbox with the workbook sheet 1 column 1
        xlFillList .ListBox1, 1, strWorkbook, "Tabelle1", True, True
        With .ListBox1
            .MultiSelect = fmMultiSelectExtended
            .ListIndex = -1
        End With
       .Show
            If .Tag = 1 Then    'OK button pressed
            With .ListBox1
                'locate the selected items from the list box and add to a string
                For i = 0 To .ListCount - 1
                    If .Selected(i) = True Then
                        If strText = "" Then
                            strText = strText & .List(i)
                        Else
                            strText = strText & vbCr & .List(i)
                        End If
                    End If
                Next i
            End With
            FillBM "bm1", strText
        End If
    End With
    Unload oFrm
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

Public Function xlFillList(ListOrComboBox As Object, _
                           iColumn As Long, _
                           strWorkbook As String, _
                           strRange As String, _
                           RangeIsWorksheet As Boolean, _
                           RangeIncludesHeaderRow As Boolean, _
                           Optional PromptText As String = "[Select Item]")

'Graham Mayor - http://www.gmayor.com - Last updated - 20 Sep 2018
'ListOrComboBox is the name of the list or combo box to be filled
'iColumn is the column in the sheet (or the range) to be displayed in the list/combo box
'strWorkbook is the full path of the workbook containing the data
'strRange is the name of the worksheet or named range containing the data
'RangeIsWorksheet - set to True if the range 'strRange' is a worksheet
' or False if it is a named range
'RangeIncludesHeaderRow - Set to True is the Worksheet or named range contains a header row
'PromptText - Use a text string here to add your preferred prompt text to a combobox.
'The PromptText is not used for ListBoxes.

Dim RS As Object
Dim CN As Object
Dim numrecs As Long, q As Long
Dim strWidth As String

    If RangeIsWorksheet = True Then
        strRange = strRange & "$]"
    Else
        strRange = strRange & "]"
    End If

    Set CN = CreateObject("ADODB.Connection")


    If RangeIncludesHeaderRow Then
        CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                  "Data Source=" & strWorkbook & ";" & _
                                  "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    Else
        CN.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                  "Data Source=" & strWorkbook & ";" & _
                                  "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""
    End If


    Set RS = CreateObject("ADODB.Recordset")
    RS.CursorLocation = 3

    RS.Open "SELECT * FROM [" & strRange, CN, 2, 1    'read the data from the worksheet

    With RS
        .MoveLast
        numrecs = .RecordCount
        .MoveFirst
    End With

    With ListOrComboBox
        .ColumnCount = RS.Fields.Count
        If RS.RecordCount > 0 Then
            .Column = RS.GetRows(numrecs)
        End If

        strWidth = vbNullString
        For q = 1 To .ColumnCount
            If q = iColumn Then
                If strWidth = vbNullString Then
                    strWidth = .Width - 4 & " pt"
                Else
                    strWidth = strWidth & .Width - 4 & " pt"
                End If
            Else
                strWidth = strWidth & "0 pt"
            End If
            If q < .ColumnCount Then
                strWidth = strWidth & ";"
            End If
        Next q
        .ColumnWidths = strWidth
        If TypeName(ListOrComboBox) = "ComboBox" Then
            .AddItem PromptText, 0
            If Not iColumn - 1 = 0 Then .Column(iColumn - 1, 0) = PromptText
            .ListIndex = 0
        End If
    End With
lbl_Exit:
    'Cleanup
    If RS.State = 1 Then RS.Close
    Set RS = Nothing
    If CN.State = 1 Then CN.Close
    Set CN = Nothing
    Exit Function
End Function

[gmayor]

Can you post the document?

[austria130]

here u go

i "censored" the path of the workbook on purpose

[gmayor]

Assuming your workbook path and sheet names are correct it now works.

[austria130]

Originally Posted by gmayor

Assuming your workbook path and sheet names are correct it now works.

Hi,

this worked perfectly. I used the code and adapted it to my needs for other documents and it works perfectly. Can you explain me what was not working correctly? i see that the autoform part is new and you changed the ok button commands as well.


thank you for your help and your patience!