Last week I had to deal with Visual Basic in Microsoft Visio Professional 2016. I wanted to fill a ListBox with items containing data from an Excel file. First I […]
Last week I had to deal with Visual Basic in Microsoft Visio Professional 2016. I wanted to fill a ListBox with items containing data from an Excel file. First I searched for a ListBox shape in the shapes pane of Visio and added a ListBox shape to the sheet.
I first couldn’t figure out, why accessing and filling the ListBox in Visual Basic didn’t work. I almost despaired until I figured out, that the developer tab was not activated. After activating the developer tab I realized, that there is an item to insert different controls (ActiveX controls) including a ListBox control.
From then on, my problems solved themselves. I added the ListBox control to the sheet and in the context menu of the ListBox control the
View Code item was available and I also was able to access the ListBox control in Visual Basic code.
Below you find the code that was used to fill the ListBox with items from the Excel file when clicking on a specific button.
Important: In code editor you have to add reference
Microsoft Excel 16.0 Object Library under
Private Sub BtnFillListBox_Click() FillListBox End Sub Private Sub FillListBox() On Error GoTo ErrHandler Dim xlApp As New Excel.Application Dim xlWB As Excel.Workbook Dim thisPath As String xlApp.ScreenUpdating = False ‘Open the Excel workbook in “READ ONLY MODE”, which has to be in the same directory as the Visio file. thisPath = ThisDocument.Path thisPath = thisPath & “FILENAME.xlsx” Set xlWB = xlApp.Workbooks.Open(thisPath, True, True) Dim worksheet As Excel.worksheet Set worksheet = xlWB.Worksheets(“WORKSHEET_NAME”) ‘Get the total rows from the workbook Dim iTotalRows As Integer iTotalRows = worksheet.Range(“A1:A” & Cells(Rows.Count, “B”).End(xlUp).Row).Rows.Count ‘Find cell containing a specific text (ROWHEADER is a placeholder) Dim rgFound As Excel.Range Set rgFound = Cells.Find(What:=”ROWHEADER”, _ After:=Range(“A1”), _ LookAt:=xlWhole, _ LookIn:=xlValues, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=True) If (Not rgFound Is Nothing) Then ‘Load all entries of row below specified header Dim rangeWithItems As Excel.Range Set rangeWithItems = worksheet.Range(Col_Letter(rgFound.Column) & (rgFound.Row + 1) & “:” & Col_Letter(rgFound.Column) & (iTotalRows – rgFound.Rows.Count)) ‘Clear list box ArbitraryListBox.Clear ‘Add items from Excel worksheet to list box For Each c In rangeWithItems.Cells With c ArbitraryListBox.AddItem .Value ‘Debug.Print .Address & “:” & .Value End With Next c End If ‘Close the Excel workbook xlWB.Close False Set xlWB = Nothing ErrHandler: xlApp.EnableEvents = True xlApp.ScreenUpdating = True End Sub