Allow to work Macros in protected workbook

[Neilesh Kumar]

Dear Experts,

I have a macro in a protected workbook that does not allow to work correctly with it protected, can I get the macros to allow to work with protected workbook. Request to you please do provide your precious support and valuable cooperation.

Thank you and kind regards,

Neilesh

[ImranBhatti]

If you know the password of your workbook then YES you can run a macro on protected workbook provided that the workbook is not shared.

[Neilesh Kumar]

If you know the password of your workbook then YES you can run a macro on protected workbook provided that the workbook is not shared.

Dear Expert,

Yes i do know the password of my workbook. Can you please do provide the macro to allow run macros in protected workbook without unprotecting the workbook.

Dear Experts, Request to you please do help me on my raised thread.

Thank you for your precious phase and valuable support.

Regards,

Neilesh

[malcmail]

DO you mean just this once or do you mean whenever someone opens it up you want them to be able to use a macro to do something?

[Neilesh Kumar]

DO you mean just this once or do you mean whenever someone opens it up you want them to be able to use a macro to do something?

Dear Experts,

The workbook will be permanently locked and i would like to allow them for all the macros for worksheets without unprotecting the workbook. Request to you kindly do help on the same.

Many thanks and kind regards,

Neilesh

[malcmail]

OK. You can set your protection to allow macros to work anyway, but every time the workbook is reopened it defaults to not allowing any changes. SO you need to add a little VBA code to the "ThisWorkbook" element. It will look a little like this.

Private Sub Workbook_Open()
Dim wsh As Worksheet
For Each wsh In ActiveWorkbook.Worksheets
If wsh.Name Like "hidden_updatedata" = False Then
wsh.EnableSelection = xlUnlockedCells
wsh.Protect Password = "password_here", userinterfaceonly:=True


End If
Next wsh
end sub

This will run each time it is opened to solve your problem. You should note that users will have to set their applications to allow macros and then to enable them when the file is opened, otherwise you will be stuck in the same place.

Hope that helps.

[Neilesh Kumar]

OK. You can set your protection to allow macros to work anyway, but every time the workbook is reopened it defaults to not allowing any changes. SO you need to add a little VBA code to the "ThisWorkbook" element. It will look a little like this.

Private Sub Workbook_Open()
Dim wsh As Worksheet
For Each wsh In ActiveWorkbook.Worksheets
If wsh.Name Like "hidden_updatedata" = False Then
wsh.EnableSelection = xlUnlockedCells
wsh.Protect Password = "password_here", userinterfaceonly:=True


End If
Next wsh
end sub

This will run each time it is opened to solve your problem. You should note that users will have to set their applications to allow macros and then to enable them when the file is opened, otherwise you will be stuck in the same place.

Hope that helps.

Dear Experts,

I do have more than 40 worksheets in my Dashboard and i do have macro for application caller to jump directly to the particular sheet from Home tab and in this situation rest all 39 tabs will be hidden. Please find here the codes below:

Sub GoToSheet()
    Application.ScreenUpdating = False
    With Worksheets(Application.Caller)
        .Visible = True
        .Select
    End With
    Application.ScreenUpdating = True
End Sub

and the provided macro for keep protecting workbook but allow to worksheet macros to work, is debugging in the above code at ".Visible = True".

and by clicking from home tab application caller macro will open and unhide that particular worksheet and for the same i am using the below code which will unhide the selected tab only, is as below:

Sub unhide()
Dim Ws As Worksheet
For Each Ws In Excel.Worksheets
If Ws.Name = "Home" Then
Ws.Visible = xlSheetVisible
Else
Ws.Visible = xlSheetVisible
End If
Next Ws
End Sub

Request to you please have a look and do provide the solution on the same. Many thanks and kind regards.

Neilesh

[Neilesh Kumar]

Dear Experts,

Can you please help me out in this. Please do let me know if i have not explained well about the requirement.

Many thanks and kind regards,


Neilesh

[malcmail]

I'm not 100% sure of what you are looking for but I use a number of things workbooks that are similar sounding to yours. The sheet tabs are hidden and the code used is not .visible but .activate. That should then bring up the sheet you want in the window.

[Neilesh Kumar]

I'm not 100% sure of what you are looking for but I use a number of things workbooks that are similar sounding to yours. The sheet tabs are hidden and the code used is not .visible but .activate. That should then bring up the sheet you want in the window.

