Contextures

Excel Worksheet Macros

Sample macros and VBA settings for working with Excel worksheets

Set a Worksheet Code Name

Each sheet in an Excel workbook has a visible name, and a hidden code name. Here's how you can see those names, and change them.

Sheet Name

The sheet's name appears on its sheet tab. By default, each new sheet is named "Sheet", followed by the next available number, such as "Sheet2".

You can change a sheet's name:

  • Double-click on the sheet tab, to select the existing name
  • Type a new name (up to 31 characters), using letters, numbers, spaces and underscores.
  • Press Enter, to complete the name

In the screen shot below, the second sheet has been renamed as "TransData".

worksheet name on sheet tab

Sheet Code Name

Each sheet also has a hidden code name. By default, each new sheet is code-named "Sheet", followed by the next available number, such as "Sheet2".

NOTE: A sheet's default code name might not be the same as the sheet's default name.

To see the active sheet's code name:

  • On the Ribbon, click the Developer tab (How to show the Developer tab)
  • In the Controls group, click Properties

worksheet name on sheet tab

In the Properties window, the (Name) property, at the top, is the code name. The Name property (without brackets), is the name that appears on the sheet tab.

In the screen shot below, the code name is Sheet2, and the name is TransData.

worksheet name on sheet tab

Change the Sheet's Code Name

To change the sheet's code name:

  • In the Properties window, double-click the code name, to select it
  • Type a new code name -- one word only -- with letters, numbers, and underscores. Keep the name short and meaningful.
  • Press Enter, to complete the code name change

worksheet name on sheet tab

Use Sheet Code Names in Macros

When you create macros that refer to specific worksheets, it is better to use the code names, than the sheet tab names, because the code names are unlikely to be changed by the users.

For example, this code would result in an error, if the sheet name was changed from "TransData" to "Transactions":

Sheets("TransData").Activate

However, if the macro refered to the code name instead, it would continue to run without errors:

wsTD.Activate

Count the Selected Sheets

If you select more than one sheet in a workbook, the sheets are grouped, and that can cause problems. For example, if you manually enter data while sheets are grouped, that data will be entered in all the selected sheets, not just the active sheet.

Or, if you try to run code that protects or unprotects all the sheets, the code will fail if the sheets are grouped, as you can see in the video below.

To prevent these problems, you can count the number of sheets that are selected. If the count is higher than 1, show a warning message, so you will remember to ungroup the sheets.

To use this code, add it to a regular code module in your Excel file. Then, you could run the code at the start of other macros, or add it to the Workbook_SheetActivate event code.

warning grouped sheets

Sub GroupedCount()
Dim wdw As Window
Dim ws As Worksheet
On Error Resume Next
Set wdw = ActiveWindow
Dim Count As Long

For Each ws In wdw.SelectedSheets
   Count = Count + 1
Next ws

If Count > 1 Then
   MsgBox "WARNING! " & Count & " sheets are grouped", _
      vbOKOnly + vbCritical, "Grouped Sheets"
End If

End Sub

Change Workheet Appearance Settings

The following macros turn the workbook/worksheet appearance settings on or off. The first two macros affect only the active sheet. The next two macros affect all sheets in the active workbook.

To change these settings manually, click the File tab on the Ribbon, and click Options. Then click Advanced, and scroll down to the "Display options for this workbook" and "Display options for this worksheet" sections.

change worksheet appearance settings

Active Worksheet

The following macro turns ON gridlines, headings, page breaks and zeros for the active worksheet. The bSet variable is True, so all the settings are turned on.

Sub SettingsActiveSheetOn()
On Error Resume Next
Dim bSet As Boolean
bSet = True
'turns on headings, gridlines
'  page breaks and zeros
With ActiveWindow
  .DisplayHeadings = bSet
  .DisplayGridlines = bSet
  .DisplayPageBreaks = bSet
  .DisplayZeros = bSet
End With

End Sub

The following macro turns OFF gridlines, headings, page breaks and zeros for the active worksheet. The bSet variable has been change to False, so all the settings are turned off

Sub SettingsActiveSheetOff()
On Error Resume Next
Dim bSet As Boolean
bSet = False
'turns on headings, gridlines
'  page breaks and zeros
With ActiveWindow
  .DisplayHeadings = bSet
  .DisplayGridlines = bSet
  .DisplayPageBreaks = bSet
  .DisplayZeros = bSet
End With

End Sub

All Worksheets in Active Workbook

The following macro turns ON scrollbars and sheet tabs for the active workbook -- it affects all the sheets. The bSet variable is True, so all the settings are turned on.

Sub SettingsActiveWorkbookOn()
On Error Resume Next
Dim bSet As Boolean
bSet = True
'turns on scrollbars and sheet tabs
With ActiveWindow
  .DisplayVerticalScrollBar = bSet
  .DisplayHorizontalScrollBar = bSet
  .DisplayWorkbookTabs = bSet
End With

End Sub

The following macro turns OFF scrollbars and sheet tabs for the active workbook -- it affects all the sheets. The bSet variable has been change to False, so all the settings are turned off

