Sample macros and VBA settings for working with Excel worksheets
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.
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:
In the screen shot below, the second sheet has been renamed as "TransData".
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:
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.
To change the sheet's code name:
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":
However, if the macro refered to the code name instead, it would continue to run without errors:
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.
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
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.
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
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
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.
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.
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.
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
This macro protects all the worksheets in the active workbook, with no password. Watch the video below, to see how it works.
NOTE: If sheets are grouped, the ws.Protect code will not work. To avoid that problem, there is a line at the start to fhe macro, to select the first sheet in the workbook. 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 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
This macro unprotects all the worksheets, with no password. There is a line of code in the macro, to ungroup the sheets, if necessary. Watch the video below, to see how it works.
NOTE: If sheets are grouped, the ws.Protect code will not work. To avoid that problem, there is a line at the start to fhe macro, to select the active sheet.
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
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.
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
Last updated: April 8, 2018 11:38 AM
Contextures RSS Feed