Hi all,
I would like my macros to be private, so they would not be visible when a user tries out Alt-F8. That works for all my macros, except for those that are called upon by a button. Is that normal?
If I make the macro private, it returns this error when the button is pressed: "Compile error: Sub or Function not defined".
Here are the relevant parts of my two macros:
- in the sheet where the button is:
- in the module 1 where all my macro's are, it calls this macro to show all the sheets in the workbook. And that macro cannot be made "Private". Is that normal?
I would like my macros to be private, so they would not be visible when a user tries out Alt-F8. That works for all my macros, except for those that are called upon by a button. Is that normal?
If I make the macro private, it returns this error when the button is pressed: "Compile error: Sub or Function not defined".
Here are the relevant parts of my two macros:
- in the sheet where the button is:
VBA Code:
Private Sub CommandButton2_Click()
ShowSheets
End Sub
- in the module 1 where all my macro's are, it calls this macro to show all the sheets in the workbook. And that macro cannot be made "Private". Is that normal?
VBA Code:
Sub ShowSheets() 'Cannot be a Private Sub !!
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
ActiveWorkbook.Unprotect "mypassword" 'yes, I changed my password :-)
For Each ws In ThisWorkbook.Worksheets
With ws
.Unprotect "mypassword"
.Visible = True
End With
Next ws
Application.WindowState = xlMaximized
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub