CodeName.Protect

[wallyeye]

I've started reading Professional Excel Development, which is a great book/pdf, and came across a suggestion to use codenames rather than worksheet.name to refer to sheets.

One of my existing protect procedures had an optional parameter for passing the sheet name, so it would protect just the one sheet instead of all sheets. So, to change this, I just had it accept a worksheet, instead of a string, parameter, and passed it a codename, like this:

Public Sub ProtectSheet(Optional wksCurr As Worksheet)
Dim strPW As String
strPW = "test"
If wksCurr Is Nothing Then
For Each wksCurr In ThisWorkbook.Worksheets
wksCurr.Protect (strPW)
Next wksCurr
Else
wksCurr.Protect (strPW)
End If
Set wksCurr = Nothing
End Sub

Now, my problem. When I call this with a codename-referenced object:

call protectsheet(sheet1)

it creates a ghost sheet in the VBA project. I tested this in a new sheet, by just creating a new module, pasting in the above code, and calling it from the immediate window. The sheet doesn't show up in the IDE right away, but if you close the VBA IDE and re-open it, you will see a new Excel Object:

Sheet4 (Sheet1)

in the object explorer, but not in the Excel GUI.

Has anyone seen this behavior before? I'm using Excel 2007, upgrading to 2010 soon.

I know I can regress to passing sheet names, I just hate the idea of going backward...

 

[sunnyland]

Some error, have to check further

 

[sunnyland]

Sorry for my first attempt to a solution. I always get carried away.

Your code seems to create another workbook in the current workbook.

I have not tested all possibilities but you are welcome to try the following code and hopefully it will do as expected

Let me know what you think.

Public Sub ProtectSheet(Optional wksCurr As Variant)
Dim strPW As String
strPW = "test"
Select Case VarType(wksCurr)
Case vbObject
If TypeOf wksCurr Is Worksheet Then
wksCurr.Protect (strPW)
End If
Case Else
Dim worki As Worksheet
For Each worki In ActiveWorkbook.Worksheets
worki.Protect (strPW)
Next worki
Set worki = Nothing
End Select
End Sub

 

[wallyeye]

It worked!

I played around with your routine for a bit, as it didn't seem that much different than my original. The real issue turned out to be the last statement:

set wksCurr = Nothing

When passing a codename into wksCurr, vba didn't like me setting codename to nothing. I'm sure this will apply in all situations, this just happened to be the first routine that I hit upon.

I liked your error checking as well, and incorporated it, ending up with:

Public Sub ProtectSheet(Optional wksCurr As Variant)
Dim strPW As String
strPW = "test"
If wksCurr Is Nothing Then
For Each wksCurr In ThisWorkbook.Worksheets
wksCurr.Protect (strPW)
Next wksCurr
Set wksCurr = Nothing
Else
If VarType(wksCurr) = vbObject Then
If TypeOf wksCurr Is Worksheet Then
wksCurr.Protect (strPW)
End If
End If
End If
End Sub

Thank you for showing me it could be done, this will become my new standard protect/unprotect routine. And, I learned a bit more about codenames.

 

[wallyeye]

Not quite right yet, the Is Nothing blows up when passed something other than a worksheet. Let's try:

Public Sub ProtectSheet(Optional wksCurr As Variant)
On Error GoTo Proc_Error
Dim strPW As String
strPW = "test"
If IsEmpty(wksCurr) Then
For Each wksCurr In ThisWorkbook.Worksheets
wksCurr.Protect (strPW)
Next wksCurr
Set wksCurr = Nothing
Else
If VarType(wksCurr) = vbObject Then
If TypeOf wksCurr Is Worksheet Then
wksCurr.Protect (strPW)
End If
End If
End If
Proc_Exit:
Exit Sub
Proc_Error:
Select Case Err
Case Else
MsgBox "Error " & CStr(Err) & ": " & Err.Description
Resume Proc_Exit
End Select
Exit Sub
End Sub

 

[sunnyland]

Hello again,

Modified my first version as notice some flows. Still not 100 % there but you are welcome to try it out. In this code and at this point, if the sheet is already protected but with another than your password it does not raise an error, and if there is another kind of error during the process of protecting, it is not handle at this point but apart from that it seems to work if you pass a string, some other objects or some inexisting objects....

As with all code, there will be always something that you don't think of that may make your code invalid.
===============================
What I notice in your code [keeping in mind that you will be passing by ref a worksheet object to protect a single worksheet or no parameters at all to protect all worksheets in the activeworkbook] is that in the followig code

--------------------------
If IsEmpty(wksCurr) Then
For Each wksCurr In ThisWorkbook.Worksheets
wksCurr.Protect (strPW)
Next wksCurr
--------------------------


Meaning if the reference passed is empty it should not be used that way but you should rather use another variable and cast it as a worksheet

Dim worki As Worksheet
For Each worki In ActiveWorkbook.Worksheets
worki.protect("test")
next

=================================

Something else, if you don't pass a parameter then you will get an error return not the empty status. To test that try instead of
If IsEmpty(wksCurr) Then

change your line to

If IsError(wksCurr) Then
MsgBox "Error"

then try to run the sub without parameter and you will see that you will get the "Error" message. Meaning the value is error rather then empty. Empty parameter does not equal empty value.
==================================

My other attempt is as follow:


Sub ProtectSheet(Optional wksCurr As Variant)
'if ref is to an object of type worksheet then message name of worksheet and protect
If IsObject(wksCurr) And TypeOf wksCurr Is Worksheet Then
MsgBox "object and existing worksheet"
wksCurr.Protect ("test")
Else
'other cases empty, string, inexisting worksheet, workbook, etc... protect all worksheet
MsgBox "not a worksheet object"
Dim worki As Worksheet
For Each worki In ActiveWorkbook.Worksheets
MsgBox worki.Name
worki.Protect ("test")
Next
End If
End Sub

 

[RoryA]

I would use something like

Public Sub ProtectSheet(Optional wksCurr As Variant)
    Dim strPW As String
    Dim ws As Worksheet
    strPW = "test"
    If IsMissing(wksCurr) Then
        For Each ws In ThisWorkbook.Worksheets
            ws.Protect strPW
        Next ws
    Else
        If TypeName(wksCurr) = "Worksheet" Then wksCurr.Protect strPW
    End If
End Sub

 

[sunnyland]

Dear Rorya,

The beauty of programming is there is always something to learn.

Thank you as you added to my current knowledge: never used or came across the ismissing function before.

And for typename, I suppose it is the same as using typeof .... is.....

Thank you for your input.

So I can simplify my code by removing the isobject() as obviously if it is a worksheet than it is defenitively an object:

Sub ProtectSheet(Optional wksCurr As Variant)
If TypeOf wksCurr Is Worksheet Then
wksCurr.Protect ("test")
Else
Dim worki As Worksheet
For Each worki In ActiveWorkbook.Worksheets
worki.Protect ("test")
Next
End If
End Sub

Francoise.

 

[RoryA]

Hi Francoise,
TypeName and TypeOf are in fact different. TypeName returns the actual type of a variable (object or otherwise) whereas TypeOf evaluates whether an object variable implements a specific interface.

 

[wallyeye]

Thanks guys, this does it for me. I was being lazy in not defining another worksheet object for the loop, the IsMissing and TypeOf will round out the code.