CODEWITHSUNDEEP
excelvbavariable-assignmentuserform
Richard Hannigan
Richard Hannigan

Reputation: 53

I am getting a runtime error when trying to pass through a selection on user form to use the selection as part of the string for Filename

I am hoping that somebody can point me in the right direction, I am trying to use a user form to allow the user to select a date either: the date in cell B6 of the active sheet which is a fixed date manually inputted
or todays date.
I am using the Optionbuttons, i did also try with 2 command buttons so you just had to click once got the same error

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

enter image description here

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 VBA Code:

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   

'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

Upvotes: 0

Views: 30

Answers (1)

Richard Hannigan
Richard Hannigan

Reputation: 53

After being looking at how it was working previously before the choice i have use the thedate variable instead of Date

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

I have replace the Date with the varable created so it becomes

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

I thought that I had specified this at the start when defining the variable

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

Not sure why this is not not carrying through and i have to respecify any ideas

Upvotes: 0

Related Questions