CODEWITHSUNDEEP
vbaexcel
Marc van der Peet
Marc van der Peet

Reputation: 343

Hide specific sheets when closing workbook

I'm creating some VBA code which should do the following:

  1. Users press a button a are required to input a code.
  2. When the input the correct code the team relevant code they get access to certain sheets.

The sheets they get access to differs according to the team number and code they enter. So when they enter he password "banana": the sheets "Team_1" & Team_1_sub become visible.

I now created the following code to achieve this:

 Sub filter_tabs()

  Dim answer As String
  answer = InputBox("Please enter your password")

   If answer = "Password" Then
    MsgBox "Correct, je krijgt nu de goede tabs te zien!"
    Worksheets("Team_1").Visible = True
    Worksheets("Team_1_sub").Visible = True

  Else
   MsgBox "Wrong password"
  End If

 End Sub

Two questions about the code above:

  1. When the users close the document all sheet should "disappear" again. Does anybody know how to do this? So when opening the document sheets "Team_1" and "Team_1_sub" should be be standard

    Worksheets("Team_1").Visible = False
Worksheets("Team_1_sub").Visible = False

  2. Could you guys give me some feedback on whether the procedure I follow above (different if statements where users are prompted for a password and then get to see certain tabs) is the most efficient one to reach my goal? End goal is to make sure certain team leader can only see certain sheets.

Upvotes: 2

Views: 11110

Answers (3)

brettdj
brettdj

Reputation: 55672

As you aren't using passwords you should at least make the sheets VeryHidden rather than Hidden - much harder for the average user to unhide.

The Me.Save proposed by the other answer also isn't necessary. 

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
    Worksheets("Team_1").Visible = VeryHidden
    Worksheets("Team_1_sub").Visible = VeryHidden
End Sub

Upvotes: 1

R.Katnaan
R.Katnaan

Reputation: 2526

Here for setting visible false, you can use Workbook_BeforeClose method as follow:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Worksheets("Team_1").Visible = False
    Worksheets("Team_1_sub").Visible = False

    'must save, if not save, it is not effect.
    Me.Save

End Sub

Reference for that method is here.

One thing is that this method must have in the ThisWorkBook module.

For next question, you should say more like, which sheets for which user and password. Because you code is enough for your question. It can use for your requirement.

Upvotes: 3

MikeD
MikeD

Reputation: 8941

ad 1)

you best use a Workbook_BeforeSave() routine to code the hiding of all sheets ... in the VBAProject view you find this under ThisWorkbook

ad 2)

The code you post looks very nice - my point of concern would be the hard coding of user names vs. sheet names. I would consider putting this in a sheet/table using headers /code/ /sheetname/ ... this way you can adapt your logic at any time without having to modify the code.

With such a table at hand (in an all time hidden sheet if need be) you traverse it (one single piece of code) and - upon code entering - you unhide If CodeInTable = CodeEntered ... in the other case you unconditionally hide that sheet ... because hiding/unhiding differs only by 2 simple conditions.

Upvotes: 0

Related Questions