Newbie question - code attached to spreadsheet vs code in module

  • Thread starter The poster formerly known as Colleyville Alan
  • Start date
T

The poster formerly known as Colleyville Alan

It has been a few years since I wrote much code for Excel, so I am at a loss
with something that, IIRC, is fairly simple.

I added some radio button ActiveX components to my spreadsheet, and
double-clicked on them to open the editor and put some simple code there. I
noticed later on that when I opened the VBA editor, there was no module for
the code. I looked around and saw that Excel had the code in the
spreadsheet object.

So I added a module and cut/pasted the code to that module. Now the radio
buttons do not work - they are not looking in the module for the code.

Question: how can I get those objects to look in the module for the code
rather than the spreadsheet object?
Thanks

Alan
 
D

DomThePom

If your button is located on a worksheet the its click event code will appear
on that sheets module. If you want to place the code in a seperate module
then create a sub in a module and run it from the buttons click event
procedure
 
T

The poster formerly known as Colleyville Alan

DomThePom said:
If your button is located on a worksheet the its click event code will
appear
on that sheets module.

Yep, that is where I started from.
If you want to place the code in a seperate module
then create a sub in a module and run it from the buttons click event
procedure

That is what I am trying to do. I created a new module and then I moved all
of the code to it by cut and paste. Now I want to click on a button and
have that code run, but nothing happens. The code is only recognized on
that sheet's module and I want the controls on that sheet to reference a
separate module so that I can have similar controls on multiple worksheets
all referencing the same module.

I have played around with it a bit since my post and it seems that if I use
a "Form" control, then the control references a separate module, but if I
use an ActiveX control, then the onclick procedure goes directly to that
sheet's module. Is that just by chance or do those two different types of
controls reference code in modules differently?
 
P

paul.robinson

Hi
The one for ActiveX controls is running event code, which has a
particular syntax (on_click, doubleclick etc) and goes in its own
module. The form code simply calls a macro, not an event associated
with the button.
What the other poster is telling you to do is leave the skeleton of
the event code where it is, but call the body of it from a normal code
module

e.g. event code in sheet module
Private sub OnEventName(some parameters possibly)
Call BodyCode Same parameters possibly
End sub

In the normal module

Public sub BodyCode (Same parameters possibly)
'code that was in event code
End Sub


regards
Paul
 
T

The poster formerly known as Colleyville Alan

Thanks

Hi
The one for ActiveX controls is running event code, which has a
particular syntax (on_click, doubleclick etc) and goes in its own
module. The form code simply calls a macro, not an event associated
with the button.
What the other poster is telling you to do is leave the skeleton of
the event code where it is, but call the body of it from a normal code
module

e.g. event code in sheet module
Private sub OnEventName(some parameters possibly)
Call BodyCode Same parameters possibly
End sub

In the normal module

Public sub BodyCode (Same parameters possibly)
'code that was in event code
End Sub


regards
Paul
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top