Button on a workshhet?

S

Stuart

User opens a workbook containing a single sheet.
Code has disabled File Save and Save As.

I need to allow the user to Save if they wish!

Is the best way a button... if so how, please?

I need to ensure the saved name is different from
the filename opened, and that they can choose their
save directory.

If they Cancel then the workbook is not to be saved.

Can you help, please?

Regards.
 
T

Tom Ogilvy

Why disable the save and saveas buttons. (did you disable Ctrl+S?) Just
intercept the save in the Workbook_BeforeSave event and handle the naming
there.

(cancel the save and manage it yourself)
 
S

Stuart

I had been trying to do just that, but couldn't get it
to work. My code was:

Dim wkbkname As String
Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim orderno As String, jobno As String

If ActiveWorkbook.Name = wkbkname Then
'user has not changed the filename
'get some example data
With Range("I9")
jobno = .Value
If jobno = "" Then
jobno = "123"
End If
End With

With Range("J9")
orderno = CLng(Mid(.Value, 2, 4))
'what if "J9" value = "" ....error I think CHECK
If orderno = "" Then
orderno = "1001"
End If
End With

MsgBox "You MUST save the file with a NEW name" _
& vbNewLine & vbNewLine & _
"Perhaps something like ..." & vbNewLine & _
" " & _
"E04-" & jobno & "-" & orderno & "-" & "Dickersons.xls"
Exit Sub
End If
End Sub

I can remind the user, but neither force the Save nor permit the
Cancel.
I know I should 'Know' this.

I have only module code in the ThisWorkbook module, no
userforms, buttons etc.

Regards and thanks.
 
T

Tom Ogilvy

Here is a rough approach. (code is untested)

Dim wkbkname As String
Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim orderno As String, jobno As String, fName as String

If ThisWorkbook.Name = "ABCD.xls" Then
'user has not changed the filename
'get some example data
With Range("I9")
jobno = .Value
If jobno = "" Then
jobno = "123"
End If
End With

With Range("J9")
orderno = CLng(Mid(.Value, 2, 4))
'what if "J9" value = "" ....error I think CHECK
If orderno = "" Then
orderno = "1001"
End If
End With
Application.EnableEvents = False
Cancel = True
' put up a message if you want
sStr = "E04-" & jobno & "-" & orderno & "-" & "Dickersons.xls"
fName = Application.GetSaveASFileName(sStr,"Excel Files (*.xls),*.xls)")
if fName <> ThisWorkbook.Name and fName <> "False" then
thisworkbook.SaveAs fName
end if
Application.EnableEvents = True
End If
End Sub
 
S

Stuart

Many thanks for the help
Regards.

Tom Ogilvy said:
Here is a rough approach. (code is untested)

Dim wkbkname As String
Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim orderno As String, jobno As String, fName as String

If ThisWorkbook.Name = "ABCD.xls" Then
'user has not changed the filename
'get some example data
With Range("I9")
jobno = .Value
If jobno = "" Then
jobno = "123"
End If
End With

With Range("J9")
orderno = CLng(Mid(.Value, 2, 4))
'what if "J9" value = "" ....error I think CHECK
If orderno = "" Then
orderno = "1001"
End If
End With
Application.EnableEvents = False
Cancel = True
' put up a message if you want
sStr = "E04-" & jobno & "-" & orderno & "-" & "Dickersons.xls"
fName = Application.GetSaveASFileName(sStr,"Excel Files (*.xls),*.xls)")
if fName <> ThisWorkbook.Name and fName <> "False" then
thisworkbook.SaveAs fName
end if
Application.EnableEvents = True
End If
End Sub
Click to expand...
 
Top