Private sub doesn't work

[opislak]

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:

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?

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

 

[Michael M]

Yep

When we think about Private subs, it is best to view them as VBA code which can only be called by other VBA code within the same module. 
For example, if Module1 contains a Private Sub, it cannot be called by any code in another module.

So, why not simply protect the VBA project so no one can view it ??

 

[diddi]

the Private keyword does not indicate that you cannot see it.
Private declares that the sub can only be called by other subs in the same module

 

[opislak]

Yep

When we think about Private subs, it is best to view them as VBA code which can only be called by other VBA code within the same module.
For example, if Module1 contains a Private Sub, it cannot be called by any code in another module.

So, why not simply protect the VBA project so no one can view it ??

Do you mean by password? For that doesn't work either (my macros are password protected). A user still can see and execute the macros when pushing Alt-F8

 

[opislak]

the Private keyword does not indicate that you cannot see it.
Private declares that the sub can only be called by other subs in the same module

Aha, is that so? Thank you, I didn't know that. Never too old to learn
But still, how come that the names of the Private Sub macros are invisible when a user presses Alt-F8, and those that are Sub (not Private Sub) are visible?

 

[diddi]

they are all visible. you are just not looking in the right place for them (assume not password protected)

 

[opislak]

they are all visible. you are just not looking in the right place for them (assume not password protected)

Sorry, you lost me.
My macros are password protected.
Where should I look then? This is what I mean:
- in the Excel sheet, I press the combination Alt-F8
- a popup window appears with the available macros. Only the non-private Subs are visible in that window.
Is there another way a user can search for my macros?

 

[diddi]

Alt-F11 ?

 

[opislak]

Alt-F11 ?

That's no problem. As my macros are password protected, you can't see them when Alt-F11 is pressed. That is, you can't see the Private Sub macros. The Subs remain visible.
Or do I misinterprate you? If so, sorry...

 

[diddi]

probably my misunderstanding.
so why not grab the code from the Sub

Private Sub CommandButton2_Click()
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
        .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
       
        .WindowState = xlMaximized
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub