mickeylove
New Member
- Joined
- Sep 25, 2019
- Messages
- 3
hi,
i have code to generate a listbox of all of my available sheets in a work book.
The code works fine and when i enter the worksheet the list is populated.
The problem i have is when i save the workbook and re-open i get a compile error, if i save the workbook a second time the error goes away.
In essence the error only manifests on every alternate saving of the work book. The error states "compile error in hidden module listboxsH"
The code is as follows:
Private Sub ListBoxsH_Click()
End Sub
Private Sub Worksheet_Activate()
Dim Sh
For Each Sh In ThisWorkbook.Sheets
Me.ListBoxsH.AddItem Sh.Name
Next Sh
Me.ListBoxsH.RemoveItem (17)
Me.ListBoxsH.RemoveItem (12)
Me.ListBoxsH.RemoveItem (7)
ListBoxsH.Selected(0) = True
End Sub
I have noticed that the only difference in the 2 open workbooks (ie one in error and one not in error) is that the item "listboxsH" is missing from the drop down list at the top of the VBA editor in the error version.
The contents of the drop down lists view as follows
SHEET IN ERROR:
(general)
worksheet
SHEET NOT IN ERROR
(general)
listboxsH
worksheet
It's as if the list doesn't get generated every second save of the workbook.
Any ideas good people???
i have code to generate a listbox of all of my available sheets in a work book.
The code works fine and when i enter the worksheet the list is populated.
The problem i have is when i save the workbook and re-open i get a compile error, if i save the workbook a second time the error goes away.
In essence the error only manifests on every alternate saving of the work book. The error states "compile error in hidden module listboxsH"
The code is as follows:
Private Sub ListBoxsH_Click()
End Sub
Private Sub Worksheet_Activate()
Dim Sh
For Each Sh In ThisWorkbook.Sheets
Me.ListBoxsH.AddItem Sh.Name
Next Sh
Me.ListBoxsH.RemoveItem (17)
Me.ListBoxsH.RemoveItem (12)
Me.ListBoxsH.RemoveItem (7)
ListBoxsH.Selected(0) = True
End Sub
I have noticed that the only difference in the 2 open workbooks (ie one in error and one not in error) is that the item "listboxsH" is missing from the drop down list at the top of the VBA editor in the error version.
The contents of the drop down lists view as follows
SHEET IN ERROR:
(general)
worksheet
SHEET NOT IN ERROR
(general)
listboxsH
worksheet
It's as if the list doesn't get generated every second save of the workbook.
Any ideas good people???