Maybe you can protect the workbook. This will prevent hidden worksheets from being displayed.

Hi amisandip,

I don't understand why you want to hide the sheets.  If you could describe what it is you are trying to achieve, maybe I could suggest a solution.

• Why do the sheets need to be hidden? 
• What is on the sheets? 
• Does it matter that whatever is on the sheets can be seen? 
• Are you just wanting to prevent the contents of the sheet(s) from being changed?

If you can provide a sample workbook too that would help.

Regards

Phil

Thanks a ton Phil and SunnyKow for replying. No, I don't want to hide any sheet. All I want is to stop displaying sheet's names in the sheet tabs. Let user only follow navigation buttons and not click on sheet's names directly.

Hi Amisandip,

As you found out yourself, users can 'turn on' the worksheet tabs themselves if they know how.  I don't know a way to force the tabs off and keep them off.

Using VBA to do anything relies on the user enabling macros.  You can't force a user to do this.

I can't help but think that maybe this is making things too complicated.  As you haven't answered my questions I don't really know why hiding the sheets is important and why users must use your navigation buttons.  As Excel has this functionality built in it does seem like duplicating what already exists.  But as I said, I don't fully understand your requirements.

Can the sheets you don't want users to see be set to xlSheetVeryHidden and all moved to the end of the workbook?  That way you could use code like I have attached.  With the VeryHidden sheets at the end of the workbook you just need to amend the code so that it never tries to activate a sheet above the last visible sheet's index number.

My code allows for the worksheet tabs to remain visible, but prevents users from using them.

Regards

Phil

Hi Phil, 

Thanks a ton for your suggestion. I agree with you. In my workbook there are some sheets where I want rows to get hidden automatically when a condition is hit. I have assigned this macro to the 'Next' button (a shape which I placed on the sheet). So if someone doesn't click on that button to go to next sheet, the macro will not get executed. That is why I want to restrict my users to click on the  navigation buttons only to go to another sheet and not to click sheet tabs directly. But I agree with you that one can't force someone to enable macros. Hence it is always a better idea to educate them. Thanks again for your suggestion.

There are ways to force users to enable macros. What if you don't allow them to see any sheet until they enable macros? They can see just a message: if you enable macros, then you will be able to use the file 😀

Here is a link  to a functional example: /hide-excel-sheets-if-macros-are-not-enabled/

From your description, sounds like you can use the Private Sub Worksheet_Activate() Event, whenever a user selects a sheet, no matter how this sheet got activated (by clicking the sheet tab, or your navigation button), a code can be run using this event. This way you can forget about hiding tabs.

I set both Worksheet_Activate() and Worksheet_SelectionChange(ByVal Target As Range) events to hide the Tabs

Catalin you are awesome here! Great help. So kind of you.

We're here to help 🙂

Which worked for you, the link or the Worksheet_Activate event?

Both. I had used Worksheet_Calculate and Worksheet_Change events in my Workbook but not Worksheet_Activate event yet. This worked nicely now. Thanks.