Create Input Box / Save File As

**Smythe78** · 12-18-2009, 04:45 AM

Nae bother. You dont want a broken furnace at this time of year!

Thanks for that. That code works great but not quite what I am looking for.
To be more specific I suppose I would like an input box for user to enter date because I dont want to give the user a choice of what the file is called or where it is stored.
So for example I would like the Input box to say 'Please enter date of Report' and once the date has been entered in box and OK has been clicked , the macro would then go on to Save As in a pre-specified folder. (for purpose of example folder would be C:\Chambers-Smith\documnets\work)

Andy

**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