You are here:

Excel/Unprotecting workbook in VBA

Advertisement


Question
QUESTION: Hello,
In Excel v2010, I have a workbook with 30 worksheets, each named after a sales rep (firstname lastname) e.g. Bob Smith.  Each sheet is protected with a password; I'm declaring variables in a module for both the username (which is the same as the sheet name) and password for the sheet.
The username variables are U1, U2, etc.  
The password variables are Up1, Up2, etc.
The following example is just for the 1st 2 sheets:

Public Const U1 As String = "Bob Smith"
Public Const U2 As String = "Linda Jones"
Public Const Up1 As String = "BS"  'password for Bob Smith
Public Const Up2 As String = "LJ"  'password for Linda Jones

Currently I have code that will unprotect each sheet, e.g.
Worksheets(U1).Unprotect Up1
Worksheets(U2).Unprotect Up2

While that code works, I'd much rather do this in a For Next loop; something like the following:
Dim x as string, y as string
For i = 1 to 30
x = "U"&i
y = "Up"&i
Worksheets(x).Unprotect y

But of course that doesn't work.
Obviously I'm doing something stupid; can you tell me what I'm doing wrong.
Many thanks for your expertise.

ANSWER: Hi Al,

The problem is that variable and constant names are not text strings that can be manipulated as such.  And unfortunately constants cannot be arrays, so the way to do what you want is to use array variables, like this:

  Public U(1 To 20)    As String
  Public Up(1 To 20)   As String
  
  U(1) = "Bob Smith"
  U(2) = "Linda Jones"
'     .
'     .
  Up(1) = "BS"
  Up(2) = "LJ"
'     .
'     .
  Dim i    As Integer
  For i = 1 To UBound(U)
     Worksheets(U(i)).Unprotect Up(i)
  Next i

Note that it is the contents of the elements of U and Up that are strings that can be set and manipulated.

I hope you find this helpful.

Damon

---------- FOLLOW-UP ----------

4Damon
4Damon  
QUESTION: Hi Damon,
Your solution looked to be perfect!
However, I'm getting a compile error (see attached image.)
The image is of module3 where I'm declaring the public variables.
Hopefully, this is an easy fix.
Thank you again!
- Al

ANSWER: Hi again Al,

The problem is that the code must be placed in a procedure, i.e., all executable code must be inside a procedure beginning with Sub myprocedurename and ending with End Sub.  Declarations (Public, Const, and Dim) can be outside but if outside should be at the top of the code module.  So that code I provided should look like this:

Sub UnProtectSheets

 Public U(1 To 20)    As String
 Public Up(1 To 20)   As String
 
 U(1) = "Bob Smith"
 U(2) = "Linda Jones"
'     .
'     .
 Up(1) = "BS"
 Up(2) = "LJ"
'     .
'     .
 Dim i    As Integer
 For i = 1 To UBound(U)
    Worksheets(U(i)).Unprotect Up(i)
 Next i

End Sub

I apologize for assuming you knew this.

Damon


---------- FOLLOW-UP ----------

4Damon1
4Damon1  
QUESTION: So you're saying that I want to have my array variables U and Up available for any 'sub/end sub' procedure, I need to declare those at the top of the module.  Will any module do?  Because that's exactly what I've done - please see attached.
We're very close I believe; I'm hoping what I'm missing is just something stupid on my part.  :)
Thank you so much, Damon.

Answer
Hi again Al,

Yes, the variables declared Public will be available to every procedure in every code module.  So any module will do.  But before any of those procedures can use them you must run the procedure that contains all the assignment statements to load their values.  

I still don't see the Sub...End Sub statements in your code in Module 3.

Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.