Results 1 to 11 of 11
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Obtain name from FONT dialog (Excel97/SR2)

    I'm at my wit's end (never far away nowadays), and can't see what I'm doing wrong.

    It ought to be a simple matter to throw up a common FONT dialog box and retrieve the end-user's chosen settings, but Excel balks.

    I can find no useful example in the Excel/VBA help files; a search of the Lounge didn't turnip anything useful (could be my pig-headedness) and I couldn't find anything to clue me in in MSKB.

    I want the command button on my GUI to solicit name, size, strikethrough etc etc and then I'll preserve the values in a registry setting for use later (in formatting my charts).

    Any hint would be appreciated - or a link to a post or MSKB article with a sort of step-by-step guide for the terminally dense. I'm just going to step out and buy a copy of "Idiots Guide To Dummies". That's how i feel .....

    Thanks

    <pre>Private Sub cmdTitleAppearance_Click()
    ' xlDialogFontProperties font, font_style, size, strikethrough, superscript, subscript, outline,
    ' shadow, underline, color, normal, background, start_char, char_count
    With Application.Dialogs(xlDialogFontProperties)
    .Show
    Dim strName As String
    strName = .Font ' Object doesn't support this property or method (Error 438)
    End With
    End Sub</pre>


  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Obtain name from FONT dialog (Excel97/SR2)

    Chris, I think your only way to this with straight forward VBA would be to query the font property of the selected object after Application.Dialogs(xlDialogFontProperties).Show has been executed. In order for the Font Properties Dialog to display, an appropriate object must be selected. If the user clicks Ok , the Show method returns True so you might use something like : <pre> dlg = Application.Dialogs(xlDialogFontProperties).Show
    If dlg Then
    With Selection.Font
    strName = .Name
    'etc ...
    Debug.Print strName
    End With
    End If</pre>


    Andrew

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Obtain name from FONT dialog (Excel97/SR2)

    You mean, why doesn't Excel VBA work like Word VBA? I have no idea. From playing with it and re-reading the online help over and over, it appears that those arguments listed in Help are only for use as input parameters, and cannot be used to "read" information out of the dialog object. Quite inexplicable.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Obtain name from FONT dialog (Excel97/SR2)

    Chris, the Font Dialog in Excel returns whatever is in the active cell, because the user can change Font in every cell they wish, so you could query the Activecell for .Font and it's child properties, and .Style. There are also

    Application.StandardFont
    Application.StandardFontSize

    and the Workbook.Styles Collection, including my personal friend "Normal". <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Obtain name from FONT dialog (Excel97/SR2)

    Andrew, thanks for this. I'm not going crazy, right?

    I modified your example and got it working, and then essayed in Word for good measure. I reproduce the two for comparison here.

    Bottom Line for me is that in Excel I seem to be forced into obtaining a font name, size, colour etc. from a cell.

    My need is to solicit Font information to be stored in a session variable in preparation for other work, so obtaining parameters from a cell isn't going to cut it. (later: maybe that WOULD be better, it would make the end-user key in samples of text, and I'd just grab the parameters from their trial displays. Their samples would be a mild form of sign-off)

    I'll probably dig out my own, much earlier GUI for fonts and tart it up a bit to make a generic cross-application font-picker.

    <pre>Sub testWORD97()
    Dim dlg
    Set dlg = Application.Dialogs(wdDialogFormatFont)
    With dlg
    Dim res
    res = .Show
    If res Then
    With Selection.Font
    Dim strName As String
    strName = .Name
    'etc ...
    Debug.Print strName
    End With
    End If
    End With
    End Sub


    Sub testEXCEL97()
    Dim dlg
    Set dlg = Application.Dialogs(xlDialogFontProperties)
    With dlg
    Dim res
    res = .Show
    If res Then
    With Selection.Font
    Dim strName As String
    strName = .Name
    'etc ...
    Debug.Print strName
    End With
    End If
    End With
    End Sub
    </pre>


  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Obtain name from FONT dialog (Excel97/SR2)

    Jonothan, thank you for re-assurance that I'm not going crazy. Also please see my response to Andrew.

    In terms of my crude knowledge of VBA Classes, it seems that the Excel-Font GUI writer created Get but not Let events.

    It is odd, nowadays, because I figure that just about any regular dialogue box (fonts, files, etc.) ought to be a harvest field for appliaction-available data.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Obtain name from FONT dialog (Excel97/SR2)

    John, thanks for this response. Please see too my response to Andrew.

    I was thrown by expecting that the Font dialog in Excel would work just like the Font dialogue in Word. (I know; I'm old enough to know better ....)

    I'm looking for a way to solicit requirements from end-users, so I'm probably going to resurrect my old Font-Picker GUI form. Again.

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Obtain name from FONT dialog (Excel97/SR2)

    Chris, if you enjoy the challenge of working with Dialogs that don't return anything, you need to work with Outlook for a bit! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Obtain name from FONT dialog (Excel97/SR2)

    > you need to work with Outlook for a bit!

    Nah. I'm made of sterner stuff.

    Eudora for me!

    It does seem a shame that, at least in the four major Office products, there wasn't a standard for dialogue boxes that said "any dialogue box can be used as an item-browser for developers". It's no good MSoft saying "yeah, but who'd use this?", because [sad experience of life] as soon as we decide that there'll never be a use for something, someone comes up with a use for it.[/sad experience of life] .

  10. #10
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Obtain name from FONT dialog (Excel97/SR2)

    If you run out of experiments, you could try using the Word dialog in your Excel project. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Obtain name from FONT dialog (Excel97/SR2)

    > you could try using the Word dialog in your Excel project.

    Knock yerself out! (attached)

    (This stripped-down workbook from a larger application is a platform for a crude method of soliciting formatting characteristics from an end user.)

    Appearance Pickers

    We introduce

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •