• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Change date format in ActiveX combo box

mf1

Member
Hi,

I having some problems with formatting a date in a ActiveX combo box.

Built
ActiveX combo box to show a list of dates in ddd, d mmmm format

Problem
Even though the combo box dates are appearing in the correct format (Mon, 15 June), the dates are returning as numbers (42170)

I have checked everywhere in the properties to see if I can change the format, and can't find anything.

Do I need to write some code? If so, I may need some help. I have attached a sample file.

Many thanks,

mf1
 

Attachments

  • ActiveX Date Format.xlsx
    13.1 KB · Views: 12
MF1

goto VBA (Alt+F11)
Select the Date sheet
Post this code in the code pane
Code:
Private Sub ComboBox1_Change()
  Application.EnableEvents = False
  ComboBox1.Text = Format(ComboBox1.Value, "ddd, d mmm")
  Application.EnableEvents = True
End Sub
goto back to Excel (Alt+F11)

enjoy
 
Hey Hui,

Im trying to add 1 to Linked cell to automatically create the next day on a spreadsheet (i.e. Tuesday, Wednesday, Thursday, Fri...) and its coming back with #VALUE! error. ( I used this technique when using data validation drop down boxes)

I have played around with format back hasn't made any difference.

I have attached the example.

Cheers,

mf1
 

Attachments

  • ActiveX Date Format.xlsm
    19.3 KB · Views: 4
O4: =INDEX(N6:N23,MATCH(O3,TEXT(N6:N23,"ddd, d mmm"),0),)
A9: =O4
D9: =A9+1
G9: =D9+1
J9: =G9+1

See attached file:

The Macro is setting the ComboBox Text as Text and so it has lost its date
O9 finds the date, which is actually a number
 

Attachments

  • Copy of ActiveX Date Format.xlsm
    19.7 KB · Views: 7
MF1

goto VBA (Alt+F11)
Select the Date sheet
Post this code in the code pane
Code:
Private Sub ComboBox1_Change()
  Application.EnableEvents = False
  ComboBox1.Text = Format(ComboBox1.Value, "ddd, d mmm")
  Application.EnableEvents = True
End Sub
goto back to Excel (Alt+F11)

enjoy
I am new to Chandoo.org and I am enrolled in the dashboard classes. I have been searching for a way to modify the date formatting for my active x combobox and your code was the only one i could get to work.

Thanks!
 
Wow Thank you so much! Using the TEXT function is a million times more efficient and easier for a non-VBA person, like myself. This will be a big help and will make this a lot easier for me in the future.

Thank you very much
 
MF1

goto VBA (Alt+F11)
Select the Date sheet
Post this code in the code pane
Code:
Private Sub ComboBox1_Change()
  Application.EnableEvents = False
  ComboBox1.Text = Format(ComboBox1.Value, "ddd, d mmm")
  Application.EnableEvents = True
End Sub
goto back to Excel (Alt+F11)

enjoy


Hi, I used your code and it worked perfect, except one date that is from last year. It will not convert. Any idea as to why?

update: The format I was using was mm/dd/yyyy. I switched to mmm/dd/yyyy and it is working. I noticed that this date is the only one with 2 numbers on the month field. I don't really want mmm or DEC instead of 12 but I can work with it.
 

Attachments

  • Employee overtime review cleaned test.xlsm
    100.2 KB · Views: 4
Last edited:
Back
Top