automation of sub
 

I posted this question on the General Board, but I was unable to obtain a
workable answer, can anyone on this board help?

Thanks,

Jim

original post
"Jim" wrote:

We have a workbook with multiple worksheets. I have written a procedure
which checks for data outside of a specified range on the worksheet.

Is it possible to have this procedure run automatically whenever a user
activates a new worksheet in the workbook? I realize that the procedure can
be tied to a key combination, or command button, but that requires input from
the user. For this application, we need to automate the process.

Thanks,

Jim

Response Provided:

From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the
left treeview search for the workbook name and click on + to expand it.
Within that you should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and check out the drop downs for the below event

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'call your procedure here
End Sub


Result:

Thank you for your reply, however this event is not shown in the drop down
box. The events listed begin with AcceptLabelsInFormulas. Is it possible
that I
may be looking in the incorrect drop down box?

Jim

automation of sub
 

Jim

There are 2 events you could use.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

or

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

Mike



"Jim" wrote:

I posted this question on the General Board, but I was unable to obtain a
workable answer, can anyone on this board help?

Thanks,

Jim

original post
"Jim" wrote:

We have a workbook with multiple worksheets. I have written a procedure
which checks for data outside of a specified range on the worksheet.

Is it possible to have this procedure run automatically whenever a user
activates a new worksheet in the workbook? I realize that the procedure can
be tied to a key combination, or command button, but that requires input from
the user. For this application, we need to automate the process.

Thanks,

Jim

Response Provided:

From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the
left treeview search for the workbook name and click on + to expand it.
Within that you should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and check out the drop downs for the below event

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'call your procedure here
End Sub


Result:

Thank you for your reply, however this event is not shown in the drop down
box. The events listed begin with AcceptLabelsInFormulas. Is it possible
that I
may be looking in the incorrect drop down box?

Jim

automation of sub
 

Please don't multi-post the same question. I replied to your other post a few
minutes ago.

Hutch

"Jim" wrote:

I posted this question on the General Board, but I was unable to obtain a
workable answer, can anyone on this board help?

Thanks,

Jim

original post
"Jim" wrote:

We have a workbook with multiple worksheets. I have written a procedure
which checks for data outside of a specified range on the worksheet.

Is it possible to have this procedure run automatically whenever a user
activates a new worksheet in the workbook? I realize that the procedure can
be tied to a key combination, or command button, but that requires input from
the user. For this application, we need to automate the process.

Thanks,

Jim

Response Provided:

From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the
left treeview search for the workbook name and click on + to expand it.
Within that you should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and check out the drop downs for the below event

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'call your procedure here
End Sub


Result:

Thank you for your reply, however this event is not shown in the drop down
box. The events listed begin with AcceptLabelsInFormulas. Is it possible
that I
may be looking in the incorrect drop down box?

Jim