Odtnhj

I am trying to activate a spreadsheet using a pair of listboxes. lstWorkbooks stores the name of the workbook. lstSheets is a list of worksheets for the selected workbook in lstworksheets. My problem is selecting values from these list is producing random behaviour.

Please see my code below

Dim xlApp As Excel.Application
Dim wbk As Workbook

Private Sub lstSheets_AfterUpdate()
 ActiveWorkbook.Sheets(lstSheets.Value).Activate
End Sub

Private Sub lstWorkbooks_AfterUpdate()
 Me.lstSheets.Clear
 For Each wbk In xlApp.Workbooks
 If wbk.Name = Me.lstWorkbooks.Value Then
 Dim sh As Worksheet
 For Each sh In wbk.Worksheets
 If sh.Visible = xlSheetVisible Then Me.lstSheets.AddItem sh.Name
 Next sh
 Exit For
 End If
 Next wbk
 Windows(lstWorkbooks.Value).Activate
End Sub

Private Sub UserForm_Activate()
 Set xlApp = GetObject(, "Excel.Application")
 For Each wbk In xlApp.Workbooks
 If wbk.Name <> "Personal.xlsb" Then Me.lstWorkbooks.AddItem wbk.Name
 Next wbk
End Sub

Any help here so that it produces the desired behaviour is appreciated

You have

 Private Sub lstSheets_AfterUpdate()
 ActiveWorkbook.Sheets(lstSheets.Value).Activate
 End Sub

but lstsheets could list sheets from any open workbook. You can't assume the sheet is on the activeworkbook. You need to ensure the workbook is active first, because the user could have activated a different workbook between selecting the workbook and selecting the sheet.

But another point is why are you trying to activate a sheet anyway. VB code doesn't require it, and the user would surely find it easier to just activate a sheet with the mouse if they wanted to see one rather than use a form?