Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel Userform

0

Hi Excel Userform Experts,         

Issue with Dates from a Userform to send to Sheet1.

I have a two question on a Userform which been hampering the brains out a bit now. Using Excel 2013 for a database, where I'm trying to send a date from Textbox to sheet1 Column 'M'. I need to have cells formatted in Sheet1 Column 'M', from cell 9 to cell 450 in "dd/mmm/yyyy".

I'm entering a short date in the textbox as "dd/mm/yy".

I used this vba code formula to send date to Sheet1 which worked perfect in a single cell only:

Sheet1.Range("M9").Value = Format(TextBox1.Value, "dd /mmm/yyyy")

This worked very well for the first single cell, but when I changed the formuls to

Sheet1.Range("M9:M450").Value = Format(TextBox1.Value, "dd /mmm/yyyy")

This worked as well in a way but it changed ALL the cells in the Column 'M' to the same date.

How can I format the Column "M' cells indivigually.

2.- There other question is I have a column which needs to add "Years" to a date to make 'Due Date',

I used this formula for each cell in Column L were I need the due date to go in Sheet1 for each cell:-

=DATE(YEAR(H9) + L9, MONTH(H9), DAY(H92))

'H9' is the install date and 'L9' is the next number of years the result goes into cell 'N9' as the Due Date.

I formatted Column 'N' with the above vba code but when I enter the date in the Userform textbox it doesn't work, I also need this 'N9 to N1000' to be formatted in 'dd/mmm/yyyy'

I will upload two samples

I thank you in advance for any assistance in this issue.

Answer
Discuss

Answers

0
Selected Answer

This is a Q & A forum, meaning one question, one answer. I shall answer your first question. Please create another thread for any more questions you may have. Thank you.

Sheet1.Range("M9:M450").NumberFormat = "dd/mmm/yyyy"

The above code will set the number format for range M9:M450. However, such code doesn't appear logical because there is no need to set the NumberFormat for the entire range every time. You can do that without code. Click Format > Cells > Number [tab] and select or set the format you want. On the other hand, if the target is changing all the time or you are worried that users might mess up your formats you would just set the NumberFormat for the cell you currently write to.

Sheet1.Range("M9").NumberFormat = "dd/mmm/yyyy"

Assigning a value to the cell is a different operation and one requiring your careful attention. A TextBox, as the name implies, contains text. Even its Value property is text. VBA's Format() function expects a number as input and outputs a string. Format(TextBox1.Value, "dd /mmm/yyyy") therefore requires VBA to do a few acrobatics. The text from the TextBox must be converted to a date (which is a number) and then formatted according to the mask provided. This conversion will only work as long as you enter the date in the TextBox in a format your computer recognises as a date. That implies the use of the correct date separator and the correct d-m-y sequence.

The output of Format(TextBox1.Value, "dd /mmm/yyyy") is a string. It's this string that is written to the worksheet. Of course, a string is text and can't, therefore, be a proper date with which you can do calculations. Your basic approach is correct in that you apply a cell format to determine how to display the date. However, this will not be successful if you don't supply a date, meaning, a number. The formula below would do that.

Sheet1.Range("M9").Value = CDate(TextBox1.Value)

This code will insert a proper date in the worksheet provided there is a date in the TextBox. The display format of that date is determined by the cell's NumberFormat property. Whether you enter 4-10-18 or 4/10/18 in the TextBox will determine whether or not the entry is recognised as a date at all. The difference depends upon the Regional Settings - outside Excel and VBA - as does the question of whether this date is recognised as being in April or October. The bright side is that once VBA accepts an entry as a date it will also know how to convert it in the CDate() function, applying the same d-m-y sequence. This would enable you to apply a test at the time of entry and reject input not understood as a date. You will have to trust the user to know whether to input day or month first.

Combining the two lines of code you would end up with this:-

With Sheet1.Range("M9")
    .Value = CDate(TextBox1.Value)
    .NumberFormat = "dd/mmm/yyyy"
End With
Discuss

Discussion

Hi, May I first thanks kindly; It seem that the code is doing nearly what I'm after, but, if I enter the date in the Textbox as 04/10/18 I'm getting in the sheet1 10 Apr 2018. I've checked all the cells in the column and they all are formatted to dd mmm yyyy.
jdgrapes (rep: 14) Oct 4, '18 at 5:04 am
Please check the default date format set in your computer's Regional Settings (Control Panel > Clock, Language and Region). The Short Date specified there must be dd/mm/yyyy so that you can enter 4/10/18 and get 4 Oct 2018.
Variatus (rep: 4889) Oct 4, '18 at 6:19 am
God's mill stones grind slowly but incredibly fine. Thank you for accepting my answer a week before its anniversary. :-)
Variatus (rep: 4889) Sep 28, '19 at 3:53 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login