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

mark hansen

Well-known Member
Joined
Mar 6, 2006
Messages
532
Office Version
  1. 2016
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

Code:
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,610
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 6, 2006
Messages
532
Office Version
  1. 2016
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,610
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

mark hansen

Well-known Member
Joined
Mar 6, 2006
Messages
532
Office Version
  1. 2016
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

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,610
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,147,560
Messages
5,741,835
Members
423,690
Latest member
VikMka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top