Getting a runtime error when trying to pass date from userform to filename save as String

[richieH]

Hi ,

I am hoping that somebody can point me in the write direction, I am trying to use a user form to allow the user to select a date either in cell B6 or if they want todays date.

Depending on the selection then this is passed through to a save as macro and used as a string. the save as works with out this new addition but fails with a

The end of this error confusing to me as the date i am using is in the format dd-mm-yy so it can be used in a file name.

Here is my code it is the

Private Sub btnOk_Click()
    ' Set Values
    
Dim theDate As Date
    theDate = Format(Date, "dd-mm-yy")
   

Dim Project As String
Dim Name As String
Dim Sheet As String
Dim Version As String
Dim SaveString As String
Dim ws As Worksheet
Dim TD As Date

If Me.OptionButton1.Value Then
        theDate = Date
    ElseIf Me.OptionButton2.Value Then
        theDate = ActiveSheet.Range("B6")
        
        End If



Set ws = ActiveSheet

Project = ActiveSheet.Range("B1").Value
Name = ActiveSheet.Range("B2").Value
Sheet = ActiveSheet.Name
Version = ws.Cells(5, Columns.Count).End(xlToLeft)
TD = theDate 'Format(Date, "dd-mm-yy")
'Rows(4).Find("*", ws.[A4], xlFormulas, xlByColumns, xlPrevious) 'ActiveSheet.Range("B3").Value

'Sheet = Left(Sheet, Len(Sheet) - 2) 'Left(filelist, Len(filelist) - 2)


SaveString = Project & "_" & Name & "_" & Sheet & "_" & TD   '& "v" & Version

'Application.ActiveWorkbook.Path &

ActiveWorkbook.Saveas filename:=SaveString, FileFormat:=52      '52 for .xlsm

    ' Unload Form
    Unload Me
End Sub

it fails as this
ActiveWorkbook.Saveas filename:=SaveString, FileFormat:=52 '52 for .xlsm


any help appreciated as i can not for the life of me work it out

 

[Anthony47]

Try with a different Dim for theDate:

Dim theDate As String

 

[richieH]

Hi Anthony ,

I managed to work out how to do it. i am not sure what the first is not formatting the date corectly.

Dim theDate As Date
    theDate = Format(Date, "dd-mm-yy")

What i have done is use the variable defined in the time as i had before when i was just using todays date

TD = theDate 'Format(Date, "dd-mm-yy")

becomes

TD = Format(theDate, "dd-mm-yy")

Thanks for your response

 

[Anthony47]

I didn't understand if you "fixed it" or not, and how

In the filename you need to use Strings; a Date has its own format and you can see that the file name (in the error message) included a 29\06 (in the format of dir and sub dir) rather than "29-06-22"