VBA Auto_Activate

[PaulD]

I've been having a real (tho stupid) problem w getting Auto_Activate to work
with sheets in my Workbook.
I create a name within an active sheet (say Sheet1) called "Auto_Activate".
I refer the name to a procedure (say OnActivate) that I have written within
Sheet1.

I get the error MsgBox "Can't find OnActivate".

So I've additionally tried the following permutations:
- Put OnActivate in Module1, ThisWorkbook (in addition to Sheet1).
(and verified its existence in all three using object browser)
- Changed the syntax in "Refers to" box to try e.g.
Sheet1.OnActivate
Sheet1!OnActivate
- Renamed Sheet1 to "FirstSheet" and repeated above using
e.g. FirstSheet!OnActivate

I've tried to methodically try every permutation I can think of and no luck.
After trying for > 5 hours, I'm giving up and asking for help.

Doesn't what I've described seem to be the right way to go?
[I knew I only had one brain cell left, but, till now, it seemed to be working
just fine.....]

 

[Jim Cone]

I am not sure what you are trying to do.
It appears you are trying to call a Sub routine from a worksheet cell
using a name that refers to the procedure's name.
That won't work, but that shouldn't be news to you.
However, you can call a function with a formula.

Functions return a value and that value can be shown in a worksheet cell.
If you were to have a function procedure in a standard module like...

Function GetNumber(ByRef strColumn As String) As Long
GetNumber = Columns(strColumn).Column
End Function
'-----------------
Then if you enter this formula in worksheet cell...
=getnumber("AB")
You would have the column number of Column("AB") displayed in the
cell as... 28

If you want to call a sub routine from the worksheet, one way would be
to add a button from the Forms toolbar and "attach" the sub's name
to the button.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"PaulD"
<[email protected]>
wrote in message
I've been having a real (tho stupid) problem w getting Auto_Activate to work
with sheets in my Workbook.
I create a name within an active sheet (say Sheet1) called "Auto_Activate".
I refer the name to a procedure (say OnActivate) that I have written within
Sheet1.
I get the error MsgBox "Can't find OnActivate".
So I've additionally tried the following permutations:
- Put OnActivate in Module1, ThisWorkbook (in addition to Sheet1).
(and verified its existence in all three using object browser)
- Changed the syntax in "Refers to" box to try e.g.
Sheet1.OnActivate
Sheet1!OnActivate
- Renamed Sheet1 to "FirstSheet" and repeated above using
e.g. FirstSheet!OnActivate
I've tried to methodically try every permutation I can think of and no luck.
After trying for > 5 hours, I'm giving up and asking for help.
Doesn't what I've described seem to be the right way to go?
[I knew I only had one brain cell left, but, till now, it seemed to be working
just fine.....]

 

[Norman Jones]

Hi Paul,

Perhaps what you seek is the Worksheet_SelectionChange event or, possibly,
the Worksheet_Activate event.

For more information on these and other event procedures, see Chip Pearson's
overview at:

http://www.cpearson.com/excel/events.htm

 

[PaulD]

Norman,
Thanks for taking the time to help out. I've been using a book by Jeff Webb
and,
after reading your post I took a look at it's publication date. 1996! Yikes!
In there he listed 4 "auto" procedures.

Autpen
Auto_Close (for Workbooks)
and Auto_Activate
Auto_Deactivate (for Worksheets).

The article you pointed out is the way I will go. (Haven't done it yet but
I'm sure its the right thing). BYW Autpen and Auto_Close still seem to
work w/ Excel 2003.

Jim-- Thanks to you also. If you look a Norms posting, you'll see that I
wasn't trying
to associate a procedure w/ a range or cell but with the action of
activating an object such as a Worksheet.

P.S. I'm awaiting delivery of 3 or 4 programming guides from Amazon---on
Excel 2003! No more foolishness I hope!