+ Reply to Thread
Results 1 to 20 of 20

Toggle button to run a Worksheet_Change macro

  1. #1
    Registered User
    Join Date
    10-21-2014
    Location
    London
    MS-Off Ver
    2016
    Posts
    24

    Toggle button to run a Worksheet_Change macro

    Hey guys!

    I am trying to call a Private Sub Worksheet_Change macro using a toggle button, which I have built into the ribbon with the help of Custom UI.

    If the toggle button has been activated, then the Worksheet_Change macro should be executed whenever the value in A2 changes. Once the value in A2 has changed, B2 captures that specific value, whereas C2 captures the time when the change was made. With every change in A2, cells in column B and C collect the data points.

    If the toggle button has been deactivated, then the Worksheet_Change macro shouldn't be triggered and any changes in A2 simply be ignored.

    Please find my macro below:

    Please Login or Register  to view this content.
    Hope someone can help me!

    Thanks!

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Toggle button to run a Worksheet_Change macro

    I'm not aware you can call a built in sheet event so I'll be interested to hear otherwise.

    However can't you use the ribbon toggle button to write a value to a cell somewhere and then read the state of that cell at the start of the Sheet_Change event and exit the sub as required.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    10-21-2014
    Location
    London
    MS-Off Ver
    2016
    Posts
    24

    Re: Toggle button to run a Worksheet_Change macro

    I am not sure I understand what you mean. If possible could you please share a macro that reflects your idea?

    Thanks

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Toggle button to run a Worksheet_Change macro

    If you want to toggle the action taken within the change event then use a global variable to hold the state of the toggle button

    standard code module
    Please Login or Register  to view this content.
    sheet event code
    Please Login or Register  to view this content.
    If you have ribbon load event you can set the status of the variable there to match that of the toggle button.

    If you need to execute the change event when the toggle button is enabled then move the code that checks and updates the sheet out of the event into a routine you can call.
    You would also need to call that routine from the event
    Cheers
    Andy
    www.andypope.info

  5. #5
    Registered User
    Join Date
    10-21-2014
    Location
    London
    MS-Off Ver
    2016
    Posts
    24

    Re: Toggle button to run a Worksheet_Change macro

    Thank you for the code and that you try to help me. I've used the code as shown in the attached pic, unfortuantely nothing happens. I must be doing something wrong, I guess?Attachment 660564

    Also, I don't quite get what you mean when it comes to the ribbon load event.

    Thanks!

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Toggle button to run a Worksheet_Change macro

    You attachment link does not work. Attach example workbook, as per instruction in the banner at the top of the page

    Attached is my example. enter data into A2 and nothing will happen.
    Press the toggle button on Tab1 and then enter data in A2 to execute change event code
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-21-2014
    Location
    London
    MS-Off Ver
    2016
    Posts
    24

    Re: Toggle button to run a Worksheet_Change macro

    Your file works indeed flawlessly, however when I convert it from .xlsm to .xlam, I start facing problems.

    Whilst I was trying to upload my .xlam file, I have realised that this type of files is not allowed. With that in mind I can only upload screenshots. I have highlighted in yellow all the details that might be important. Hope it will help to sort the issue.

    Thanks again!!!
    Attached Images Attached Images

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Toggle button to run a Worksheet_Change macro

    As an addin does not have a visible workbook the worksheet change event will not happen by the user changing anything.

    If you want you code to run for any workbook then you will need to create an application object, with events, to listen for changes in a worksheet.
    See this recent post for more details.
    https://www.excelforum.com/excel-pro...workbooks.html

  9. #9
    Registered User
    Join Date
    10-21-2014
    Location
    London
    MS-Off Ver
    2016
    Posts
    24

    Re: Toggle button to run a Worksheet_Change macro

    Thank you for giving me a hint. It's good to know that it's feasible. I will be busy for a few days and get back to this topic later this week. I will definitely give you a feedback whether or not I've managed to solve this task.

    Thanks again!

  10. #10
    Registered User
    Join Date
    10-21-2014
    Location
    London
    MS-Off Ver
    2016
    Posts
    24

    Re: Toggle button to run a Worksheet_Change macro

    I have created an application object that I called xlApp but it doesn't seem to work. Please can you tell me where the mistake is?

    Please Login or Register  to view this content.

  11. #11
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Toggle button to run a Worksheet_Change macro

    Quote Originally Posted by Andy Pope View Post
    . . . use a global variable to hold the state of the toggle button

    Please Login or Register  to view this content.
    Note that external variables are reset (in the case of Booleans to FALSE) when the VBA project is reset, e.g., when throwing a runtime error and choosing to stop a macro rather than debug it. Arguably safer to use a hidden name, plus that'd allow saving the state when the workbook is saved and closed then subsequently reopened.

  12. #12
    Registered User
    Join Date
    10-21-2014
    Location
    London
    MS-Off Ver
    2016
    Posts
    24

    Re: Toggle button to run a Worksheet_Change macro

    I am totally confused now. Please can you adjust my macro from above, so I can see the changes? Thanks in advance!

  13. #13
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Toggle button to run a Worksheet_Change macro

    Hi birdman

    It is a simple thing to 'toggle' whether any worksheet-event macro runs or not.
    Forget about using global variables - because when any vba routine 'malfunctions' i.e. debugs/fails etc etc etc then ALL current global variables in memory are lost. Unless you are a brilliant coder and can guarantee you will never have a macro fail, maybe you should avoid global variables. Just saying.
    It is much better to just refer to a particular cell (it could be a named-cell if you prefer).
    For example, here is your modified code..
    Please Login or Register  to view this content.
    With this code, you just need to have cell [Z1] contain the text "OFF" to stop that event-macro from running.
    You could easily create a button do put "OFF" or "ON" in that particular cell.
    You could even have that button display whether it is "ON" or "OFF" as the button-text.

    zeddy

  14. #14
    Registered User
    Join Date
    10-21-2014
    Location
    London
    MS-Off Ver
    2016
    Posts
    24

    Re: Toggle button to run a Worksheet_Change macro

    Hi zeddy,

    I appreciate your help, however let me quickly explain what I am trying to achieve here. I have a .xlam file with a built in tab that contains a toggle button that appears in the ribbon whenever Excel is loaded.

    The idea is to let macro run from any workbook and any worksheet once the toggle button gets activated, as it is always visible because it's in the ribbon, so I would just need to click on it. If that's the case, then any changes in cell A2 shall initiate the collection of data, otherwise nothing should happen. Please see the initial post.

    I can make it work as a part of a specific .xlsm file, however the challenge is to make it work as a part of .xlam file and this is why application events need to be taken into account.

    Hope that helps! Thanks again!
    birdman

  15. #15
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    467

    Re: Toggle button to run a Worksheet_Change macro

    Hi birdman

    Got it.
    I would probably use Andy Pope's listening device (post#8) together with an ON/OFF flag set in the hidden-name-space for the current Excel instance.

    zeddy

  16. #16
    Registered User
    Join Date
    10-21-2014
    Location
    London
    MS-Off Ver
    2016
    Posts
    24

    Re: Toggle button to run a Worksheet_Change macro

    Quote Originally Posted by Andy Pope View Post
    As an addin does not have a visible workbook the worksheet change event will not happen by the user changing anything.

    If you want you code to run for any workbook then you will need to create an application object, with events, to listen for changes in a worksheet.
    See this recent post for more details.
    https://www.excelforum.com/excel-pro...workbooks.html
    Hi Andy,

    Please can you have a quick look and tell me why the following macro doesn't work. I've tried to work with application events but there must be at least one or potentially more mistakes somewhere.

    Please Login or Register  to view this content.
    Really appreciate your help!

    Thanks
    birdmannn

  17. #17
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Toggle button to run a Worksheet_Change macro

    Is that code in the Thisworkbook object or a standard code module?

    Either way I think it should be split

    Thisworkbook
    Please Login or Register  to view this content.
    Standard code module
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    10-21-2014
    Location
    London
    MS-Off Ver
    2016
    Posts
    24

    Re: Toggle button to run a Worksheet_Change macro

    Hi Andy,

    Unfortuantely, it doesn't work. When the toggle button is activated and I change values in A2, nothing happens.

    Could it be because I don't address "Sh" although previously declared in
    Please Login or Register  to view this content.
    If so, how do I address it correctly? Shall I simply replace Worksheets with Sh?

    Thank you for your help!!!!!
    birdmannn

  19. #19
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Toggle button to run a Worksheet_Change macro

    Your code is saying if I'm listening for changes,
    and the target being changed has the same address as cell A2 then
    get the last row of the first worksheet in active workbook using column 2
    and make the contents of cell lastrow+1 column B, of the first worksheet in active book the same as cell A2 of first worksheet in active workbook
    and make the contents of cell lastrow+1 column C, of the first worksheet in active book the current time

    Is the above what you are trying to do? If not it could be that you need to change worksheets(1) to Sh. If you want to store content and time in the same sheet as being changed.

    With out seeing the actual add-in, the code and a definition of "it doesn't work" it's hard to say how to fix it.

  20. #20
    Registered User
    Join Date
    10-21-2014
    Location
    London
    MS-Off Ver
    2016
    Posts
    24

    Re: Toggle button to run a Worksheet_Change macro

    Hi Andy,

    Problem solved!!!!!!!!
    I have replaced Worksheets(1) with Sh and now it performs exactly what you have described in your last post with one small exception - however exactly how I wanted it to be - it is not necessarily the first worksheet but any active worksheet.

    Thank you so much!!!!
    birdmannn

+ 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. [SOLVED] how do i make a toggle button change other toggle buttons value to false
    By kevinu in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-10-2018, 04:19 PM
  2. [SOLVED] Run/Stop macro using toggle button
    By ChipsSlave in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-07-2017, 06:04 PM
  3. Replies: 1
    Last Post: 02-13-2014, 10:55 PM
  4. Toggle Button VBA Handler macro
    By re0 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-02-2013, 07:51 PM
  5. [SOLVED] Toggle Button, Macro Off/On Help
    By ike609 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-17-2012, 09:27 AM
  6. Toggle Macro on Button
    By Baziwan in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-05-2010, 01:08 PM
  7. Macro in a Toggle Button
    By Preatorian in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-28-2009, 09:20 AM

Tags for this Thread

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