setting OnAction macro names for form controls from a VBA macro

[jon]

I need to be able to modify the names of the OnAction macros for the
controls on a form that has already been created.

I can find the form via

ThisWorkbook.VBProject.VBComponents(f)

and I can find the component via

ThisWorkbook.VBProject.VBComponents(f).designer.Controls(c)

What I can't figure out is how to set the OnAction (event procedure) for the
control. Any hints?



Jon

jbondy at sover dot net

 

[Bob Phillips]

Jon,

Do you mean userforms? They don't have an OnACtion property, they have
events, such as the click event, associated with them.

What are you trying to do?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Unless you are using a MAC, then the controls on a Userform don't have an
onaction property. They use events.

You might look at

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

at Chip Pearson's site.

If you are using a MAC, then disregard.

 

[jon]

Bob:

Thanks for your response.

In my conceptual framework, there is little distinction between an OnAction
property and a Click Event: in Windows and in Delphi, an event proc is an
event proc, but I guess VBA is more ... complex than the underlying
structures. But I'm sure you're correct.

I want to assign one of the procedures in my code to the Click Event. Is
there any way to do this?



Jon

----- Original Message -----
From: "Bob Phillips" <[email protected]>
Newsgroups: microsoft.public.excel.programming
Sent: Monday, March 08, 2004 12:59 PM
Subject: Re: setting OnAction macro names for form controls from a VBA macro

 

[Bob Phillips]

Jon,

All controls on a userform have a certain events associated with them. So
for instance, if you have a commandbutton called cmdOK, you can code to a
cmdOK_Click event that will fire when the button is clicked. So you can
build the code when you create the control. So, when you add a control to a
userform, double le-click it and it opens the default event, but you can
access any of them from the dropdowns.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[jon]

Bob:

Thanks for the response.

My problem is this (bear with me: this is a bit complicated).

I'm writing an AddIn (AI) in Excel 97, because if I use 2000 or 2002, we
disenfranchise many of our users. I can't "sign" the AI from 97, so I write
it in 97 and then load it into 2000 to be signed and converted from an XLS
to an XLA (I have to develop as an XLS because I'm embedding sheets in the
AI, and once it is converted from an XLS to an XLA, I lose control over the
sheets).

Every once in a while, during the manual conversion from the 97 XLS to the
2000 XLA, I forget, and do a Save instead of a Save As. The result is an
XLS file that can no longer be accessed from 97. I then have to go into the
XLS using 2000 and export all of the forms and code modules. I then create
a new WB containing all of the old sheets, switch from 2000 to 97, and
import the forms and code modules. A huge pain in the neck, and I've
already had to do it twice. Along the way, I rename the WB from, say, fred1
to fred2, so that I don't accidentally write on top of the only file that I
still have that contains my precious code.

The problem occurs later on, when I'm trying to use the newly resurrected WB
in 97. Often, the OnAction event procedure names contain the complete path
and file name of the OLD file ("fred1"), even though the new WB file name is
"fred2". When the events occur, they attempt to open the old WB (executing
old code). Terribly confusing. I spent many hours trying to figure out why
changes in code were not being executed. Sigh.

I managed to write a procedure that "fixes" all of the OnAction file names,
but I cannot figure out how to fix the Action file names (which are also
incorrect). I can't figure out how to access them at all.

So. Huge wastes of time, lots of code getting written to work around MS
bugs. Sigh.

So. Any idea how I can fix the event file names programmatically? Last
time I had to re-enter all of them manually, which was another entire waste
of time.

Thanks for listening.

Jon