0

I have a userform which requires date input and up until today hasn't caused an issue (Used for the last 2 weeks).

When the date is input it posts the information to a staging sheet that is visible on a printable page.

The format that originally worked was - 

ActiveCell.FormulaR1C1 = TextBox4.Value

But with the date now being 03/11/2014 it is switching this round to 11/03/2014.

I then tried to change this code to format it as a date e.g. 

ActiveCell.FormulaR1C1 = Format(TextBox4.Value, "DD/MM/YYYY")

But again the date showed as 11/03/2014.

Does anyone have a solution to this date format error?

Thanks Al

Alan Treanor
  • 157
  • 4
  • 19
  • [Interesting read](http://stackoverflow.com/questions/12012206/formatting-mm-dd-yyyy-dates-in-textbox-in-vba/12013961#12013961) – Siddharth Rout Nov 03 '14 at 12:56

2 Answers2

1

I found my dates worked correctly by using CDate()

With ActiveCell
    .Value = CDate(TextBox4.Text)
End With

Then it respects whatever my sheet formatting is. Attempting to format using code (i.e. .NumberFormat = "dd/mm/yyyy") just wasn't making any difference.

Marcello B.
  • 4,177
  • 11
  • 45
  • 65
LiceRewis
  • 103
  • 6
0

What I would do to avoid any problem with date formatting is:

With ActiveCell
    .Value = TextBox4.Text
    .NumberFormat = "DD/MM/YYYY"
End With

Like this you make the shown value into the cell independent from how it is inserted by the user, as long as it's a date-time value.

P.s. I have arbitrarily removed your .FormulaR1C1 since you are inserting a value, not a formula, so there's no point to insert your textbox value into the formula property of the active cell.

Matteo NNZ
  • 11,930
  • 12
  • 52
  • 89
  • I have tried this and for some reason it is still giving the same date error. @Matteo – Alan Treanor Nov 03 '14 at 14:19
  • Your code contains reference to `ActiveCell`, which means the currently selected cell. Are you sure you're not expecting your code to change a cell which is not the active one? I have tried this code and it works, cannot figure out why it shouldn't with you. Make sure also that you're not still using the `.FormulaR1C1` but rather the cell `.Value`. – Matteo NNZ Nov 03 '14 at 14:27
  • I am activating the cell I require just before this runs - 'Range("M2").Select' @Matteo – Alan Treanor Nov 03 '14 at 15:03
  • Ok @AlanTreanor, last attempt. Can you re-try the code I have edited? I have changed from `textbox4.Value` to `textbox4.Text`, since the second property is the current content while the first might be stored differently (if it doesn't work, consider posting some more code running after the `.NumberFormat` change. – Matteo NNZ Nov 03 '14 at 15:10
  • Unfortunately this isn't working still I then tried using date 13/11/2014 and this worked but then 04/11/2014 showed as 11/04/2014. This is strange that it is changing the format.@matteo – Alan Treanor Nov 04 '14 at 08:32
  • Try `.NumberFormat = "dd/mm/yyyy"` it might be the CAPS in the format – Jean-Pierre Oosthuizen Dec 08 '15 at 13:14