Create Input Box / Save File As

**Leith Ross** · 12-18-2009, 05:37 AM

Hello Andy,

I will work on that in the morning. I'm heading off to bed now. Thanks for the input.

**Smythe78** · 12-19-2009, 04:36 AM

That would be great thanks

**Smythe78** · 01-11-2010, 07:08 AM

Hi there. Apologies for thread bump but back at work and still struggling with same problem!

I am looking for some macro code to create an Input box in excel which will prompt the user to enter a specific date (not todays date so NOW function is not appropriate). This will in turn cause the file to be saved in a specific folder with the date becoming the name of the saved file.

Any suggestions would be greatly appreciated

Cheers

**Leith Ross** · 01-11-2010, 04:05 PM

Hello Andy,

Bliadhna Mhath Ùr!

Glad you bumped the post. This macro should be closer to what you want. This will save a copy of the workbook the macro is in to the directory you specify and using a name you provide containing the date the user entered. The parameters for the file and folder are marked in blue.

Sub Macro1A()

  Dim InputDate As Variant
  Dim FileType As String
  Dim myFileName As String
  Dim myFolder As String
  
    FileType = ".xlsm"
    myFileName = "Report"
    myFolder = "C:\Chambers-Smith\documents\work"
    
EnterDate:
      InputDate = InputBox("Please enter date of Report")
      If InputDate = "" Then Exit Sub
    
      If Not IsDate(InputDate) Then
         MsgBox "The date you entered is not valid"
         GoTo EnterDate
      End If
    
      InputDate = " " & Replace(InputDate, "/", "-")
      ThisWorkbook.SaveAs myFolder & "\" & myFileName & InputDate & FileType
      
End Sub