Solved: Save Options

[Andybuck86]

Hi,

I currently have a template spreadsheet that is accessed by several pc's across my business. Some access the spreadsheet locally (via a mapped NAS drive) and some access it through a VPN.

I have two buttons on my spreadsheet that will save the spreadsheet based on filename in cell G1. One button will use the folder path in cell G2 and the other will use cell G4. The reason I have two buttons is because the folder paths are different depending on whether the spreadsheet is access locally or via the VPN.

i.e.
Cell G2 contains the VPN folder location - \\5.51.214.9\Network\Document
Cell G4 contains the Local folder location - \\ftws1\Network\Document

The above is just an example. The folder paths vary with data entry

I would like to be able to use one button...that would check the connection before saving. Eg. if folder location in cell G2 does not work then use G4.

My code is as follows:

[vba]Option Explicit
Sub Save_Password()
Application.DisplayAlerts = False
Dim Open_Password As String
Dim New_File_Name As String
New_File_Name = Range("G2").Value & "\" & Range("G1").Value & ".xlsm"
Open_Password = InputBox("Please enter a password")
ActiveWorkbook.SaveAs New_File_Name, _
WriteResPassword:=Open_Password, _
Password:="test"

' ActiveWorkbook.SaveAs New_File_Name, _ ' creates a new file with the name in G1
' Password:=Open_Password, _ ' this is the password the user enters
' WriteResPassword:="password" ' this is the master password

End Sub[/vba]

The above is for the VPN connection. The button for the local connection is similar - this is the only change:

[vba]New_File_Name = Range("G4").Value & "\" & Range("G1").Value & ".xlsm"[/vba]


Any help would be greatly appreciated


Andy

[Bob Phillips]

Untested

[vba]

Open_Password = InputBox("Please enter a password")
On Error Resume Next
New_File_Name = Range("G2").Value & "\" & Range("G1").Value & ".xlsm"
ActiveWorkbook.SaveAs New_File_Name, _
WriteResPassword:=Open_Password, _
Password:="test"
If Err.Number <> 0 Then
Err.Clear
New_File_Name = Range("G4").Value & "\" & Range("G1").Value & ".xlsm"
ActiveWorkbook.SaveAs New_File_Name, _
WriteResPassword:=Open_Password, _
Password:="test"
End If
On Error Goto 0[/vba]

[Andybuck86]

Untested

[vba]

Open_Password = InputBox("Please enter a password")
On Error Resume Next
New_File_Name = Range("G2").Value & "\" & Range("G1").Value & ".xlsm"
ActiveWorkbook.SaveAs New_File_Name, _
WriteResPassword:=Open_Password, _
Password:="test"
If Err.Number <> 0 Then
Err.Clear
New_File_Name = Range("G4").Value & "\" & Range("G1").Value & ".xlsm"
ActiveWorkbook.SaveAs New_File_Name, _
WriteResPassword:=Open_Password, _
Password:="test"
End If
On Error Goto 0[/vba]

Sorry for the late response - been a little busy.

Thanks so much for your help - that worked perfectly!