PDA

View Full Version : Solved: BeforeSave Event



zoom38
05-01-2006, 07:56 AM
Im trying to have vba code automatically change the active cell to cell a1 on all of the worksheets in the workbook beforesave event. This is so when the file is opened the next time all of the sheets will be set to cell a1. The following code is what I came up with but it only works on the activesheet.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wkSheet As Worksheet

For Each wkSheet In Worksheets
Range("a1").Select
Next wkSheet
End Sub


Any suggestions?
Thanks
Gary

Jacob Hilderbrand
05-01-2006, 08:18 AM
Each worksheet needs to be active when you want to select a range. i.e.


Thisworkbook.Activate
For Each wkSheet In Worksheets
wkSheet.Activate
Range("A1").Select
Next

lenze
05-01-2006, 08:23 AM
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sh As Worksheet
For Each sh In Worksheets
sh.Activate
Range("a1").Select
Next sh
Sheet1.Select
End Sub

This seems to work

lenze

zoom38
05-01-2006, 08:30 AM
Thanks DRJ you fixed my code and solved it.
Gary

Jacob Hilderbrand
05-01-2006, 12:21 PM
You're Welcome :beerchug:

Take Care

zoom38
05-02-2006, 05:15 AM
Thanks for the replies. I came across another issue with this. No matter what sheet you are on, when you hit save it brings you to the last sheet. If you are on the 5th sheet of 18, how do you have it automatically bring you back to the 5th sheet when you hit save since it no longer is the active sheet?

Bob Phillips
05-02-2006, 05:30 AM
Untested, but should work



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim thisSheet As Worksheet
Dim sh As Worksheet
Set thisSheet = ActiveSheet
For Each sh In Worksheets
sh.Activate
Range("a1").Select
Next sh
thisSheet.Activate
End Sub

zoom38
05-02-2006, 06:33 AM
Thanks xld that does work without a hitch.
Gary