Newbie question - code attached to spreadsheet vs code in module

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

The poster formerly known as Colleyville Alan - 14 Apr 2008 02:31 GMT

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

Reply to this Message

DomThePom - 14 Apr 2008 08:58 GMT

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

"The poster formerly known as Colleyville" wrote:

> 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.
[quoted text clipped - 13 lines]
>
> Alan

Reply to this Message

The poster formerly known as Colleyville Alan - 14 Apr 2008 14:06 GMT

> 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?

> "The poster formerly known as Colleyville" wrote:
>
[quoted text clipped - 19 lines]
>>
>> Alan

Reply to this Message

paul.robinson@it-tallaght.ie - 14 Apr 2008 15:35 GMT

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

On Apr 14, 2:06 pm, "The poster formerly known as Colleyville Alan"
<nos...@nospam.net> wrote:

> > If your button is located on a worksheet the its click event code will
> > appear
[quoted text clipped - 44 lines]
>
> - Show quoted text -

Reply to this Message

The poster formerly known as Colleyville Alan - 15 Apr 2008 01:10 GMT

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

On Apr 14, 2:06 pm, "The poster formerly known as Colleyville Alan"
<nos...@nospam.net> wrote:

> "DomThePom" <DomThe...@discussions.microsoft.com> wrote in message
>
[quoted text clipped - 52 lines]
>
> - Show quoted text -

Reply to this Message