Excel VBA - Disable Command button in sub?

[kickme93]

Hi, In my project I have a command bar which has 3 buttons. Enable Reminder, Time Reminder, Disable Reminder
I have the Enable Reminder and Disable reminder buttons working and am currently working on getting time reminder working, my problem is I want this button to disable itself after the user has pressed it - how would I go about doing this?

Currently this is the code I have for this command button.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

As you can see I have tried to disable this button - However I'm not to sure if this is the right approach?

[john55]

one way is to add at the end of your code

Please Login or Register  to view this content.

and set it to "true" when the workbook is open

[kickme93]

one way is to add at the end of your code

Please Login or Register  to view this content.

and set it to "true" when the workbook is open

Please Login or Register  to view this content.

Its giving me a run time error 424?

[millz]

Try this maybe, just a guess.

Please Login or Register  to view this content.

[Izandol]

You may use simple:

Please Login or Register  to view this content.

[kickme93]

Try this maybe, just a guess.

Please Login or Register  to view this content.

You may use simple:

Please Login or Register  to view this content.

Both give me an error, the first being run time error 5
the second one being run time error 91

[Izandol]

Both work here with your code. You are clicking the button to run the code I guess?

[kickme93]

Both work here with your code. You are clicking the button to run the code I guess?

I click the button, it runs the sub StartTimer() - i'm putting the code in this sub and both are just giving of the error?

EDIT: Just thought, Could it be because i've created the commandbar in a class module and the subs are in a standard module?

[millz]

One possible reason is (another guess), you cannot disable a control holding the focus.

[Izandol]

It should not matter. Actioncontrol is a reference to the button you clicked. May you provide an example workbook?

[kickme93]

Please see attached add-in,

password to access code is: gamerscore20

Encryption Reminder.zip

[Izandol]

I have no errors adding the line to disable the control.

[xlbiznes]

Hi,

try this code :

Please Login or Register  to view this content.

[kickme93]

I have no errors adding the line to disable the control.

Could you please share how you've done it?

[Izandol]

I added

Please Login or Register  to view this content.

to the routine the button calls.

[kickme93]

I added

Please Login or Register  to view this content.

to the routine the button calls.

So you didn't do it in the subs?
When the user presses the button it should disable then after the timer has completed it should re enable itself?

[Izandol]

Subs are routines - which 'subs' do you refer to? If you must re-enable after the timed process you may store a reference to the control in a public variable:

Please Login or Register  to view this content.

In the StartTimer routine:

Please Login or Register  to view this content.

then in the finish of the timer routine:

Please Login or Register  to view this content.

[kickme93]

Subs are routines - which 'subs' do you refer to? If you must re-enable after the timed process you may store a reference to the control in a public variable:

Please Login or Register  to view this content.

In the StartTimer routine:

Please Login or Register  to view this content.

then in the finish of the timer routine:

Please Login or Register  to view this content.

When the user presses the "Timer" button - it runs the starttimer sub (it should disable just the timer button)
then after the timer has completed it runs the endtimer sub which should re enable the button.

That's what i've been trying to achieve.

[Izandol]

This is what I have answered. At first there was no mention of re-enabling the control - only question was how to disable it.

[kickme93]

This is what I have answered. At first there was no mention of re-enabling the control - only question was how to disable it.

Still getting error 91

Please Login or Register  to view this content.

[john55]

I think the simpliest way to get the desired result/help is to upload a test file having the same structure...all the solutions provided here work fine!

[kickme93]

I think the simpliest way to get the desired result/help is to upload a test file having the same structure...all the solutions provided here work fine!

This is an add-in that i've developed, so its going to work the same in any workbook regardless.

[john55]

k, good luck!

[Izandol]

The disabling works in your add-in, although I think you have many errors in your code because it does not compile. You have 'End If' statement missing and you should not make the StartTimer and StopTimer routines private if you wish to call them from other modules. The re-enabling does not occur however because your StopTimer code will only run when TimerEnabled = False.

[kickme93]

The disabling works in your add-in, although I think you have many errors in your code because it does not compile. You have 'End If' statement missing and you should not make the StartTimer and StopTimer routines private if you wish to call them from other modules. The re-enabling does not occur however because your StopTimer code will only run when TimerEnabled = False.

Would you be able to share with me the working code?
I very rarely program in vba so its a learning progress.

[Izandol]

It is:

Please Login or Register  to view this content.

You should also use change the code in order to add the controls only if they are not present already or you may have many dropdowns. Of course you may also use the Ribbon instead of commandbars.

[kickme93]

That is brilliant! Thank you so much for your time and helping me!

[Izandol]

You are welcome.