CodeName.Protect

wallyeye

New Member
Joined
Feb 3, 2012
Messages
9
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...
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
I would use something like
Code:
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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
Back
Top