Macro to enter Center Header problem

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi Everyone,

I have the following macro to update the Center Header, it works fine but won't work if I enter anything that starts with a number or numbers only.
If I take out the formatting part, then it'll work, but I would really like to keep the formatting.

This is just the part of my code that deals with the Center Header.
What's wrong with my macro?

Code:
Application.ScreenUpdating = False
Center = InputBox("Enter Center Header")
        If Center = "" Then Exit Sub
        Application.StatusBar = "Changing header/footer in " & sh.Name
        sh.PageSetup.CenterHeader = "&""Calibri,Bold""&14" & Center
        MsgBox ("Center Header Changed to ") & Center
        Application.StatusBar = False
Application.ScreenUpdating = True

I'm using 2010,
Thanks for helping.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

ozbod

Board Regular
Joined
Apr 2, 2008
Messages
177
jtakw,

A really quick fix is to determine whether the first character of Center is numeric, and if so force a space at the front so it is treated as string.

I have highlighted the change (extra row) in red.

I will look into a better conversion process.


Code:
Sub ChangeCenterHeader()

    Set sh = Sheets("Sheet1")

    Application.ScreenUpdating = False
    Center = InputBox("Enter Center Header")
    
    [COLOR=#ff0000]If IsNumeric(Left(Center, 1)) Then Center = " " & Center
    [/COLOR]
    If Center = "" Then Exit Sub
        
    Application.StatusBar = "Changing header/footer in " & sh.Name
    sh.PageSetup.CenterHeader = "&""Calibri,Bold""&14" & Center
    MsgBox ("Center Header Changed to ") & Center
    
    Application.StatusBar = False
    Application.ScreenUpdating = True
    
End Sub
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
jtakw,

A really quick fix is to determine whether the first character of Center is numeric, and if so force a space at the front so it is treated as string.

I have highlighted the change (extra row) in red.

I will look into a better conversion process.


Code:
Sub ChangeCenterHeader()

    Set sh = Sheets("Sheet1")

    Application.ScreenUpdating = False
    Center = InputBox("Enter Center Header")
    
    [COLOR=#ff0000]If IsNumeric(Left(Center, 1)) Then Center = " " & Center
    [/COLOR]
    If Center = "" Then Exit Sub
        
    Application.StatusBar = "Changing header/footer in " & sh.Name
    sh.PageSetup.CenterHeader = "&""Calibri,Bold""&14" & Center
    MsgBox ("Center Header Changed to ") & Center
    
    Application.StatusBar = False
    Application.ScreenUpdating = True
    
End Sub

Wow, Thanks ozbod,

I just tried your modified code and it seems to work just fine. I had thought it was something more complicated.

Thank you very much for your time and insight, do let me know if there's some other way, just out of curiosity.

Thanks again!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,535
Office Version
  1. 365
Platform
  1. Windows
Thank you very much for your time and insight, do let me know if there's some other way, just out of curiosity.
Yes, there is another way.

But first, the problem is this code line
Rich (BB code):
sh.PageSetup.CenterHeader = "&""Calibri,Bold""&14" & Center
In the header code, an ampersand (&) followed by a number sets the font size. In your case you are trying to set it to size 14 per the red text.

However, if, in the input box you enter, say, 12345, then the variable 'Center' holds 12345 and that line of code becomes ...
Rich (BB code):
sh.PageSetup.CenterHeader = "&""Calibri,Bold""&1412345
... which tries to set the font size to 1412345 :eek:

The clue to solving this is a space, but it needn't be in the variable or require a special test as it can go directly in the code line like this. Note the space after the font size and before the quote mark.
Rich (BB code):
sh.PageSetup.CenterHeader = "&""Calibri,Bold""&14 " & Center
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Yes, there is another way.

But first, the problem is this code line
Rich (BB code):
sh.PageSetup.CenterHeader = "&""Calibri,Bold""&14" & Center
In the header code, an ampersand (&) followed by a number sets the font size. In your case you are trying to set it to size 14 per the red text.

However, if, in the input box you enter, say, 12345, then the variable 'Center' holds 12345 and that line of code becomes ...
Rich (BB code):
sh.PageSetup.CenterHeader = "&""Calibri,Bold""&1412345
... which tries to set the font size to 1412345 :eek:

The clue to solving this is a space, but it needn't be in the variable or require a special test as it can go directly in the code line like this. Note the space after the font size and before the quote mark.
Rich (BB code):
sh.PageSetup.CenterHeader = "&""Calibri,Bold""&14 " & Center

Thank You Peter! You guys amaze me!

I had a real good giggle(ok, laugh), after reading your reply, because I had actually tried "12345". I also tried 12345text, and got some really weird results.

This has been a really fun learning experience.

Thank You Very Much for pointing out the problem with my macro.

Still laughing! (Font size 1412345)
 

ozbod

Board Regular
Joined
Apr 2, 2008
Messages
177
Thanks Peter_SS,

I knew there would have been a better solution, but was just trying to get it to work for jtakw initially to get him over the hump. Have not worked with the header stuff much so your insights are invaluable
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,535
Office Version
  1. 365
Platform
  1. Windows
I had a real good giggle(ok, laugh), after reading your reply, because I had actually tried "12345". I also tried 12345text, and got some really weird results.

This has been a really fun learning experience.

Thank You Very Much for pointing out the problem with my macro.

Still laughing! (Font size 1412345)
Yes, I had a chuckle too. Initially my response was going to suggest "3" in the input box but thought a font size of over a million was a much more juicy thought (big piece of paper required!!). :LOL:
 

Watch MrExcel Video

Forum statistics

Threads
1,129,803
Messages
5,638,448
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top