Set Cells to A1 Before Save

[Steve]

I want to have the option to set all cells to A1 before a save (or not set
them before a save).

Here is what I've put together so far from examples.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
a = MsgBox("Do you want to set all sheets to A1?", vbYesNo)
If a = vbNo Then

(I need a command here to save the workbook and skip the call to SetA1)

SetA1
End Sub


Sub SetA1()
Application.ScreenUpdating = False
For Each ws In Sheets
ws.Activate: ws.Range("A1").Activate
Next
Sheets(1).Activate
Application.ScreenUpdating = True
End Sub


I suspect, however that someone will suggest a better way to do what I want.
I'm all for that !!

 

[Per Jessen]

Hi Steve

If user click Yes then run SetA1, else do nothing. Also I would set the
reference to ActiveWorkbook in the 'For Each ws...' statement

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
a = MsgBox("Do you want to set all sheets to A1?", vbYesNo)
If a = vbYes Then
SetA1
End If
End Sub

Sub SetA1()
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Sheets
ws.Activate
ws.Range("A1").Select
Next
Sheets(1).Activate
Application.ScreenUpdating = True
End Sub

Regards,
Per

 

[Jacob Skaria]

Try the below..

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
If MsgBox("Do you want to set all sheets to A1?", vbYesNo) = vbYes Then
SetA1
End If
End Sub

If this post helps click Yes