I'm trying to populate a List Box (ActiveX control). This list box is on a sheet labeled "Dashboard" of my workbook, and not a user form. I want to populate it with a range from a sheet labeled "Data".
My problem is that if I populate it on the workbook open event procedure, I get an error when the workbook opens that it "Can't execute in break mode." However, there aren't breakpoints active at all.
If I populate it on the "Dashboard" worksheet active event procedure, it won't populate when the workbook is open. It only populates if I click on another worksheet, and then go back to the Dashboard worksheet, then it will populate.
Is there a better way to populate the list box so that it's always populated and ready to go? I have a lot of vLookup functions that are associated with the list box, and if the list box is not populated, then the rest of my code won't work.
I will post my codes that I have so far. The first is when I attempt to populate the listbox through the workbook_open even procedure. The second is through the "Dashboard" worksheet activate procedure.
Private Sub Workbook_Open()
Dim strName As String
Dim blDone As Boolean
Dim cPlanets As MSForms.ListBox
Dim vArray As Variant
Dim shtData As Worksheet
Dim wkbSolarSystem As Workbook
Set wkbSolarSystem = Application.Workbooks("workbookname.xlsm")
Set shtData = wkbSolarSystem.Worksheets("Data")
Set cPlanets = wkbSolarSystem.Worksheets("Dashboard").lstPlanets
vArray = shtData.Range("Planets").value
cPlanets.List = vArray
cPlanets.ListIndex = 3
'input box message for user when workbook opens up
strName = InputBox("Hello! Please enter your name", "Welcome!")
'check if there is a name entered via loop and if statement
Do
If Len(strName) = 0 Then
'if no name entered, ask user again
MsgBox ("Please enter a valid name to continue"), vbCritical, "Valid Name Required"
'ask user to type in name again
strName = InputBox("Hello! Please enter your name", "Welcome!")
Else
'display message with information for user
MsgBox ("Hello, " & strName)
blDone = True
End If
'finish loop statement
Loop Until blDone = True
This next code is the one I have on Sheet3 code worksheet activate procedure
Private Sub Worksheet_Activate()
Dim shtData As Worksheet
Dim wkbSolarSystem As Workbook
Set wkbSolarSystem = Application.Workbooks("workbookname.xlsm")
Set shtData = wkbSolarSystem.Worksheets("Data")
lstPlanets.List = shtData.Range("Planets").value
lstPlanets.ListIndex = 3
End Sub
I tried this code on a different computer, and it works. It seems that it's the computer configuration that was causing the issue. The code works fine as it should on several different computers.