User Form Label format (date)

BenMelrose

New Member
Joined
Sep 24, 2019
Messages
3
Hi all. Long time watcher first time poster.

Have searched he’d for a few days and am stuck on the below

I have a user form that populates Labels with a date from a cell in a worksheet

the date in the worksheet is in standard format. dd/mm/yy and is imported to the user form with no issue.

The question is how can I format the Userform Label box to only show this date at mmm yyyy

this is the text in the VBA that populates the label

ADD_ACT.Label5.Caption = Worksheets("LIST SHEET").Range("L4").Value

this correctly fills the label with dd/mm/yy from the cell L4

i have tried the below

ADD_ACT.Label5 = Format(Date, "mmm yyyy")
ADD_ACT.Label5.Caption = Worksheets("LIST SHEET").Range("L4").Format

this correctly formats the date on the userform as mmm yyyy

BUT the form then freezes with runtime error 438 “Object doesn’t support this property or method”

thus us I am lost.

Ben.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
There doesnt seem anything wrong there. What line is causing the error? If you say it correctly formats the date then it must be a subsequent line.
 
Upvote 0
There doesnt seem anything wrong there. What line is causing the error? If you say it correctly formats the date then it must be a subsequent line.

steve. Thanks for the reply.


ADD_ACT.Label5 = Format(Date, "mmmm yyyy")
ADD_ACT.Label5.Caption = Worksheets("LIST SHEET").Range("L4").Format

Error is on second line. Correct. Got me stuffed ???

if I change the last word from format to value. It works again but does not format the date. ?
 
Upvote 0
Oh i never noticed that. You can do it all in one line:

Code:
ADD_ACT.Label5.Caption = Format(Worksheets("LIST SHEET").Range("L4").Value, "MMM YYYY")
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top