Need help using a cell for Save and SaveAs Manipulation

Mgreencornerstone

Board Regular
Joined
Nov 23, 2005
Messages
116
I have a contract that has electronic signature capture importing into a cell, and I need to save this document for read only except for an administrator who can change the contract. I would like to know if there is a way that a worksheet can be saved using two cells (A7 and I7, I7 is a date) as file name and save it in a certain folder location. I would also like to disable the “save” and “save as” so the user cannot change the file name or save location. The document also needs to be locked for anyone else to change after the save so only the administrator can change it.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fausto

New Member
Joined
Jul 28, 2004
Messages
44
Hello Mgreencornerstone

Please paste this into the ThisWorkbook section in your VBA Editor (Alt+F11)

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False
Cancel = True
Dim saveas As String
Dim allowed As String
allowed = Sheets("Sheet3").Range("A7").Value & Sheets("Sheet3").Range("I7").Value
If SaveAsUI = True Then
    saveas = Application.GetSaveAsFilename(InitialFileName:=allowed, filefilter:="Microsoft Excel Workbook (*.xls), *.xls")
    If saveas = "False" Then
        Application.EnableEvents = True
        Exit Sub
    End If
Else
    saveas = "Normal"
End If
If saveas = "Normal" Then
    If ThisWorkbook.Name <> allowed & ".xls" Then
        MsgBox "Illegal filename!!!" & vbCrLf & "The filename must be " & allowed & ".xls", vbCritical, "Error"
        Application.EnableEvents = True
        Exit Sub
    Else
        Me.Save
    End If
Else
    If saveas <> ThisWorkbook.Path & "\" & allowed & ".xls" Then 'EDIT THE PATH REQUIRED
        MsgBox "Illegal filename!!!" & vbCrLf & "The filename must be " & allowed & ".xls", vbCritical, "Error"
        Application.EnableEvents = True
        Exit Sub
    Else
         Me.saveas saveas
    End If
End If
Me.Saved = True
Cancel = False
Application.EnableEvents = True
End Sub

I hope this is what you are looking for. If not please post again
 

Watch MrExcel Video

Forum statistics

Threads
1,123,355
Messages
5,601,142
Members
414,431
Latest member
JustmemyselfandI

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top