Using VBA to change ActiveX Button Caption w/Formatting

[WSBirch]

Good morning!

I've been able to change the caption of my ActiveX Buttons using the below; However, is there no way to change the formatting in the way that the text is displayed?

Worksheets("Snapshot").CommandButton2.Caption = Worksheets("ValueShipped").Range("Q2")

For context, cell Q2 is a WORKDAY formula then the cell is formatted with a custom "ddd, mmm dd" to display Thu, Sep 14. The VBA to change the caption of the button displays 9/14/2023, but I would like it to display the custom number formatting.

WORKDAY(TODAY(),1,ValueShipped!$F$2:$F$300)

I've tried using this VBA but it doesn't work. Throws an error. Is there a way for this to work?

Worksheets("Snapshot").CommandButton2.Caption = Worksheets("ValueShipped").Range("Q2").NumberFormat = "ddd, mmm dd"

 

[ranman256]

= format(Worksheets("ValueShipped").Range("Q2").value,"ddd, mm dd")

 

[WSBirch]

= format(Worksheets("ValueShipped").Range("Q2").value,"ddd, mm dd")

Fantastic, that worked perfect. Thank you!!