Sub SettingsActiveWorkbookOff()
On Error Resume Next
Dim bSet As Boolean
bSet = False
'turns on scrollbars and sheet tabs
With ActiveWindow
  .DisplayVerticalScrollBar = bSet
  .DisplayHorizontalScrollBar = bSet
  .DisplayWorkbookTabs = bSet
End With

End Sub

Worksheet Custom Footer Macro

There are built in header and footer options in Excel, but if you need something different, or if you want to pull information from named cells in the worksheet, you can use a macro, similar to the sample code shown below.

To see how to set up the named cells, and an explanation of how the code works, you can watch this short video. The written instructions and code are below the video.

Using the Custom Footer Macro

In this example, there are named cells that contain the order date, customer name and order number. That data will be added to the footer, and the date will be formatted. The video below shows how to set this up.

To test the macro, you can download the Excel Footer Macro sample workbook. The zipped file is in xlsm format. After you unzip the file and open it, enable macros, so you can run the code.

change worksheet appearance settings

Custom Footer Macro Code

The following code adds the Order Date, Customer Name, and Order Number to the footer, based on the contents fo the named cells on the order sheet. The date formatted as dd-mmm-yyyy, and you could change to a different date format, such as "yyyy-mm-dd".

You can manually run the code, after entering the order information in the named cells. Or, you could add it to the Workbook module, in the BeforePrint event.

change worksheet appearance settings

Sub SetFooter()
' put customer name, date and Order Number in footer
' www.contextures.com
Dim wb As Workbook
Dim wsOrder As Worksheet
Dim strCust As String
Dim strOrder As String
Dim dtmDate As Date

Set wb = ThisWorkbook
Set wsOrder = wb.Sheets("Orders")

dtmDate = wsOrder.Range("OrderDate").Value
strCust = wsOrder.Range("CustName").Value
strOrder = wsOrder.Range("OrderNum").Value

With wsOrder.PageSetup
    .LeftFooter = _
       "Customer Name: " & strCust & Chr(10) & _
       "Order: " & strOrder
    .CenterFooter = ""
    .RightFooter = _
       "Date: " & Format(dtmDate, "dd-mmm-yyyy")
End With

End Sub

Protect All Sheets No Password

This macro protects all the worksheets in the active workbook, with no password.

Sub ProtectAllSheetsNoPwd()
Dim ws As Worksheet
On Error Resume Next
'select first sheet, in case sheets are grouped
Sheets(1).Select 

For Each ws In ActiveWorkbook.Worksheets
  ws.Protect DrawingObjects:=True, _
    Contents:=True, Password:=""
Next ws

End Sub

Unprotect All Sheets No Password

This macro unprotects all the worksheets, with no password. There is a line of code in the macro, to ungroup the sheets, if necessary.

Sub UnProtectAllSheetsNoPwd()
Dim ws As Worksheet
On Error Resume Next
'select active sheet, in case sheets are grouped
ActiveSheet.Select 

For Each ws In ActiveWorkbook.Worksheets
  ws.Unprotect
Next ws

End Sub

I added a line of code to the macro, to ungroup the sheets, if necessary, and the problem is solved. You could do something fancier, like identifying the active sheet, and selecting it, instead of the first sheet.

Sub ProtectAllSheetsNoPwd()
Dim ws As Worksheet
On Error Resume Next
'select active sheet, in case sheets are grouped
ActiveSheet.Select 
For Each ws In ActiveWorkbook.Worksheets
  ws.Protect DrawingObjects:=True, _
    Contents:=True, Password:=""
Next ws

End Sub

The same line was added to the Unprotect macro.

Sub UnProtectAllSheetsNoPwd()
Dim ws As Worksheet
On Error Resume Next
Sheets(1).Select '<====== selects first sheet in workbook
For Each ws In ActiveWorkbook.Worksheets
  ws.Unprotect
Next ws

End Sub

Video: Protect Macro Fails When Sheets Grouped

To see the problem that occurs when you try to protect or unprotect group sheets, and the code change that fixes it, please watch this short Excel video tutorial.

Hide Specific Worksheets

Worksheets can be set to xlSheetVeryHidden, xlSheetHidden or xlSheetVisible

This macro hides all the worksheets in the active workbook, if the sheet name starts with "Admin". The sheets are set ot "xlSheetVeryHidden", so those sheet names don't appear if you right-click a sheet tab, and click Unhide.

To show them, run a macro that changes the setting to "xlSheetVisible", or go into the Visual Basic Editor, and manually change the worksheet's Visible property.

Also see: Hide Sheets Based on Tab Colour

Sub HideAdminSheets()
Dim ws As Worksheet
On Error Resume Next

For Each ws In ThisWorkbook.Worksheets
    If Left(ws.Name, 5) = "Admin" Then
        ws.Visible = xlSheetVeryHidden
    Else
        ws.Visible = xlSheetVisible
    End If
Next ws

Worksheets(1).Activate

End Sub

More Tutorials

Worksheet Tips

Hide Sheets Based on Tab Colour

Worksheet Protection Selector

Worksheet AutoFilter VBA

Copy VBA to Sheet

VBA - Get Started

Search Contextures Sites

 

Excel Tools Add-in

Free Pivot Table Tools

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

 

 

Excel UserForms for Data Entry

 

Last updated: August 27, 2016 5:59 PM
Contextures RSS Feed