list box returns compile error on every alternative saving of a work book

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???
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
It is a strange thing, but I already notice an error in your code:

The worksheet_activate code runs every time you active the sheet, so when you click on a diferent tab and then back to thi sheet the code runs again, and it will then append the tab names to the exisiting list, so the list of tab names will be duplicated (and next time triplicated, etc)
So you should clear the list first before adding
Code:
me.ListBoxsH.Clear
Alternatively You could generate the list on Workbook_Open

The other thing is that you delete three sheets in a fixed position. Are the tabs static (ie the sheets never change)? You could then link your control to a range containing the tab names, so it doesn't need to be generated on the fly.

A third option is to create and position the listbox on opening the workbook.
 
Upvote 0
Hi,

thanks for the response

i had already put the clear command in.

I have removed the code that generates the sheets and replaced it with ADDITEM. the new code is as below where P1,P2,P3 and P4 are tabs in my workbook.

The idea of this worksheet and listbox is the user can select from the list (using shift / ctrl for multiple tabs) exactly which worksheets to print.




Private Sub ListBoxsH_Click()

End Sub

Private Sub Worksheet_Activate()
Dim Sh

Me.ListBoxsH.Clear

Me.ListBoxsH.AddItem ("p1")
Me.ListBoxsH.AddItem ("p2")
Me.ListBoxsH.AddItem ("p3")
Me.ListBoxsH.AddItem ("p4")

ListBoxsH.Selected(0) = True


End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub


the list generates perfectly when i open the worksheet that contains the list box. The print command works perfectly but i am still getting the same error on every second (alternate) saving of the work book.

I have tried to create the code in the main workbook but the list doesn't populate within the worksheet.

I currently have to open and close the workbook twice to prevent the error.
 
Upvote 0
If I were you I'd replace your listbox with a Form control version and redo the code accordingly. ActiveX controls are flaky at best, especially if you need them to work on different computers.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top