Dear Experts,

while using the provided code for workbook to allow run the worksheet macros under protected form, my current code for GoToSheet is showing error highlight ".Visible=True" and the code which i am using for GoToSheet is as below:

Sub GoToSheet()
    Application.ScreenUpdating = False
    With Worksheets(Application.Caller)
        .Visible = True
        .Select
    End With
    Application.ScreenUpdating = True
End Sub

I hope i have explained now Sir.

Many thanks and kind regards,

Neilesh

[xlnitwit]

Hi,

If you have protected the workbook itself- structure and/or windows- then you cannot change the visibility of any sheet without unprotecting it first. Your code will need to unprotect, show the sheet, and then reprotect the workbook.

[malcmail]

I'm not sure why you woudl make it visible. UNless you have made the sheets very hidden unless I'm mistaken you could just use activate to show the sheet instead of the two lines you have there. Give it a try.

[Neilesh Kumar]

I'm not sure why you woudl make it visible. UNless you have made the sheets very hidden unless I'm mistaken you could just use activate to show the sheet instead of the two lines you have there. Give it a try.

Dear Expert,

As i mentioned that i do have around 40 tabs in my Dashboard and i am using the macro for only one tab visible that is Home tab. I have hyperlinked to rest all other tabs and out of them few are hidden as working tabs. Now when click from Home tab to any particular report will take me to that particular hidden tab which will unhide immediately and for the same i am using the below code in Model1:

Sub GoToSheet()
    Application.ScreenUpdating = False
    With Worksheets(Application.Caller)
        .Visible = True
        .Select
    End With
    Application.ScreenUpdating = True
    
End Sub

and once i return back to the home tab then the other visible tab will hidden immediately and for the same i am currently using the code as below:

Sub BackTohome()
    Application.ScreenUpdating = False
    Dim aSheet As String
    
    aSheet = ActiveSheet.Name
    
    Worksheets("Home").Select
    Worksheets(aSheet).Visible = False
    Application.ScreenUpdating = True
    
End Sub

Since i do have few working sheets as well and i do not want to keep them as visible or hidden but i want them to keep as veryhidden and for the same if i protected my workbook with password and when i used the provided macro to allow macros to work in protected workbook then it is debugging in the first macro code (GoToSheet) and highlighting ".Visible = True".

I hope now i am able to explain what i am looking for Sir.

Request to you kindly do provide the required macro or if the changes are required in the current using macro for GoToSheet or BackToHome then please do so dear Expert.

Thank you for your precious efforts and valuable support.

Regards,

Neilesh

[Neilesh Kumar]

Dear Experts,

My main purpose here to get the macro for allow other worksheet macros in protected workbook is only that i do have few working sheets and those i want to keep as veryhidden but when i do protect my workbook with password then the GoToSheet macro and BackToHome macro does not work and it debug at GoToSheet macro at ".Visible = True".

Request to you please help me out dear Experts.

Many thanks and kind regards,

Neilesh

[ImranBhatti]

In a standard modual copy this code

Public Sub Protect_All()
    Application.ScreenUpdating = False
        Dim ws As Worksheet
        For Each ws In Worksheets
    ws.Protect Password:="123"
    Next
    Application.ScreenUpdating = True
End Sub
Public Sub unProtect_All()
    Application.ScreenUpdating = False
        Dim ws As Worksheet
        For Each ws In Worksheets
    ws.Unprotect Password:="123"
    Next
    Application.ScreenUpdating = True
End Sub

Now for every macro in your workbook if they need to modify something on the worksheets call the unprotect_All procedure at the beginning of every macro and call the Protect_All at the end of every modual like this

sub your macro()
call Unprotect_All

"Your macro here"

call Protect_All
end sub

And of course you will have to change the password accordingly.

[Neilesh Kumar]

In a standard modual copy this code

Public Sub Protect_All()
    Application.ScreenUpdating = False
        Dim ws As Worksheet
        For Each ws In Worksheets
    ws.Protect Password:="123"
    Next
    Application.ScreenUpdating = True
End Sub
Public Sub unProtect_All()
    Application.ScreenUpdating = False
        Dim ws As Worksheet
        For Each ws In Worksheets
    ws.Unprotect Password:="123"
    Next
    Application.ScreenUpdating = True
End Sub

