Excel vba function asking for object required error 424

[bilalmussa]

Hi,

I have created the following function in excel

Please Login or Register  to view this content.

I then use the following to call it when the toggle button is clicked

Please Login or Register  to view this content.

However, I get the 424 error saying that an object is required.

What is the reason behind this ?

Thanks

Bilal

[jaslake]

Hi Bilal

Welcome to the Forum!!

You don't tell us what line of Code is blowing out. Based on the posted Code I'd suggest these changes:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

[bilalmussa]

Hi,

This is the original sub I wrote for each toggle button and now I want to turn it into a function to make my code cleaner

Please Login or Register  to view this content.

The above works fine with no issues.

Could you advise me on how best to turn this into a function ? The original thread heading was my attempt at writing a function.

Bilal

[Norie]

What are ra_main_seasonals and tgbtnSeasonal?

[Kaper]

First: have you changed

Please Login or Register  to view this content.

into

Please Login or Register  to view this content.

As c2 is declared as Integer (it would be wiser to declare it as long) it cannot be Set. You can Set only object variables.

so:

Please Login or Register  to view this content.

if it does not help, press Debug when code stops, chech which code line is highlited in yellow and let us know.

[bilalmussa]

Hi,

rg_main_seasonals and tgbtnSeasonal are named ranges and toggle button respectively.

I want to turn that sub routine into a function so that I can reuse it for the other toggle buttons on the page by just passing through the named range and toggle button name.

Is that possible ?

Bilal

[bilalmussa]

First: have you changed

Please Login or Register  to view this content.

into

Please Login or Register  to view this content.

As c2 is declared as Integer (it would be wiser to declare it as long) it cannot be Set. You can Set only object variables.

so:

Please Login or Register  to view this content.

if it does not help, press Debug when code stops, chech which code line is highlited in yellow and let us know.

I have made this change. However, I want to turn my sub routine into a function so that I can then reuse it for the other toggle buttons by just passing through the named range and toggle button name.

However, when I try to call it, it says object required.

Any suggestions ?

Bilal

[Kaper]

OK. We know what error message you are getting.
But ... Where the code execution stops?

PS. is rg_main_seasonals the name of the range or range object
I mean: (Ctrl+F3) ... rg_main_seasonals
or

Please Login or Register  to view this content.

if it is the first case (just named range) then you shall call

Please Login or Register  to view this content.

Edit: I noticed after posting (I used parts of your previous messages copied into this answer):
Isn't it just a problem with missspelling:
ra_main_seasonals
vs.
rg_main_seasonals

(the cure for such problems is using Option Explicit to force variable declarations)

[bilalmussa]

Hi,

I made the following change of adding range into the call function and proceeds. Now the code stops here

Please Login or Register  to view this content.

Why is that ? do I not need to write sht.range ?

Bilal

[bilalmussa]

The error I now get is Run time 1004

"Method, 'range' of object '_worksheet' failed"

Why could that be ?

Bilal

[Norie]

Can you post the exact code you have now, and an example of how you are calling it?

[bilalmussa]

Fixed,

I was calling the range into the module which was playing up.

It is

Please Login or Register  to view this content.

Thank you very much all !!!

[mikerickson]

You might use code like this

Please Login or Register  to view this content.

Its not clear to me what the rng variable is used for. Perhaps changing the With line to

Please Login or Register  to view this content.

would be what you want. Then the range rg_main_tablets would have to be passed to the function as an argument.

Also, its not clear what value you want returned by the function.

(BTW, row visibility cannot be controlled by a UDF called from a worksheet formula, neither can the caption of a toggle button).