Dialogs(xlDialogFontProperties) Arguments

[saeedfiroozei]

Hi everyone,
When i run The code
#Application.Dialogs(xlDialogFontProperties).Show "Calibri", "Bold", "15", False, , , , , xlUnderlineStyleSingle, 3
it works Fine. but Similarly on these lines of code:

#Application.Dialogs(xlDialogFontProperties).Show "Calibri", "Bold", "15", False, , , , , xlUnderlineStyleNone, 3
or
#Application.Dialogs(xlDialogFontProperties).Show "Calibri", "Bold", "15", False, , , , , xlUnderlineStyleDouble, 3

i get Error '1004' (Show method of dialog class failed). Does anyone know the reason?
Thx A lot.

 

[Micron]

I think you have one too many commas in there. According to the documentation I read it looks like it's ok, but it seems to work if I remove just one.

 

[saeedfiroozei]

I think you have one too many commas in there. According to the documentation I read it looks like it's ok, but it seems to work if I remove just one.

Thx Micron. I did it according to Microsoft document by this link:

Built-In Dialog Box Argument Lists

Office VBA reference topic

learn.microsoft.com

 

[Micron]

That is what I read also. I trimmed back the options until it worked then added until it didn't. What worked was to eliminate one comma.

 

[saeedfiroozei]

Sorry. I tried many times but it was not successful. could you please give me your sample?

 

[Micron]

This

Sub Macro1b()
   Application.Dialogs(xlDialogFontProperties).Show "Calibri", "Bold", "15", False, , , , xlUnderlineStyleNone, 3

gave me this. Now that I've created this post, I see that it picked double underline. So by "not successful" do you mean you still get the error, or no error but incorrect formatting results? I get no error

Using the macro recorder and choosing single underline the code it generated was this:

    With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .SuperScript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        '.Underline = xlUnderlineStyleSingle
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
Application.Dialogs(xlDialogFontProperties).Show "Calibri", "Bold", "15", False

I haven't tried that style of coding but will play around and see if I can make it do something with the selection (correctly).

 

[saeedfiroozei]

This

Sub Macro1b()
   Application.Dialogs(xlDialogFontProperties).Show "Calibri", "Bold", "15", False, , , , xlUnderlineStyleNone, 3

gave me this. Now that I've created this post, I see that it picked double underline. So by "not successful" do you mean you still get the error, or no error but incorrect formatting results? I get no error
View attachment 119413

Using the macro recorder and choosing single underline the code it generated was this:

    With Selection.Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .SuperScript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        '.Underline = xlUnderlineStyleSingle
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
Application.Dialogs(xlDialogFontProperties).Show "Calibri", "Bold", "15", False

I haven't tried that style of coding but will play around and see if I can make it do something with the selection (correctly).

I tried Many scenarios but every time there was a problem. sometimes method of dialog class fails and sometimes it open, but every time one or two property of font were not correct. for example sometimes i could not get the true color, sometimes underline and etc. Finally i wrote a sub to copy all of font properties to a specific cell and then make it as activecell and then simply call the dialog font properties. it works fine. thx for the Idea.

 

[Micron]

FWIW to anyone in the future, I found that this works (except 3 or 4 makes no difference for the colour option)
Application.Dialogs(xlDialogFontProperties).Show "Calibri", "Bold", "14", True, False, False, False, xlUnderlineStyleDouble, 3
but if you specify 13 for font size it doesn't work for that font.

Not sure what the advantage of this is in the end. As far as I can see, it can only serve to preload the dialog with settings, which would be a minor thing at best. Sometimes complicated approaches are not worth it in the end (like when you can present a dialog and just let users click the settings) because complicated methods are prone to failure. I think that I'd just run code on the active cell (or a range) and not bother with the dialog. Code alone would replace the dialog, and I don't see the point in opening a dialog with code instead of just applying the format in the cell via code.