Macro - Name v (Name) in Properties

[MarkBeck]

Hi

The following code attempts to protect several sheets within a workbook, but only those whose name begins with "Emp".

Sub ProtectSheets()
    
    For Each sh In Sheets
        If Left(sh.Name, 3) = "Emp" Then sh.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Next sh
    Sheets("Welcome").Range("a1").Select
End Sub

My problem is that, within the properties, (Name) is "Emp1" "Emp2", etc; but Name (without brackets) is "NN", "SF", Etc. So the code dosen't do anything.

What do i need to change?

Mark

 

[xlbo]

Use the worksheet.codename property

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[stevexff]

sh.CodeName perhaps?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[MarkBeck]

Thanks Geoff & Steve, but it does not change anything

 

[combo]

(Name) property is a sheet's name that the user see and can change in sheet's tab. You reference the sheet by, for instance, Sheets(Name).
Name property is sheet's code name, it is independent from name in the tab. It is useful when the user can change sheet's name or you need to access specific properties/methods. It is up to you which name you use, but you need to do it consistently.

combo

 

[PHV]

MarkBeck, to better understand what are Name and CodeName for a sheet:
For Each sh In Sheets
Debug.Print "Name=" & sh.Name & ", CodeName=" & sh.CodeName
Next sh

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886