Format of Date - Saveas Issue

[trevolly]

Hi all,

I'm running a piece of vba code that opens a save as box and autofill's the filename. The vba references two cells to create the filename, one of these cells (C6) being the date. The date cell is filled in by the user of the form and may not be the current days date.

The cell format has been set to show the date as 26-07-23 for example but if the user enters the date in the format of 26/07/23 for example, the formatting of the cell corrects it to 26-07-23 but when the save as box opens, it shows it as what the user entered, 26/0723. This then causes windows to refuse to save the workbook.

Is there any way around this? The date of the cell isn't always the current day otherwise I could code the vba to show this.

Thank you all

Sub SaveFile()
Dim NameFile As Variant
With Worksheets("Form")
NameFile = .Range("C2") & " - " & .Range("C6") & ".xlsm"
End With
NameFile = Application.GetSaveAsFilename(InitialFileName:=NameFile, Filefilter:="Excel Workbook (*.xlsm), *.xlsm")
If NameFile = False Then
MsgBox "File Not Saved"
Else
ThisWorkbook.SaveAs Filename:=NameFile
End If
End Sub

 

[Joe4]

Try changing this line:

NameFile = .Range("C2") & " - " & .Range("C6") & ".xlsm"

to:

NameFile = .Range("C2") & " - " & Format(.Range("C6"),"dd-mm-yy") & ".xlsm"

 

[dmt32]

Hi
try using the Range.TEXT property

NameFile = .Range("C2").Text & " - " & .Range("C6").Text & ".xlsm"

and see if resolves your issue

Dave

 

[trevolly]

Wow - quick response! Thank you @dmt32 and @Joe4 - both worked! Appreciate your help.

 

[dmt32]

Wow - quick response! Thank you @dmt32 and @Joe4 - both worked! Appreciate your help.

most welcome glad issue resolved & we were able to help

Dave