In my opinion, a mistake was made already at the conception stage of this project. If cell A7 is to store the date (as a number, not text), then care should be taken that the date is passed from the form, not the text. Note that TextBoxes (as well as several other controls) always store data in the form of text. When passing data from a control to a worksheet, force the appropriate data type (in this case, date).
The general rule for passing data to and from a form:
1. when passing from a worksheet to a form control, use the Format function, without a formatting parameter, e.g.
A short date should appear in the txtDate control according to Windows settings. You'll probably get 03/26/2024. Since I'm guessing you're using US date format, you could possibly pass the date to the control like this:
You'll get the short date: 03/26/24.
2. Now in the other direction, from the form to the sheet. Force the correct data type:
A common mistake is to try to force a format when passing a value, e.g.
You shouldn't do that, because text is passed to the cell, and there should be a date (the Format function returns text). If you want the cell to display the date in a different format (e.g. "mm/dd/yy") then you should format the cell accordingly, not the passed value! Implicit conversion of text to numbers on the VBA-Excel border is a separate issue. Do not rely on implicit conversion, as this can lead to errors. If you use the code:
my guess is that you will see 03/26/2024 in the formula bar, but if you want the cell to display a different format, format it accordingly.
Once you are sure that cell A7 stores the date, your code for exporting to a PDF file will look like this:
You don't have to add the extension in the file name, because you first force the type of the exported file (xlTypePDF).
Artik
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks