+ Reply to Thread
Results 1 to 28 of 28

Excel VBA - Disable Command button in sub?

  1. #1
    Registered User
    Join Date
    08-27-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    39

    Excel VBA - Disable Command button in sub?

    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?
    Last edited by kickme93; 12-07-2013 at 05:47 AM.

  2. #2
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,062

    Re: Excel VBA - Disable Command button in sub?

    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
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

  3. #3
    Registered User
    Join Date
    08-27-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Excel VBA - Disable Command button in sub?

    Quote Originally Posted by john55 View Post
    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?

  4. #4
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Excel VBA - Disable Command button in sub?

    Try this maybe, just a guess.
    Please Login or Register  to view this content.

  5. #5
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Excel VBA - Disable Command button in sub?

    You may use simple:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    08-27-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Excel VBA - Disable Command button in sub?

    Quote Originally Posted by millz View Post
    Try this maybe, just a guess.
    Please Login or Register  to view this content.
    Quote Originally Posted by Izandol View Post
    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

  7. #7
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Excel VBA - Disable Command button in sub?

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

  8. #8
    Registered User
    Join Date
    08-27-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Excel VBA - Disable Command button in sub?

    Quote Originally Posted by Izandol View Post
    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?

  9. #9
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Excel VBA - Disable Command button in sub?

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

  10. #10
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Excel VBA - Disable Command button in sub?

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

  11. #11
    Registered User
    Join Date
    08-27-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Excel VBA - Disable Command button in sub?

    Please see attached add-in,

    password to access code is: gamerscore20

    Encryption Reminder.zip

  12. #12
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Excel VBA - Disable Command button in sub?

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

  13. #13
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: Excel VBA - Disable Command button in sub?

    Hi,

    try this code :

    Please Login or Register  to view this content.
    Happy Computing ,

    Xlbiznes.

    To show your appreciation please click *

  14. #14
    Registered User
    Join Date
    08-27-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Excel VBA - Disable Command button in sub?

    Quote Originally Posted by Izandol View Post
    I have no errors adding the line to disable the control.
    Could you please share how you've done it?

  15. #15
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Excel VBA - Disable Command button in sub?

    I added
    Please Login or Register  to view this content.
    to the routine the button calls.

  16. #16
    Registered User
    Join Date
    08-27-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Excel VBA - Disable Command button in sub?

    Quote Originally Posted by Izandol View Post
    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?

  17. #17
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Excel VBA - Disable Command button in sub?

    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.

  18. #18
    Registered User
    Join Date
    08-27-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Excel VBA - Disable Command button in sub?

    Quote Originally Posted by Izandol View Post
    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.

  19. #19
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Excel VBA - Disable Command button in sub?

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

  20. #20
    Registered User
    Join Date
    08-27-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Excel VBA - Disable Command button in sub?

    Quote Originally Posted by Izandol View Post
    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.

  21. #21
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,062

    Re: Excel VBA - Disable Command button in sub?

    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!

  22. #22
    Registered User
    Join Date
    08-27-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Excel VBA - Disable Command button in sub?

    Quote Originally Posted by john55 View Post
    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.

  23. #23
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,062

    Re: Excel VBA - Disable Command button in sub?

    k, good luck!

  24. #24
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Excel VBA - Disable Command button in sub?

    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.

  25. #25
    Registered User
    Join Date
    08-27-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Excel VBA - Disable Command button in sub?

    Quote Originally Posted by Izandol View Post
    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.

  26. #26
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Excel VBA - Disable Command button in sub?

    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.

  27. #27
    Registered User
    Join Date
    08-27-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Excel VBA - Disable Command button in sub?

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

  28. #28
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Excel VBA - Disable Command button in sub?

    You are welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Disable command button not working in excel 2010
    By cneha1991 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-30-2011, 07:36 AM
  2. Disable command button
    By kuraitori in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-01-2009, 03:38 AM
  3. Disable Command Button
    By Hasnain in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-07-2008, 01:32 PM
  4. [SOLVED] Disable Command Button
    By Raj in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-10-2006, 01:01 AM
  5. Disable command button
    By Mikeice in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2005, 06:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1