User Form

[oldjay]

I need a UserForm (or something) that will prompt the user for a name to save
the file.
Then have 3 options 1. Save to the C: drive. 2. Save to the C rive and
Server3 3. Cancel

 

[Joel]

You don't need a userform with this code.

Sub test()

fileSaveName = Application.GetSaveAsFilename( _
Title:="Get Save Filename", _
fileFilter:="Excel Files (*.xls), *.xls")
If fileSaveName = False Then
MsgBox ("Can't get Filename - Exiting macro")
Exit Sub
End If


Do
ValidResponse = True
Response = InputBox(prompt:= _
"Enter where you want to save the file" & vbCrLf & _
"1) Save to C: Drive" & vbCrLf & _
"2) Save to C: and Server" & vbCrLf & _
"3) Cancel")

Select Case Response
Case "1":

Case "2":

Case "3":

Case Else
MsgBox ("Bad Response - enter choice again")
ValidResponse = False

End Select
Loop While ValidResponse = False
End Sub

 

[oldjay]

Sorry didn't make myself very clear. This is the code I have now

'Revised saved order 6/09/08
quotenumber1 = InputBox("Please enter QUOTE file name to save to your C
drive & Server3", _
"Technologies LLC", NUMBERSAVE)
QUOTE = "C:\Quick Quotes3\" & NUMBERSAVE & ".XLS"
ActiveWorkbook.SaveAs Filename:=QUOTE

On Error GoTo ehandler2

QUOTE1 = "\\server3\jobs\estimate1\Quick Quotes3\" & NUMBERSAVE & ".XLS"
ActiveWorkbook.SaveAs Filename:=QUOTE1
ActiveWorkbook.Close
'End 6/09/08 revision

I want to modify it so that It asked for the file name if it is other than
the one displayed. The user would accept the default or type in a new name
then select whether to save to the C drive or the C drive and to server3

I hope this clears up what I want to do

oldjay

 

[Joel]

The code below handles a number of different case. See if you need any
changes. NUMBERSAVE wasn't define in your posting so I'm not sure if any
changes are needed.

Sub test()

Folder = "C:\Quick Quotes3"
Network = "\\server3\jobs\estimate1\Quick Quotes3\"
Quote = Folder & "\" & NUMBERSAVE & ".XLS"

'make sure dir exists to prevent errors
FName = Dir(Quote, vbDirectory)
If FName = "" Then
fileSaveName = Application.GetSaveAsFilename( _
Title:="Get Save Filename", _
fileFilter:="Excel Files (*.xls), *.xls")

Else
Response = MsgBox("Do you want to use the following file " & _
"to save to your C drive & Server3?" & vbCrLf & _
Quote, Buttons:=vbYesNo, Title:="Use Default Filename")

If Response = vbYes Then
fileSaveName = Quote
Else
fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:=Quote, _
Title:="Get Save Filename", _
fileFilter:="Excel Files (*.xls), *.xls")

If fileSaveName = False Then
MsgBox ("Can't get Filename - Exiting macro")
Exit Sub
End If
End If
End If

'get base name of file selected
BaseName = Mid(fileSaveName, InStrRev(fileSaveName, "\") + 1)

Do
ValidResponse = True
Response = InputBox(prompt:= _
"Enter where you want to save the file" & vbCrLf & _
"1) Save to C: Drive" & vbCrLf & _
"2) Save to C: and Server" & vbCrLf & _
"3) Cancel")

Select Case Response
Case "1":
ActiveWorkbook.SaveAs Filename:=fileSaveName
Case "2":
ActiveWorkbook.SaveAs Filename:=fileSaveName
QUOTE1 = Network & BaseName
ActiveWorkbook.SaveAs Filename:=QUOTE1
Case "3":
'do nothing
Case Else
MsgBox ("Bad Response - enter choice again")
ValidResponse = False

End Select
Loop While ValidResponse = False

ActiveWorkbook.Close savechanges:=False

End Sub