Fahrvergnugen
New Member
- Joined
- Mar 21, 2005
- Messages
- 33
I am currently working on a spreadsheet that I have diabled the Save, Save As, and Ctrl+S funtionality using the following code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'disables Save, Save As, and Ctrl+S funtionality so users must use
'Save button on the spreadsheet
Cancel = True
MsgBox "The 'Save' function has been disabled." & Chr(10) & "You must use the 'Save' button on the spreadsheet", _
vbCritical + vbOKOnly, "Save As Disabled"
End Sub
I have coded a separate Sub that I want the users to use in order to save the file in the appropriate network drive:
Sub SaveAs()
'Saves the workbook to the appropriate directory on the network with a
'file name using the users name and period end date
Dim txtPeriodEnd As Variant
Dim txtEmployeeName As Variant
txtPeriodEnd = Range("N2") 'Gets the period end date
txtEmployeeName = Range("I1") 'Gets the users name
'Check to ensure that the user has selected their name
If txtEmployeeName = "" Then
MsgBox "You must enter your name", vbOKOnly + vbCritical, "Enter Your Name"
Exit Sub
End If
Application.ThisWorkbook.SaveAs ("\\Users\Accounting\Time Sheets\" & txtEmployeeName & "\" & txtEmployeeName & " " & txtPeriodEnd & ".xls")
End Sub
I have this sub attached to a command button, but the problem is when the user selects the command buttone the save functionality is disabled.
Any ideas as to how to enable the Save functionality for the duration of my sub and then be disabled again?
Any help is appreciated!
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'disables Save, Save As, and Ctrl+S funtionality so users must use
'Save button on the spreadsheet
Cancel = True
MsgBox "The 'Save' function has been disabled." & Chr(10) & "You must use the 'Save' button on the spreadsheet", _
vbCritical + vbOKOnly, "Save As Disabled"
End Sub
I have coded a separate Sub that I want the users to use in order to save the file in the appropriate network drive:
Sub SaveAs()
'Saves the workbook to the appropriate directory on the network with a
'file name using the users name and period end date
Dim txtPeriodEnd As Variant
Dim txtEmployeeName As Variant
txtPeriodEnd = Range("N2") 'Gets the period end date
txtEmployeeName = Range("I1") 'Gets the users name
'Check to ensure that the user has selected their name
If txtEmployeeName = "" Then
MsgBox "You must enter your name", vbOKOnly + vbCritical, "Enter Your Name"
Exit Sub
End If
Application.ThisWorkbook.SaveAs ("\\Users\Accounting\Time Sheets\" & txtEmployeeName & "\" & txtEmployeeName & " " & txtPeriodEnd & ".xls")
End Sub
I have this sub attached to a command button, but the problem is when the user selects the command buttone the save functionality is disabled.
Any ideas as to how to enable the Save functionality for the duration of my sub and then be disabled again?
Any help is appreciated!