Disable ribbon element if workbook doesnt contain a specific worksheet

Hi all,

I think I'm pretty close to figuring this one out, but I still need an experts opinion/advice:

Following xml is used:

Please Login or Register  to view this content.

Following VBA used:

Please Login or Register  to view this content.

Even if the code is ok, it wont work since there won't be any worksheet when the UI loads(only the application window). How should i proceed? Basically i want a specific ribbon button to be disabled when the current workbook doesnt contain a worksheet named "Wkly. Sum."

Thanks

I am personally not convinced that the code is correct.

The following statement results in a runtime error

Please Login or Register  to view this content.

should read

Please Login or Register  to view this content.

The statement below also returns an error

Please Login or Register  to view this content.

and should at least be

Please Login or Register  to view this content.

After making these changes the code will run, however I would not use a variable name of "worksheet" (i.e. too close to a restricted word).

You're absolutely right about the rookie mistakes. But even after those changes, the code still won't work due to no worksheet being open at the very begining(after the application initializes).

This results in a runtime error91 - object variable or with block variable not set @ line

Please Login or Register  to view this content.

Ah,

there is no active sheet yet

replace with

Please Login or Register  to view this content.

Thanks OllieB. But I'm afraid that's still not what I was looking for. The code will run only once and will return false since the "thisWorkbook" redirects to the add-in and there's no wkly. sum. sheet in there. I'd like to make it run on every workbook_change and worksheet_change event. Do you know how I should proceed here? Whether you do or not - thanks once again for your help and time.

Is there a way of determining the workbook the add-in is running in without referring to an active sheet? Would activeworkbook hold the correct pointer? It should be possible to determine the workbook the add-in is running in. I have never worked with add-ins so I am not really familiar with the possibilities.

Based on a google search you should use activeworkbook to refer to the host workbook from inside an add-in. The activeworkbook pointer should be valid at that time

bump (tooshort)

With all respect, have you tried changing the code to the below?

Please Login or Register  to view this content.

i'll give it a shot in a sec. Thanks!

Sadly still won't do the magic.

I've got a class module working now. Can anyone guide me how torun a "xxx_getEnabled" macro after the ribbon has loaded?

Please Login or Register  to view this content.

This disables the ribbon element.

Now this code:

Please Login or Register  to view this content.

My ribbonx book hasnt arrived yet thus I'm clueles(yes, even after googling^^). Thanks

Normally you would invalidate the ribbon in order to fire the callbacks.

do you use the onLoad callback to get object reference to ribbon interface?

Yes I do.

Please Login or Register  to view this content.

I have read that invalidating the ribbon has to be done as well, but until I get there, I still need to pass the true argument to the returnedVal and this I don't know how to do :/

You would need to use a public variable to store the return value and the use that in your callback.

Perhaps we're getting close, but still not quite it:

Please Login or Register  to view this content.

returns a object variable or with block variable not set at the last line...

Hard to say from just snippets of code as to but where is the gxIRibbonUI declared?

that would be in a separate module:

Please Login or Register  to view this content.

i see this is a tough one. Perhaps someone could ignore all the previous snippets of the codes, and advise me how to disable a ribbon element if the open workbook doesn't contain a worksheet named "test"? Is this doable?

the code you have looks like it oughta work as long as you haven't had a state loss that cleared the ribbon variable and assuming you have an onLoad set up in your customui

Thanks JosephP! Here's one last attempt on this before i give up. A summary of what is currently where and everything:

ThisWorkbook of the add-in contains the following:

Please Login or Register  to view this content.

Module1 (standard module) contains the following - I have marked the actual code as comments, and am trying just to get it to work with what is after the comments:

Please Login or Register  to view this content.

mrxIribbonUi module (standard) contains:

Please Login or Register  to view this content.

And when i run init i get the following error :

Run-time error '91': Object variable or With block variable not set

at this line:

Please Login or Register  to view this content.

On top of that i have a class module which is irrelevant i think as i tested it in every possible way and it seems to be working.

The msgbox is being displayed correctly before the error appears.

I found an old topic where Andy Pope has contributed: http://www.mrexcel.com/forum/excel-q...-callback.html
The OP had a very similar problem which he solved by disabling events in workbook open. Doesn't do the trick for me though :/

if someone encounters the same problem, please have a look on the site below. Pretty weird that the ribbonx book doesn't inform about this:

http://www.accessribbon.de/en/?Acces...ns_Dynamically

I find that very hard to believe because it's pretty fundamental

See the attached file. Save as an xlam and then load it.
Create a couple of workbooks and then name a sheet to MyTest. Toggle between workbooks with the tab visible.

class module CAppEvt

Please Login or Register  to view this content.

Thisworkbook code

Please Login or Register  to view this content.

Standard code module

Please Login or Register  to view this content.