Register
Log In
Unanswered
Home
June 27, 2016
Offline
I have a macro enabled workbook with several sheets. I have provided navigation buttons to go to and fro among all the worksheets. I want my users to click on these buttons only to navigate through the workbook. I have unchecked "Show Sheet Tabs" from File - Options - Advanced. I have also added this Macro
Private Sub Workbook_Open()
ActiveWindow.DisplayWorkbookTabs = False
End Sub
But even after all these an user can still check "Show Sheet Tabs" from File - Options - Advanced and see the sheet names. How to stop him from doing that or how can I stop showing up sheet tabs even if one checks "Show Sheet Tabs" option. Any help would be highly appreciated. Thank you in advance.
See Answer
Dashboards
June 25, 2016
Offline
Maybe you can protect the workbook. This will prevent hidden worksheets from being displayed.
October 5, 2010
Offline
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
June 27, 2016
Offline
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.
October 5, 2010
Offline
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
June 27, 2016
Offline
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.
November 8, 2013
Offline
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.
Answers Post
Dashboards
June 25, 2016
Offline
I set both Worksheet_Activate() and Worksheet_SelectionChange(ByVal Target As Range) events to hide the Tabs
June 27, 2016
Offline
Catalin you are awesome here! Great help. So kind of you.
Most Users Ever Online: 57
Currently Online: Max Neumann
21 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Ajay Mukhi
Stephen Motte
Rodney Hoyle
michael pitre
Samantha Burleson
Krishnaa Kurugod
David McSwain
Shameem Nassiree
Alanna Henderson
Janel Oz
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
