CODEWITHSUNDEEP
vbaexcel
lou
lou

Reputation: 21

Making VBA apply to renamed tabs & all tabs in a workbook

I don't know very much at all about VBA, but I found the below code on a website and am using it in a workbook. 

Private Sub Workbook_Open()
With Worksheets("WFD")
.EnableOutlining = True
.Protect Password:="XXXX", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

How should I amend this so that if the Sheet name is changed from "WFD" to something else, the code still works? Also I would like it to apply to all sheets in the workbook.

Thanks very much

Upvotes: 1

Views: 63

Answers (3)

MacroMarc
MacroMarc

Reputation: 3324

You should use the Sheet Object Codename. This is found in the VB Editor beside the sheet objects in the VB project.

By default they are Sheet1, Sheet2 etc. You can easily change them by clicking and typing a new name etc.

You could of course leave them as default codeName if you like... This is NOT the same as the worksheet name, which is changed by users on the Sheet tabs in Excel interface.

Private Sub Workbook_Open()
    With WFD 'where WFD is the CODENAME of the Sheet Object!
        .EnableOutlining = True
        .Protect Password:="XXXX", _
        Contents:=True, UserInterfaceOnly:=True
    End With
End Sub

Upvotes: 1

Krystian Kozłowski
Krystian Kozłowski

Reputation: 162

If you want this code for each worksheet use code below:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
   With ws
    .EnableOutlining = True
    .Protect Password:="XXXX", _
    Contents:=True, UserInterfaceOnly:=True
   End With
Next
End Sub

Upvotes: 1

Vityata
Vityata

Reputation: 43595

You could write Worksheets(1) or Worksheets(5), depending on the number of the Worksheet. However, if you start adding and deleting Worksheets, it whould not work (e.g., it would be working on some other worksheet). To see the associated number to the worksheet, run this and check the immediate window:

Option Explicit

Public Sub TestMe()

    Dim ws  As Worksheet
    Dim cnt As Long

    For cnt = 1 To ThisWorkbook.Worksheets.Count
        Debug.Print cnt; "-> "; Worksheets(cnt).name
    Next cnt

End Sub

However, if you have only one Worksheet, Worksheets(1) would always work.

Upvotes: 0

Related Questions