tiehaze
asked on
Using xldialogFormatFont in a userform
I am trying to setup a customized userform to replicate the xldialogpagesetup.
The part I am trying to recreate is the header part, where it have three textboxes, for the left, center and right headers.
How do I set it up so that xldialogFormatFont will actually edit the text for the textbox that is selected?
If you have any questions, let me know so I can answer them.
The part I am trying to recreate is the header part, where it have three textboxes, for the left, center and right headers.
How do I set it up so that xldialogFormatFont will actually edit the text for the textbox that is selected?
If you have any questions, let me know so I can answer them.
As a matter of interest why are you trying to replicate a built-in dialog?
ASKER
Because I need all of the features of the 'Custom Header' & 'Custom Footer' in the 'Page Setup' built-in dialog, but I also need two more custom buttons that are similar to the 'page number' & 'date' buttons. Any ideas?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Rory,
I am just using VBA in excel right now. Do I have to use the above link in Visual Basic?
I am just using VBA in excel right now. Do I have to use the above link in Visual Basic?
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Works great... two things I need altering though. First, how can I get cmdFont to change the font of the selected textbox, assuming that I am using the code that you gave me in the other question:
Private Sub CB_CycleFooter_Click()
Dim ctl As Control
Set ctl = GetActiveControl(Me)
If TypeName(ctl) = "TextBox" Then
ctl.SelText = "&[Cycle]"
End If
End Sub
But instead of adding "&[Cycle]", I want to edit the font for that textbox
Second question is about the code itself. When I edit the font of the textbox, it is replacing what is currently inside the textbox with the name and size of the font. How do I stop that from happening?
Private Sub CB_CycleFooter_Click()
Dim ctl As Control
Set ctl = GetActiveControl(Me)
If TypeName(ctl) = "TextBox" Then
ctl.SelText = "&[Cycle]"
End If
End Sub
But instead of adding "&[Cycle]", I want to edit the font for that textbox
Second question is about the code itself. When I edit the font of the textbox, it is replacing what is currently inside the textbox with the name and size of the font. How do I stop that from happening?
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Last question. For the userform I am working on, the textbox names are Header0, Header0, Header1, Header2, Header3, Header4. Is there anyway I can determine which one ctl is possibly with the following code?
UserForm31("Header" & i)
If I can get the value of i, that would be awesome.
UserForm31("Header" & i)
If I can get the value of i, that would be awesome.
ASKER
Nevermind, got it.
ASKER
Sorry, One more question. If I wanted to format Range("A1") the same way that I formatted Header0. How would I modify the following code:
Private Sub CB_CycleFooter_Click()
Dim ctl As Control
Set ctl = GetActiveControl(Me)
If TypeName(ctl) = "TextBox" Then
varRet = test_DialogFont(ctl)
End If
End Sub
Private Sub CB_CycleFooter_Click()
Dim ctl As Control
Set ctl = GetActiveControl(Me)
If TypeName(ctl) = "TextBox" Then
varRet = test_DialogFont(ctl)
End If
End Sub
ASKER
When I mentioned Header0, I ment to say ctl
You can loop through the relevant properties:
With Range("A1").Font
.Name = ctl.FontName
.size = ctl.FontSize
.UnderLine = ctl.FontUnderline
.Italic = ctl.FontItalic
End With
HTH
Rory
With Range("A1").Font
.Name = ctl.FontName
.size = ctl.FontSize
.UnderLine = ctl.FontUnderline
.Italic = ctl.FontItalic
End With
HTH
Rory
I'm not sure that will work. The xldialogFormatFont dialog only seems to act on the actual workbook, not the textbox. I think you may have to build your own font dialog.
Wayne