Disallow switching worksheets by clicking on a worksheet tab

[Bagharmin]

Is there a way to disable worksheet tabs so that a user can't change worksheets by clicking on them? I still want the sheets and tabs visible, but I don't want users to be able to use the tabs themselves.

Thanks.

 

[stnkynts]

I usually do something like this for each sheet.

Private Sub Worksheet_Activate()
 
Application.ScreenUpdating = False
If Not Environ("UserName") = "your user name" Then
    MsgBox ("You are not an authorized user")
    Sheets(1).Select
End If
Application.ScreenUpdating = True
 
End Sub

Note: This is not a foolproof way of keeping people out of the sheets but for the most part it will work.

 

[Scott R]

Similarly, a Deactivate Event in the sheet you want to keep them on.

 

[Bagharmin]

Deactivate Event sounds more like what I'm looking for. It's not that I want to keep them on certain sheets as much as I want them to use the navigation I've provided and not just jump from sheet tab to sheet tab. That way, if they click a sheet tab, it will just not do anything at all.

Can you tell me how "Deactivate Event" works?

 

[stnkynts]

The "Deactivate Event" that Scott was referring to is done using Worksheet_Deactivate. You would include code to prevent the switching of tabs on whatever event you wanted. For example: If cell A1 is blank then you are prompted to enter a value in it and the same sheet is reactivated.

Please provide what specific event you want to have happen prior to changing tabs so i can create a macro for you.

 

[Bagharmin]

I don't mind if users go from one sheet to another (in fact, I need them to). I just don't want them being able to use the worksheet tabs to do it. So, if they try to change sheets by clicking on the tab of a different worksheet, I

1. Don't want the sheet they clicked on to become active
2. Do want the sheet they were on to stay active

There aren't really any events I want to happen, I just want to be able to recognize and cancel the worksheet_Deactivate event if they clicked a tab to do it. And I don't know how to do either.

 

[Scott R]

I don't know how to disable tabs other than hiding them.

Why prevent users from clicking on tabs when you need them to navigate to other sheets?

Another way to ask is, What is your preferred method for having them navigate sheets?

 

[Bagharmin]

Not everyone who will use this particular workbook is well versed in Excel. Plus, I already have a userform that is on-screen from the moment they open the workbook that makes going from sheet to sheet (plus other things) very easy and intuitive (i.e., Click here for such-and-such). Also, my coding centers around the users clicking the buttons I have provided; manually changing sheets by clicking on the tabs can cause issues that I would rather avoid (I tried to trap everything I could think of, but some things still slip through from time to time).

If there isn't a way to do it, that's okay, but I thought I would at least check. Thanks, both of you, for your help.

 

[stnkynts]

Sounds to me like you need to hide the sheets that you dont want others to be able to tab to then unhide/rehide when necessary. Its not difficult to do in coding just time consuming depending on your current code. In addition you can code each sheet to recognize when it is being shown and put a stop check on that. Good luck.