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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

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,118,812
Messages
5,574,456
Members
412,595
Latest member
slim313
Top