+ Reply to Thread
Results 1 to 5 of 5

code to toggle (enable/disable) a specific Worksheet_SelectionChange macro

  1. #1
    Registered User
    Join Date
    10-19-2013
    Location
    Georgia
    MS-Off Ver
    Excel 2016
    Posts
    10

    Post code to toggle (enable/disable) a specific Worksheet_SelectionChange macro

    I am using the following code for several different tables in one workbook:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("Table2").RowHeight = 15
    Selection.Rows.AutoFit
    End Sub

    Some of my colleagues love this feature but others find it annoying, so I would like to provide toggle buttons that enable and disable the macro a particular table. Can someone provide the code to implement this with a button that either
    (1) references the macro above or
    (2) incorporates that functionality, replacing the macro above?

    Also, which button or other control would be most suitable?

  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: code to toggle (enable/disable) a specific Worksheet_SelectionChange macro

    You've attached that macro to a sheet Selection change event which means it will run whenever any cell is selected.

    Are the tables on different sheets? If so one option might be to use an Forms Control Checkbox somewhere which sets the value of the linked cell to True or False. Name the linked cell say SetRowHeight

    Then test the cell value with an IF test as the first line of your macro

    e.g.
    If Range("SetRowHeight") = False Then Exit Sub

    You could use a Workbook Sheet Change event instead of a Sheet Selection Change. This would avoid the need for a macro on every sheet although it will only run when a cell value is changed, or at least selected and re-entered, unlike the Sheet Selection change vent which runs whenever a cell is selected
    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
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,080

    Re: code to toggle (enable/disable) a specific Worksheet_SelectionChange macro

    If that macro is the only event macro in your workbook, you can disable and enable events using a macro for each task. Place these two macros in regular modules. Assign a button to each macro. The first macro will allow your event macro to run. The second macro will disable it.
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  4. #4
    Registered User
    Join Date
    10-19-2013
    Location
    Georgia
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: code to toggle (enable/disable) a specific Worksheet_SelectionChange macro

    Thanks, Richard. That solution should do nicely.

  5. #5
    Registered User
    Join Date
    10-19-2013
    Location
    Georgia
    MS-Off Ver
    Excel 2016
    Posts
    10

    Re: code to toggle (enable/disable) a specific Worksheet_SelectionChange macro

    Thanks very much!

+ 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. VBA code for Disable/Enable cells
    By sarbashpk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-21-2020, 11:21 AM
  2. Enable a specific event during Disable Events
    By archieross123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-27-2018, 04:20 AM
  3. VBA Code to Know Macro Enable or Disable when Open workbook
    By GOLDENEXCEL in forum Excel General
    Replies: 3
    Last Post: 10-09-2015, 08:52 AM
  4. Enable & disable conditional formatting using toggle button
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-16-2015, 01:50 PM
  5. Enable/Disable vba code
    By henk400 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2015, 08:16 AM
  6. [SOLVED] Code to disable and enable indentations
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-14-2014, 01:38 PM
  7. Create A Button In Document To Disable/Enable A Specific Line of Code
    By BLK306 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-05-2014, 02:12 PM

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