Solved: Speedy PageSetup

[Djblois]

I created a quicker pagesetup with a form and all and I can't get this part to work:

[VBA]Dim leftHeaderText As String
Dim centerHeaderText As String
Dim rightHeaderText As String
leftHeaderText = PageSetupForm.LeftText.Value
centerHeaderText = PageSetupForm.CenterText.Value
rightHeaderText = PageSetupForm.RightText.Value
With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Bold" & leftHeaderText
.CenterHeader = "&""Arial,Bold" & centerHeaderText
.RightHeader = "&""Arial,Bold""&12 " & rightHeaderText
End With[/VBA]

I get Arial, bold as my headers

[lucas]

I'm really lost Daniel.....you formatted the header as Arial Bold and then you question why that is the result.....

[mdmackillop]

[VBA]Private Sub CommandButton1_Click()
Dim leftHeaderText As String
Dim centerHeaderText As String
Dim rightHeaderText As String
leftHeaderText = PageSetupForm.LeftText.Value
centerHeaderText = PageSetupForm.CenterText.Value
rightHeaderText = PageSetupForm.RightText.Value
With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Bold""&12" & leftHeaderText
.CenterHeader = "&""Arial,Bold""&10" & centerHeaderText
.RightHeader = "&""Arial,Bold""&12" & rightHeaderText
End With

End Sub
[/VBA]

[Djblois]

Thank you mdmack,

Sometimes I feel like an idiot!!! lol

[Djblois]

I have created a function of my add-in to do a quick page setup formating and part of it checks what is in the right header, center header, and left header already and populates my form but it is also picking up the formating. How do I stop it from picking up the formating also? This is what I have:

[VBA]Private Sub UserForm_Initialize()
LeftText.Value = ActiveSheet.PageSetup.LeftHeader
CenterText.Value = ActiveSheet.PageSetup.CenterHeader
RightText.Value = ActiveSheet.PageSetup.RightHeader
End Sub[/VBA]

[Norie]

What do you mean it's picking up the format?

As far as I'm aware if you populate a textbox's value then that's all you do - the format doesn't come across.

[Djblois]

This is what I am getting (&"Arial,Bold"&10&"Arial,Bold"&10test) The code for the page formating.

[lucas]

I merged these threads Daniel. You are confusing folks and expecting them to know what has been going on in other posts.....you should have added your followup question to the existing thread to cut down on the confusion.......

[Djblois]

Sorry lucas i would have but I marked it as solved and I haven't figured out how to trun off the sloved yet.

[mdmackillop]

Hi Daniel,
Can you post a sample, otherwise I (and anyone else) has to create one to test out what you're saying to try and find a solution.
Regards
MD

[Norie]

Daniel

Why do you find this unusual?

Those are the values of those properties.

[Djblois]

Ok, Madmack

Norie, not necesarrily unusual I just don't want to see the formating also

[Djblois]

Here is the test book

[mdmackillop]

Hi Daniel,
This should strip off the font description and size. There will be problems if your header starts with a number. I've not found a way to return the header font size, which otherwise could be used.



[VBA]Private Sub UserForm_Initialize()

LeftText.Value = GetText(ActiveSheet.PageSetup.LeftHeader)
CenterText.Value = GetText(ActiveSheet.PageSetup.CenterHeader)
RightText.Value = GetText(ActiveSheet.PageSetup.RightHeader)

End Sub

Function GetText(txt As String)
Dim Arr, i As Long
If txt = "" Then
GetText = ""
Exit Function
End If
Arr = Split(txt, Chr(34))
txt = Arr(UBound(Arr))
If Left(txt, 1) = "&" Then
i = 2
Do Until IsNumeric(Mid(txt, i, 1)) = False
i = i + 1
Loop
i = i - 1
End If
GetText = Right(txt, Len(txt) - i)
End Function
[/VBA]

[Djblois]

Thank you for the help. that works perfect

[lucas]

Daniel...You can remove the solved: by using the thread tools at the top of the page and select edit thread....just remove the solved: from the title and save the changes.