Hide Macro

[Thanks]

Hello
Im using the following macros to protect/unprotect all sheets in my work book.

Sub Protect_sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="password"
Next ws
End Sub

Sub unprotect_sheets()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="password"
Next ws
End Sub

For my use I would like to assign a hot key to run the macros but I would
also like to hide the macros from other users I suppose my making the code
private. Apparently I cant do this because when I make the code private my
hot keys don't work.

How can I accomplish both requirements?

 

[Eduardo]

Hi,
You can do that with a password in your VBA module, however passwords in
excel can be broken

go to your VBA application, tools, VBAProject - Project Properties,
Protection, enter your password, then each time you want to go to VBA to see
the macros it will ask for the password

To apply macro to a button, go to Developer, Insert, insert your button and
assign the Macro,

 

[Thanks]

Thanks for your reply.
Well I had already protected the module. I added 2 buttons and assigned the
macros but the code still runs without password when clicked.

 

[Eduardo]

Hi,
I think I misunderstood what you wanted, if you want a password to be
entered when pushing the button and no all users having access to it, create
a new sheet where you will place the buttons, right click on the mouse in
that new sheet, view code, paste this
Private Sub Worksheet_Activate()
Dim strPassword As String
On Error Resume Next
Me.Protect Password:="MANAGER"
Me.Columns.Hidden = True

strPassword = InputBox("Enter password to access DATA sheet")

If strPassword = "" Then
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
ElseIf strPassword <> "MANAGER" Then
MsgBox "Password Incorrect "
ActiveSheet.Visible = False
Worksheets("Menu").Select
Exit Sub
Else
Me.Unprotect Password:="MANAGER"
Me.Columns.Hidden = False
End If
Range("a1").Select

On Error GoTo 0
End Sub

Private Sub Worksheet_Deactivate()
On Error Resume Next
Me.Columns.Hidden = True
On Error GoTo 0
End Sub

then hide the sheet, and in your main sheet place a buttom to open this
sheet, at that time it will ask for the password to unhide it.
Hope this helps