A save question:

[Phil Hageman]

Is there a way to attach a macro to the Excel save button
and have it work exclusively for an uniquely identified
workbook, but operate normally otherwise.

Or is the answer to put a save button (control) on each
worksheet?

Thanks,
Phil

 

[Jan Karel Pieterse]

Hi,

For that specific workbook, use its Before_save event in
the thisworkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel As Boolean)
if msgbox("Save ?",vbYesNo)=vbNo then
Msgbox "I'm not saving myself!!"
Cancel=True
Else
msgbox "Saving!!"
end if
End Sub

Regards,

Jan Karel Pieterse
Excel TA/MVP

 

[Phil Hageman]

Jan Karel, Thanks for your answer. Could we take this a
step further...

My workbook is named Scorecard, and its worksheets are
entitled Scorecard, Customer, Finance, and Employee.

When the user clicks the Save icon, I want the cursor in
each worksheet to be placed in Cell A1, and the worksheet
named Scorecard to be the the last worksheet changed.

The object is that when a user opens the Workbook, the
worksheet named Scorecard comes up first, and as each of
the worksheets is opened, it shows the top of the
worksheet - thus the force of cursor to cell A1.

What would the code be, and just where would I put it?

Phil

 

[steve]

Phil,

Here is a more convoluted macro that works. (it cycles
through a large workbook quickly)

Note that just selecting A1 does not guarantee cell A1 will
be at the top of the screen - that is the reason for the ScrollRow
& ScrollColumn lines.

be fun...

steve

==============================================
Sub SaverWB()
' Save workbook - No Alerts
' Select A1 on each sheet
' Select Sheet(1)

Application.ScreenUpdating = False
Application.CutCopyMode = False

'''Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
For x = 1 To ActiveWorkbook.Worksheets.COUNT
Sheets(x).Select
Range("a1").Select

ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1

Next
On Error GoTo 0

If IsError(Sheets(1).Select) Then
Sheets(2).Select
Else
Sheets(1).Select
End If

Cells(1, 1).Select

Application.EnableEvents = True
Application.ScreenUpdating = True

Sheets("Scorecard").Select

ActiveWorkbook.Close True

Application.DisplayAlerts = True
End Sub
=========================================

 

[John Wilson]

Didn't realize that this thread preceded the "other" thread that I
mentioned in my last post.
Please disregard my last post.

John