Using worksheet number in code... Or what part of a worksheet property normally can't be changed

[mark hansen]

I'm creating a template workbook that will have the ability for users to put in the password they were given to surface only the worksheets they are allowed to look at. The rest of the worksheets will be "very hidden". The worksheets will be password protected from editing so protection will be turned on.

In order for a user to set this up, I've come up code that looks at the password they enter to view the worksheet(s) that password is set up to show. So I have a grid with the 11 worksheets available with the view password, and the sheet protection password. (This allows the admin to set things up without digging into VBA)

When hiding and protecting the worksheets, I have a loop that sets the sheet to very hidden and protects the sheet with the password from the grid in the set up area.

The problem I'm having is finding the property of a worksheet the user normally cannot change. I thought it was the sheet number. But what I'm starting to discover, the order of the tabs seems to affect the sheetnumber (sn) in the code.

The reason I think this is, when I run the following loop, what ever worksheet I have first in order, this is the worksheet used with with "With Sheets(sn)" statement. When starting, the variable "sn" i=1 , and the next 2 .Range statements write text on the first sheet in the worksheet tab order, not on Sheet1. The code name is correct, but because the .Protect Password line uses the Password set aside for sheet 1, (starting at line 34) the sheet protecting password is wrong.

Why does the Sheets(sn) (when sn is equal "1") write on the worksheet tab to the left, in cells AJ34 and AJ35, rather than on Sheet1? (the .Range lines are temporary lines to see what's going on and what password is used to protect the sheet.)

I would have thought the With Sheets(sn) statements would work with the sheet numbers in number order.... Not the order they appear at the botton of Excel.

Thanks for any insight.
Mark

Set PW = Sheet6
    xRow = 33
    For sn = 1 To 11
        xRow = xRow + 1
        With Sheets(sn)
            .Range("AJ34").Value = Sheets(sn).CodeName
            .Range("AJ35").Value = PW.Range("AF" & xRow).Value
            .Protect Password:=PW.Range("AF" & xRow).Value
            .Visible = xlSheetVeryHidden
          ' .Visible = True
        End With
        Sheet7.Visible = xlSheetVisible
    Next sn

 

[Fluff]

The sheet index number (which is what you are using) is the position of the sheet within the workbook (counting left to right & including hidden sheets) & has nothing to do with the actual sheet name.

 

[mark hansen]

So I need to loop through the codes names... I found out they can be changed, but as that need to be done in the VBE, and the project will be protected, I don't think changing code names will be an issue.

Instead of looping code names, I guess I could put them in my grid with passwords, and use them to look up off the code name to find the worksheet password to use when protecting... I'm assuming looping through the code name may be more difficult.

 

[Fluff]

As far as I know, you cannot directly loop through the codenames. You can however loop through the index number, or the worksheets & compare that to the codename.

 

[mark hansen]

Thanks for the idea Fluff!! You got me thinking in another direction.

I still looped through using the index number.
Then I set the code name in a variable.
Used that variable in a Vlookup to find the worksheet protection password.
And used that password to set the protection on the worksheet
Then set the worksheet to very hidden

Sub HideSheet()
Dim PW As Worksheet
Dim sn As Integer
Dim xRow As Integer
Dim CodeN As String
Dim ShtPW As String

On Error Resume Next
Set PW = Sheet6
   For sn = 1 To 11
        With Sheets(sn)
            CodeN = Sheets(sn).CodeName
            ShtPW = Application.WorksheetFunction.VLookup(CodeN, PW.Range("AD34:AF44"), 3, False)
            .Protect PassWord:=ShtPW
            .Visible = xlSheetVeryHidden
        End With
        Sheet7.Visible = xlSheetVisible
    Next sn
End Sub

Appreciate the help!
So it doesn't matter the order of the worksheet, the 1 to 11 loop will get them all. Doesn't matter the name of the worksheet. The VB project will be locked down, so no code name changes. I know if a worksheet is added, it won't be looped in, but if a worksheet is added, code needs to be modified.

Hope I got everything. Thanks again for the help.

Mark Hansen

 

[Fluff]

Glad you sorted it & thanks for the feedback.
However if you have not already done so, I would recommend changing the code names from the generic Sheet1, Sheet2 etc.