Color/font picker

[fraz627]

I'm working on a project which the user can change the Text, color, font of a shape, similar to the font tab. How can i access the dialog and return values to the shape and a cell.
Thanks

 

[Micron]

Not sure what you're asking. You want vba to open a font dialog and set the shape textframe font color to the chosen value and put that value in a cell? What will trigger that - a mouse click on the shape? AFAIK, you would have to take that approach because if the user opens the dialog from any menu I don't think that action is an event that you can use to execute any code. If there is a way to do that I don't know it, but using WithEvents comes to mind. However, at present that's a bit beyond my skills.

 

[6StringJazzer]

I was with you up until "and a cell". Can you describe in more detail what you need?

There are dialogs you can call from VBA to directly apply formatting to a cell, but I can't find any that return a formatting value that you can apply to a shape. That is, you call the dialog and whatever the user picks is applied to the active cell; the code never gets to find out what was applied.

Or I suppose you could designate a cell, apply the formatting to it, then query it for the formatting and re-apply it to your shape.

But we need to understand a little more about what you're doing.

 

[Micron]

As an experiment, I was able to raise a dialog to allow changing a shape text frame font colour then msgbox what that colour became. So msgbox could be replaced by altering a cell value to that colour number. However, I'm not certain if the dialog I used affects anything other than the shape. It does not seem to.

 

[6StringJazzer]

I was able to raise a dialog to allow changing a shape text frame font colour

Can you please share the code?

 

[Micron]

Sure. These are in a standard module. Excel seems to choose that when assigning a new macro to a shape rather than putting the code in the sheet module.

Sub Star5Points2_Click()
Dim lngColorOld As Long
Dim shp As Shape

Set shp = ActiveSheet.Shapes("5-Point Star 2")
lngColorOld = shp.TextFrame.Characters.Font.Color
If Application.Dialogs(xlDialogFontProperties).Show Then
    With Selection.Font
        'MsgBox .Color
        shp.TextFrame.Characters.Font.Color = .Color
    End With
    If lngColorOld <> shp.TextFrame.Characters.Font.Color Then
        MsgBox "font color changed to " & shp.TextFrame.Characters.Font.Color
    End If
End If
Set shp = Nothing

End Sub

What I find odd and a bit difficult is that when you assign a new macro to a shape, the name of the shape usually doesn't match what's in the first code line. This should help with that problem:

Sub getNames()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
    Debug.Print shp.Name
Next

End Sub

Neither message box is required. Only there to aid in testing.

 

[fraz627]

I was with you up until "and a cell". Can you describe in more detail what you need?

There are dialogs you can call from VBA to directly apply formatting to a cell, but I can't find any that return a formatting value that you can apply to a shape. That is, you call the dialog and whatever the user picks is applied to the active cell; the code never gets to find out what was applied.

Or I suppose you could designate a cell, apply the formatting to it, then query it for the formatting and re-apply it to your shape.

But we need to understand a little more about what you're doing.

What I'm working on is a Point of Sale system with many Shapes(KEYS), I would like for the End User to be able to change the Shape back ground color, font Size and color without using the standard shape formatting procedures. the current plan is to call up a user form to for user to edit the shape.
I only need away to call of the color picker for Font and background and Return the value into a variable.

I sometimes have putting my brain on a keyboard.
Thanks