Now for every macro in your workbook if they need to modify something on the worksheets call the unprotect_All procedure at the beginning of every macro and call the Protect_All at the end of every modual like this

sub your macro()
call Unprotect_All

"Your macro here"

call Protect_All
end sub

And of course you will have to change the password accordingly.

Dear Expert,

I have tried from my end but unable to do so, Would like to request to you if you can put the provided macro and kindly have a look whether it works under protected form of workbook or not. Please find here attached Dashboard with the exists macros.

Workbook and Worksheet password is 1234.

Thank you for your precious support and valuable contribution.

Regards,

Neilesh

[ImranBhatti]

Sorry your password is wrong
Upload an unprotected workbook.

[Neilesh Kumar]

Sorry your password is wrong
Upload an unprotected workbook.

Dear Expert,

I have changed the workbook Sir and the password is 1234.

[malcmail]

The unhide button seemed to simply unhide all of the sheet tabs. Is this what you want to happen?

[Neilesh Kumar]

Dear Expert,

I do require to modify in current macro or add the macros to work even the workbook is password is protected. Now in the current situation if i do protect the workbook with password other macros does not work Sir.

Request to you please do help me out on the same Sir.

Many thanks and kind regards,

Neilesh

[Neilesh Kumar]

Dear Experts,

I would like to allow to run the current macros but simultaneously i would like to protect the workbook and even after protecting the workbook or protecting the VBA Project. So that is i am looking for Dear Experts. Request to you kindly do provide the requested macro.

Thank you for your precious support and valuable phase.

Regards,

Neilesh

[malcmail]

As provided the buttons work for me without any problems. What am I missing?

[Neilesh Kumar]

As provided the buttons work for me without any problems. What am I missing?

Dear Experts,

Currently the provided workbook and VBA Project is unprotected and only the worksheets are protected with password 1234 and i want to protect the workbook as well and rest all other macros should run without any unprotection of workbook and VBA Project.

Request to you if you can please do provide the solution on the same.

Many thanks and kind regards,

Neilesh

[malcmail]

Is the ultimate goal to show all tabs when you press the button or to only show a particular sheet that is chosen by the user on a dashboard type list?

[Neilesh Kumar]

Dear Expert,

In the provided example wokrbook, I do have around more than 40 worksheets and out of them few are working worksheets which i really want them to hide and protect with password but that is only possible to lock the workbook with password and if i do lock then the current macros are not working.

That is the reason Sir i have keep visible only two tabs at a time one will Home and the other will be visible based on click from Home tab and once i do back from other tab to Home tab the rest other tab will be hidden and if i want to Hide or unhide all the worksheets with a single command so for the same i have kept the two buttons but unhide sheet button is unhiding all the worksheets including working worksheets as well.

So i do have no other option now but only to protect the workbook with passcode so the if i or some hit the unhide sheet button then it should not unhide the working sheets as well.

Dear Expert my main MOTTO are as follows:

1) Keep visible only Home tab and once i do click on another report will jump to that particular tab and once i do back to home the inactive tab should be hidden automatically and the command should work in protected worksheet, workbook and protected VBA Project.

2) Hide Sheet button should work in protected worksheet, workbook and protected VBA Project.

3) Unhide Sheet button should work in protected worksheet, workbook and protected VBA Project.

Your precious and kind support would be appreciated on the same.

Many thanks and kind regards,

Neilesh

[malcmail]

This may achieve what you are after but without protecting the workbook itself - just the sheets. You won't need to protect the workbook as nobody will be able to get into the sheet tabs (or show them).

Some code.

Private Sub hide_everything()
Dim Ws As Worksheet
For Each Ws In Excel.Worksheets
If Ws.Name = "Dashboard" Then
'Ws.Visible = xlSheetVeryHidden
Else
Ws.Visible = xlSheetVeryHidden
End If
Next Ws
End Sub

You can use this to make the sheets "super hidden", so no sheet tabs will be shown, no way to access them with keystrokes etc.

To get a particular sheet to show...

Sub goto_sheet()
Worksheets("BS PPY Actuals").Visible = xlSheetVisible
Worksheets("BS PPY Actuals").Activate
End Sub

I just did this simply to show it worked. You can still use your application.caller code.

To rehide it put in the worksheet_deactivate code the needed pieces from the code above for veryhidden. Give that a go and see if it does what you need.

[Neilesh Kumar]

