Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Triggering procedures
I'm quite new to vb for excel, and fairly new to vb. I've written some
subroutines in a workbook that contains multiple worksheets. I'm having quite a time trying to figure out how to execute my code when the workbook opens, when the sheet is activated, and when certain cells change in a sheet. I know how to test for cell changes so that's not a problem - the code is located behind the worksheet and executes fine. However, I cannot get the code to excute for the other 2 conditions. I tried putting copies of the code in a module, and in 'this workbook', and also using worksheet_activate, but cannot seem to get it to run consistently when it needs to. Any help would be greatly appreciated. Thank you Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Triggering procedures
Hi Robert,
When you open the VBA editor you have the project explorer on the left. (If not displayed, then Select View - Project Explorer or Ctrl/R) If you select one of the Sheets, then at the top of the large white area where the code goes, you have 2 dropdown boxes. The left one defaults to (General) and the right to (Declarations). Click the dropdown against the left one and select Worksheet. You will automatically be presented with a Private Sub/end sub. Click the drop down on the right and you will see a number of events relating to worksheet. Select the one you want such as Activate the the Private sub/end sub for that is displayed. Enter the required code between the private sub and end sub. NOTE: You cannot change the sub names that are automatically created for these events. However, if any unwanted private sub/end sub is created then you can delete it. Similar for Thisworkbook except the left dropdown will show Thisworkbook then select the required event from the right side. When you understand the above I think that you will be able to proceed from there but one other thing to know is that the parameters in brackets in some procedures can be used in your code. Example in ThisWorkbook Private Sub Workbook_SheetActivate(ByVal Sh As Object) Sh refers to the worksheet. The following code will return its name MsgBox Sh.Name -- Regards, OssieMac "Robert" wrote: I'm quite new to vb for excel, and fairly new to vb. I've written some subroutines in a workbook that contains multiple worksheets. I'm having quite a time trying to figure out how to execute my code when the workbook opens, when the sheet is activated, and when certain cells change in a sheet. I know how to test for cell changes so that's not a problem - the code is located behind the worksheet and executes fine. However, I cannot get the code to excute for the other 2 conditions. I tried putting copies of the code in a module, and in 'this workbook', and also using worksheet_activate, but cannot seem to get it to run consistently when it needs to. Any help would be greatly appreciated. Thank you Robert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Triggering procedures
Hi Robert
In ThisWorkbook module (and each sheet module), at the top you have two drop down menus. In the left menu choose Workbook/Worksheet, in the right menu you can see all availiable event options. In ThisWorkbook you want the Workbook_Open event. Worksheet_Activate should be what you need, when you want to run some code when the sheet is activated. For further help, post you current code, with a description of what and when you want to happen Regards, Per "Robert" skrev i meddelelsen ... I'm quite new to vb for excel, and fairly new to vb. I've written some subroutines in a workbook that contains multiple worksheets. I'm having quite a time trying to figure out how to execute my code when the workbook opens, when the sheet is activated, and when certain cells change in a sheet. I know how to test for cell changes so that's not a problem - the code is located behind the worksheet and executes fine. However, I cannot get the code to excute for the other 2 conditions. I tried putting copies of the code in a module, and in 'this workbook', and also using worksheet_activate, but cannot seem to get it to run consistently when it needs to. Any help would be greatly appreciated. Thank you Robert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Triggering procedures
Thank you very much for your replies. Using the information you supplied I
had the code executing each time it needed to within minutes. Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
my VBA procedures stopped calling other procedures in excel 2007. | Excel Programming | |||
Events not triggering | Excel Programming | |||
Triggering UDF | Excel Programming | |||
Triggering Macros | Excel Programming | |||
triggering macros | Excel Programming |