PDA

View Full Version : Solved: Speedy PageSetup



Djblois
03-23-2007, 12:34 PM
I created a quicker pagesetup with a form and all and I can't get this part to work:

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

I get Arial, bold as my headers

lucas
03-23-2007, 02:35 PM
I'm really lost Daniel.....you formatted the header as Arial Bold and then you question why that is the result.....

mdmackillop
03-23-2007, 02:40 PM
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

Djblois
03-26-2007, 08:15 AM
Thank you mdmack,

Sometimes I feel like an idiot!!! lol

Djblois
03-26-2007, 12:56 PM
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:

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

Norie
03-26-2007, 01:14 PM
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
03-26-2007, 01:23 PM
This is what I am getting (&"Arial,Bold"&10&"Arial,Bold"&10test) The code for the page formating.

lucas
03-26-2007, 01:25 PM
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
03-26-2007, 01:34 PM
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
03-26-2007, 01:49 PM
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
03-26-2007, 01:56 PM
Daniel

Why do you find this unusual?

Those are the values of those properties.

Djblois
03-26-2007, 02:04 PM
Ok, Madmack

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

Djblois
03-26-2007, 02:19 PM
Here is the test book

mdmackillop
03-26-2007, 03:43 PM
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.



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

Djblois
03-27-2007, 05:17 AM
Thank you for the help. that works perfect

lucas
03-27-2007, 07:37 AM
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.