If you want to replace text in Excel, you can go to Home >> Editing >> Find & Select >> Replace (Ctrl + H).
It’s not very complicated. The problem is that it doesn’t look for text in headers and footers, so there is no way to replace it using this method. If you want to replace the text there, you have to use the VBA code.
Insert header or footer
Let’s insert three headers to Excel (Insert >> Text >> Header & Footer).
Now, you have three headers inserted (left, center and right). You can click each of them and enter text.
If you scroll your page down, you will notice that there are also three footers there. Here, you can enter text in a similar way.
Replace headers or footers in the current sheet
The following code is going to replace text in the center header on the current worksheet. “Center header” is going to be “Center header 1”.
1 2 3 4 5 6 7 |
Sub ReplaceSingleHeader() mysheet = ActiveSheet.Name If Worksheets(mysheet).PageSetup.CenterHeader <> "" Then Worksheets(mysheet).PageSetup.CenterHeader = Replace(Worksheets(mysheet).PageSetup.CenterHeader, "header", "header 1") End If End Sub |
This is what it looks like in Excel.
In a similar way, you can do it for a footer.
1 2 3 4 5 6 7 |
Sub ReplaceSingleFooter() mysheet = ActiveSheet.Name If Worksheets(mysheet).PageSetup.CenterFooter <> "" Then Worksheets(mysheet).PageSetup.CenterFooter = Replace(Worksheets(mysheet).PageSetup.CenterFooter, "footer", "footer 1") End If End Sub |
I showed you how you can change the text for the center header. You can also do it for LeftHeader and RightHeader, or all of them at once.
Replace headers in all sheets
(Change code, the previous one worked for all headers)
1 2 3 4 5 6 7 8 |
Sub ReplaceAllHeaders() Dim i As Long For i = 1 To ActiveWorkbook.Sheets.Count With Sheets(i).PageSetup If Sheets(i).PageSetup.CenterHeader <> "" Then Sheets(i).PageSetup.CenterHeader = Replace(Sheets(i).PageSetup.CenterHeader, "header", "header 1") End With Next i End Sub |
So far, our code worked for a single worksheet
Adding InputBox – the easy way
In the previous example, we added two strings inside our code. The disadvantage of this is that we have to change it each time if we want to use different strings.
The other way to do our script is to add an input box.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Sub ReplaceSingleHeaderInputBox() Dim ws As Worksheet Dim first_input As Variant Dim second_input As Variant first_input = InputBox("Enter text to replace") second_input = InputBox("Enter replace phrase") For Each ws In Worksheets If ws.PageSetup.CenterHeader <> "" Then ws.PageSetup.CenterHeader = Replace(ws.PageSetup.CenterHeader, first_input, second_input) End If Next ws End Sub |
Each time you start a program, you will be asked for two strings. Each string for each input box.
The disadvantage of this method is that it’s not the most elegant way. The next example shows how to do it using a single form.
Adding InputBox – the bit harder way
In order to create this form, press Alt + F11 to open the VBA editor. Choose Insert >> UserForm.
Change the name to frmReplaceHeader and the caption to Replace Header.
From a Toolbox (View >> Toolbox) insert:
Two labels: lblFindWhat and lblReplaceWith.
Two textboxes: tbxFindWhat and tbxReplaceWith.
One command button: btnReplaceAll.
Change captions and place them in the same way as you can see in the image below.
Double-click the “Replace All” button to generate the private procedure. The code you type there will be executed each time you click the “Replace All” button.
1 2 3 4 5 6 7 8 9 |
Private Sub btnReplaceAll_Click() Dim ws As Worksheet For Each ws In Worksheets If ws.PageSetup.CenterHeader <> "" Then ws.PageSetup.CenterHeader = Replace(ws.PageSetup.CenterHeader, Me.tbxFindWhat.Value, Me.tbxReplaceWith.Value) End If Next ws End Sub |
Code explanation:
In this code, in the Replace function, there is Me.tbxFindWhat.Value and Me.tbxReplaceWith.Value. Me refers to user form, then it takes value from two textboxes and the rest of the code works the same way as before.