This may achieve what you are after but without protecting the workbook itself - just the sheets. You won't need to protect the workbook as nobody will be able to get into the sheet tabs (or show them).

Some code.

Private Sub hide_everything()
Dim Ws As Worksheet
For Each Ws In Excel.Worksheets
If Ws.Name = "Dashboard" Then
'Ws.Visible = xlSheetVeryHidden
Else
Ws.Visible = xlSheetVeryHidden
End If
Next Ws
End Sub

You can use this to make the sheets "super hidden", so no sheet tabs will be shown, no way to access them with keystrokes etc.

To get a particular sheet to show...

Sub goto_sheet()
Worksheets("BS PPY Actuals").Visible = xlSheetVisible
Worksheets("BS PPY Actuals").Activate
End Sub

I just did this simply to show it worked. You can still use your application.caller code.

To rehide it put in the worksheet_deactivate code the needed pieces from the code above for veryhidden. Give that a go and see if it does what you need.

Dear Expert,

Thank you so much for your precious efforts. Let me try Sir. Dear Sir, please do correct if i am at wrong, that as i do have are 20 different worksheets with analysis and reaching to the i made the keystrokes from Home tab. So the first provided code will be pasted in only in those worksheets which i have to present to the management and the second code will be pasted in Model or that also in individual worksheet which i have to present.

Please do let me know. Once again thanks a ton for your precious efforts Sir.

Many thanks and kind regards,

Neilesh

[Neilesh Kumar]

Dear Expert,

Thank you so much for your precious efforts. Let me try Sir. Dear Sir, please do correct if i am at wrong, that as i do have are 20 different worksheets with analysis and reaching to the i made the keystrokes from Home tab. So the first provided code will be pasted in only in those worksheets which i have to present to the management and the second code will be pasted in Model or that also in individual worksheet which i have to present.

Please do let me know. Once again thanks a ton for your precious efforts Sir.

Many thanks and kind regards,

Neilesh

Dear Expert,

Can you please do provide the example workbook with pasting the provided code. So i can follow the same for rest of worksheets.

Many thanks and kind regards.

Neilesh

[malcmail]

The first bit of code you can run any time to hide them. I would also have it run on workbook_open as well (in the same way to the previous protection code).

I have changed it up a little in the attached so the code to rehide runs using your home button (which now goes to the dashboard). If you use the excel options to hide the sheet tabs then the visible way to swap tabs is using the button so the code will be run. The other alternative would be evvery time someone clisk back to the dashboard the code runs to hide all the sheets again. I'll leave that to you.

Hopefully the attached works and is all good for you. Good luck.

[Neilesh Kumar]

The first bit of code you can run any time to hide them. I would also have it run on workbook_open as well (in the same way to the previous protection code).

I have changed it up a little in the attached so the code to rehide runs using your home button (which now goes to the dashboard). If you use the excel options to hide the sheet tabs then the visible way to swap tabs is using the button so the code will be run. The other alternative would be evvery time someone clisk back to the dashboard the code runs to hide all the sheets again. I'll leave that to you.

Hopefully the attached works and is all good for you. Good luck.

Thank you so much Sir, It worked perfectly as per my requirement. Thanks a lot once again Sir

Many thanks and kind regards,

Neilesh

[Neilesh Kumar]

This may achieve what you are after but without protecting the workbook itself - just the sheets. You won't need to protect the workbook as nobody will be able to get into the sheet tabs (or show them).

Some code.

Private Sub hide_everything()
Dim Ws As Worksheet
For Each Ws In Excel.Worksheets
If Ws.Name = "Dashboard" Then
'Ws.Visible = xlSheetVeryHidden
Else
Ws.Visible = xlSheetVeryHidden
End If
Next Ws
End Sub

You can use this to make the sheets "super hidden", so no sheet tabs will be shown, no way to access them with keystrokes etc.

To get a particular sheet to show...

Sub goto_sheet()
Worksheets("BS PPY Actuals").Visible = xlSheetVisible
Worksheets("BS PPY Actuals").Activate
End Sub

I just did this simply to show it worked. You can still use your application.caller code.

To rehide it put in the worksheet_deactivate code the needed pieces from the code above for veryhidden. Give that a go and see if it does what you need.

Dear Expert,

Can you please do provide the example workbook with pasting the provided code. So i can follow the same for rest of worksheets.

Many thanks and kind regards.

Neilesh