Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form
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 "oldjay" wrote: 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" wrote: 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" wrote: 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 :Drive and Server3 3. Cancel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date field in user form & Loading a user form on opening workbook | Excel Programming | |||
Call user form from ThisWorkbook; close file if form closed | Excel Programming | |||
Automatically add a textbox to a user form based on user requireme | Excel Programming | |||
User form ComboBox Items: Remember user entries? | Excel Programming | |||
How to: User Form to assign a user defined range to a macro variab | Excel Programming |