Private sub doesn't work

opislak

Board Regular
Joined
Feb 28, 2017
Messages
61
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:
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,585
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Yep
Rich (BB code):
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

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Feb 28, 2017
Messages
61
Yep
Rich (BB code):
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

Board Regular
Joined
Feb 28, 2017
Messages
61

ADVERTISEMENT

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

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
they are all visible. you are just not looking in the right place for them (assume not password protected)
 

opislak

Board Regular
Joined
Feb 28, 2017
Messages
61

ADVERTISEMENT

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

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows

opislak

Board Regular
Joined
Feb 28, 2017
Messages
61
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

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
probably my misunderstanding.
so why not grab the code from the Sub

VBA Code:
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
 
Solution

Forum statistics

Threads
1,137,154
Messages
5,679,912
Members
419,862
Latest member
Bluewings666

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
Top