Create font pick list on a form 1

[KMillar]

Hi,

My question is; is there a way of adding the font pick list and size controls to a form - like the one you see when you call up the "format cells" box by right clicking on a cell? And how do you capture the selections?

Thanks.

 

[rmikesmith]

The easiest way to implement this is to use Excel's built-in dialogs. Here is a simple procedure that displays the standard Font dialog you would see when selecting Cells|Format:

Sub DisplayFontDialog()
Dim dlgResult As Variant
   dlgResult = Application.Dialogs(xlDialogFontProperties).Show
End Sub

When this dialog is invoked, it references the current selection. Either have your code select the appropriate cells prior to calling or let the user make the selection. To do that from a modal Userform, use a RefEdit control then set the selection on exit. Example (code in the Userform's module):

Private Sub RefEdit1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   ActiveSheet.Range(RefEdit1.Text).Select
End Sub

Private Sub CommandButton1_Click()
   FontPickList
End Sub

I used a separate CommandButton to run the Font dialog display procedure.


HTH,
Mike

 

[KMillar]

Thanks Mike! I believe this is exactly what I was after. I'll give it a spin and let you know how it went.