Set Cells to A1 Before Save

S

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 !!
 
P

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top