Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select 1st cell of specified sheet in before close event
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select 1st cell of specified sheet in before close event
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select 1st cell of specified sheet in before close event
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! On Feb 28, 6:16 pm, Dave Peterson wrote: 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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select 1st cell of specified sheet in before close event
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 DK wrote: 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! On Feb 28, 6:16 pm, Dave Peterson wrote: 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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select 1st cell of specified sheet in before close event
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 "DK" wrote in message oups.com... 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! On Feb 28, 6:16 pm, Dave Peterson wrote: 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 wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select 1st cell of specified sheet in before close event
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 On Feb 28, 7:49 pm, "Alan" wrote: 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 "DK" wrote in message oups.com... 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! On Feb 28, 6:16 pm, Dave Peterson wrote: 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 wrote: 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- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
in vba what command is used to determine if a particular cell on a particular sheet changed? some kind of event? how to get the old and new value of the cell? | Excel Worksheet Functions | |||
Worksheet close event++? | Excel Programming | |||
Don't save before close event? | Excel Programming | |||
Assign values to a cell in sheet A, from worksheet_change event of sheet B | Excel Programming | |||
After Close Event? | Excel Programming |