Select 1st cell of specified sheet in before close event

[DK]

Hello!

I need help in creating a before close event. If the workbook has 16
sheets and the users saves it with the cursor in any sheet, a before
close event should be called which will ensure that the cursor is
placed in the first cell of the first sheet.


Can someone please assist me in this?

Thanks! - DK

 

[Dave Peterson]

I wouldn't use the workbook_beforeclose event. If you do, you'll have to make
sure that you save. If you don't save, then the selection change isn't really
done.

If you do save (and the user just wanted to close without saving), then you may
be saving some bad changes!

I'd use the workbook_Open event.

Option explicit
sub workbook_Open()
application.goto worksheets(1).range("a1"), scroll:=true
end sub

 

[DK]

Hello Dave,
Thank you for your reply. I tried to create this but it did not work.
I am a fairly new user. Can you please give me some more steps on how
to create this?

Also, if I have 15 sheets, and on close/open, I want to place the
cursor in the first cell of each sheet and finally make Sheet 1 as
active sheet, can this be incorporated as well?

Thanks!

 

[Dave Peterson]

First, the code goes under the ThisWorkbook module -- not in a General module
and not behind a worksheet.

Option Explicit
Sub Workbook_Open()
Dim wks As Worksheet
Application.ScreenUpdating = False
For Each wks In Me.Worksheets
Application.Goto wks.Range("a1"), scroll:=True
Next wks
Me.Worksheets(1).Select
Application.ScreenUpdating = True
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Alan]

Open the workbook. Press Alt/F11 at the same time.
You will see your workbook on the left hand side it will look something like
this:VBAProject(Your Workbook).
Click the + sign beside your VBAProject(Your Workbook).
Keep clicking + signs until you see a "ThisWorkbook" icon.
Right click on the "ThisWorkbook" icon and select "View Code".
Paste the code that Dave gave you into the blank window on the right.
Click the save icon on the Visual Basic Editor toolbar or close the editor
and save the workbook before closing.

The next time the workbook opens, it will always move the activecell to
sheet1, A1.

Regards,

Alan

Hello Dave,
Thank you for your reply. I tried to create this but it did not work.
I am a fairly new user. Can you please give me some more steps on how
to create this?

Also, if I have 15 sheets, and on close/open, I want to place the
cursor in the first cell of each sheet and finally make Sheet 1 as
active sheet, can this be incorporated as well?

Thanks!

 

[DK]

Dave,
Thank you so much for your help! This works wonderfully. I will go
through the referred website as well. I need to learn this stuff.

Alan, Thank you for helping out!

Have a great day!
DK