Help With Stripping Out Header Garbage...

jimmyj352

Board Regular
Joined
Jul 5, 2002
Messages
72
Hi everyone,

I have a macro that will launch an InputBox with the header from the current worksheet as the default text. I have it using:

ThisWorkbook.ActiveSheet.PageSetup.CenterHeader

The problem is that it returns all the formatting garbage like:

&"Times New Roman,Bold"&14ABC Company Report

I just would need it to show just the text - in this case:

ABC Company Report

Is there any property that maybe I'm missing or a different method that regardless of the font properties would return just the text itself?

Thanks for your help.

-- Jim
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

bydesign

Board Regular
Joined
Nov 29, 2003
Messages
184
Jimmy

My test only returns the header text...

txtHeader = ThisWorkbook.ActiveSheet.PageSetup.CenterHeader


Are you doing it this way?

can you give some more details?
 

jimmyj352

Board Regular
Joined
Jul 5, 2002
Messages
72
I just did a couple of my own tests and when you don't set any changes to the header font, it will return just the text. But, go ahead back into the header and set it with some characteristics such as bold and 14pt. Then run the test again and you should get back all the garbage in there.

Unfortunately, I know I will have different fonts in there.

Any other thoughts? I appreciate your help.

-- Jim
 

bydesign

Board Regular
Joined
Nov 29, 2003
Messages
184
Jimmy

This is a stumper...

The only thing I can think of is to start at the right of that string and check to see if each character is Alpha or a Space... if you hit a number use the Right$ function Lenght of the string - position of the numberic character..

Of couse the limitation being if you have numbers in your header text...

:oops:
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Jimmy,

You are probably using something like this, which as you note, returns all the formatting garbage.
Code:
Sub myInput()
Dim myFile As String

myFile = Application.InputBox( _
            Prompt:="Reports" & _
            vbNewLine & "", _
            Title:="Company Name and Report", _
            Default:=ThisWorkbook.ActiveSheet.PageSetup.CenterHeader, _
            Type:=0)
End Sub
I’m not aware of any property that would return just the text itself. However, here is a work around (albeit a little clunky)

In some cell, say J1, return the header details:
Code:
Sub myHeader()
ActiveSheet.Range("J1").Value = ActiveSheet.PageSetup.CenterHeader
End Sub
Cell J1 will show:
&"Times New Roman,Bold"&14ABC Company Report

Assuming that the words “ABC” will not change, the next step is to parse cell J1 to remove everything left of “ABC”:

In J3:
=RIGHT(J1,LEN(J1)-FIND(“ABC”,J1)+1)

J3 will now show:
“ABC Company Report” (without the quotes).

Now use J3 in your input box:
Code:
Sub myInput2()
Dim myFile As String

myFile = Application.InputBox( _
            Prompt:="Reports" & _
            vbNewLine & "", _
            Title:="Company Name and Reports", _
            Default:=Sheets(1).Range("J3"), _
            Type:=2)
End Sub

HTH

Mike
 

jimmyj352

Board Regular
Joined
Jul 5, 2002
Messages
72
Thanks for your help guys... I think I will try to use something similar to what Scott was thinking because the header will always change, but I will see if I can come up with something.

Thanks.

-- Jim
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,321
Messages
5,769,444
Members
425,546
Latest member
DisMissive

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