Solved: Change Number into text for Page Heading

[Djblois]

I am using code for a user to add a page Heading. The code works perfectly, except when the user types in a number then it will change the size of the font to the number the user typed in. Here is the code that I am using:

Sub SpeedyPageSetup()

Dim LeftHeaderText As String, centerHeaderText As String, rightHeaderText As String

LeftHeaderText = frmExtraTableFormating.LeftText.Value
centerHeaderText = frmExtraTableFormating.CenterText.Value
rightHeaderText = frmExtraTableFormating.RightText.Value

If Not LeftHeaderText = "" Then
With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Bold""&10" & LeftHeaderText
End With
End If
If Not centerHeaderText = "" Then
With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""&12" & centerHeaderText
End With
End If
If Not rightHeaderText = "" Then
With ActiveSheet.PageSetup
.RightHeader = "&""Arial,Bold""&10" & rightHeaderText
End With
End If

End Sub


I tried using some code that I was already using and adjusting it:

With Application.WorksheetFunction
fnCalcTime = " " & .Text(.Min(shtLookup.Columns(lgColumn)), _
"mm\\dd\\yy") & " - " & .Text(.Max(shtLookup.Columns(lgColumn)), _
"mm\\dd\\yy")
End With


However that second set of code is when I know the format the numbers will go in. The code that I am working on now, I have no idea what the format will be - I want it to be in the exact format that the user types it in. It can be the same as it is there "mm\\dd\\yy" or it can be "yyyy" or it can be 00.00. I have no idea, it can be any format.

[Bob Phillips]

Use the text property of the range that contains the value.

[Djblois]

It isn't a range, it is an Input box.

[Bob Phillips]

Then it must just be text string, so just output that,

[Djblois]

I don't understand because all I do is use that string in the pagesetup and if it is a number than it affects the font size.

[Djblois]

I found strConv in one of my books:

With ActiveSheet.PageSetup
.LeftHeader = "&""Arial,Bold""&10" & StrConv(LeftHeaderText, vbProperCase)
End With

I tried it and nothing is coming out when I use it.

[Tommy]

I had to add a space to get it to work.

If Not LeftHeaderText = "" Then
With ActiveSheet.PageSetup
.LeftHeader = "&""Arial""&B&10 " & LeftHeaderText
End With
End If

[Djblois]

Tommy,

Thank you, I never thought it would be something so simple. That works perfectly.