0

I have a long chunk of code that handles date data from a hidden datasheet, however anytime I enter a new date trough a userform, it writes it into the cell in the database in a format that for some reason can't be handled error free by my code chunk.

The problem is I need a sub to change the format (which is easy) but it does not actually apply the formatting unless I manually go into the datasheet, enter the cell and press enter.

I have searched and could not find a solution on how to do this in VBA, obviously this code is supposed to run automatic flawlessly plenty of times and having to manually go in an enter the cell defeats the entire purpose of the programming.

Sub Testing123()
MsgBox "run"

Workbooks("Excel Stock System.xlsm").Worksheets("DataNews_Events").Columns(8).NumberFormat = "dd.mm.yyyy  hh:mm"
'something magic that actually automatically applies the format




End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
Amelung Schwenke
  • 5
  • 3
  • 1
    You're working with *text-that-looks-like-a-date* probably. Changing the number format doesn't change the underlying value. – BigBen Jan 07 '21 at 16:57
  • @BigBen is correct. You need to change the format of the number/date when it's pushed *into* the database. – Scott Holtzman Jan 07 '21 at 16:58
  • 1
    Like @BigBen said, if you're working with a date that's actually a text string, when you enter it into a cell manually, Excel will automatically parse the string to an actual datetime. If you're entering it through VBA, you need to explicitly parse it yourself. You can use the `cdate` function to make a "best guess", the same way Excel does when you enter it manually. See https://stackoverflow.com/questions/37139019/excel-vba-convert-date-string-to-date – Josh Eller Jan 07 '21 at 17:01
  • Thank you guys, @Josh Eller that was very helpful , i did not know this was a thing. Worked great – Amelung Schwenke Jan 07 '21 at 18:11

0 Answers0