Triggering procedures

[Robert]

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

 

[OssieMac]

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

 

[Per Jessen]

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]

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