0

I've spent some time looking on SO for an answer but cannot find it.
I have a spreadsheet with contact details on it. I have a VBA macro that extracts the data for a contact from the spreadsheet and presents it on a userform in a series of textboxes so that it can be checked and updated. One of the columns contains a date. The code used it is:

    .tb21 = Format$(Cells(intRowUID, 21).Value, "dd/mm/yyyy")

When the user clicks [OK] the code that writes it back to the spreadsheet is:

    Cells(intRowUID, 21).NumberFormat = "dd/mm/yyyy"
    Cells(intRowUID, 21) = Format(.tb21.Value, "dd/mm/yyyy")

This appears to work but when I go the spreadsheet and try to filter on the date Excel does not recognised it as a date and it is shown on the filter dropdown at the bottom as what appears to be text.
Filter dropdown

If I click on the formula bar and then click on the [tick] excel then recognises it as a date and the filter works OK.
The cell is formatted as a date as "dd/mm/yyyy".
How can I make Excel recognise the date?

MikeK
  • 78
  • 6
  • Try to format the cells as dates manually before running the code. – DirtyDeffy Jun 14 '18 at 09:49
  • @DirtyDeffy The cell is already formatted correctly. The changes made by the code do not change that. I have edited the above to reflect this. – MikeK Jun 14 '18 at 09:53

1 Answers1

2

Use

.tb21 = Cells(intRowUID, 21).Value

and make sure the format of the textbox is a date format (in the textbox properties).
Then rewrite using

Cells(intRowUID, 21).NumberFormat = "dd/mm/yyyy"
Cells(intRowUID, 21) = .tb21.Value

Using Format$ like you show in your code, is turning a date into a string.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • The Format$ is there to get the date correct in the textbox. If I remove it the date is shown as "4/30/2018" in the textbox – MikeK Jun 14 '18 at 10:07
  • I think that's answered the spreadsheet formatting problem. I now need to know how to: "format of the textbox as a date format." – MikeK Jun 14 '18 at 10:14
  • Solved thanks to @Patrick reminding me that the textbox value was a string. Converting the string back to a date is given here [link](https://stackoverflow.com/questions/31903449/excel-vba-error-with-dates-on-sheet-and-textbox) – MikeK Jun 14 '18 at 10:37