+ Reply to Thread
Results 1 to 9 of 9

Run an Event Worksheet_Change Macro

  1. #1
    Registered User
    Join Date
    11-17-2010
    Location
    Canberra, Australia
    MS-Off Ver
    Excel 2010
    Posts
    19

    Unhappy Run an Event Worksheet_Change Macro

    Hi all, I'm trying to run a macro that will unhide row's once an option is selected in a validation dropdown list. I'm using the following 'borrowed' code:

    Please Login or Register  to view this content.
    My problem is, Excel 2010 doesn't seem to be recognising the Event and when I hit run, a dialogue box appears asking me to create a macro. I'm stumped...can anyone help please?
    Last edited by gazzz_tha1; 11-24-2010 at 10:57 PM.

  2. #2
    Registered User
    Join Date
    11-17-2010
    Location
    Canberra, Australia
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Issue running an Event Worksheet_Change Macro in Excel 2010...

    Does nobody want to help with this?

  3. #3
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,887

    Re: Issue running an Event Worksheet_Change Macro in Excel 2010...

    Hi Gazzz, try:
    Please Login or Register  to view this content.

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Issue running an Event Worksheet_Change Macro in Excel 2010...

    Is the code in the sheet module?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Issue running an Event Worksheet_Change Macro in Excel 2010...

    FWIW, IMO:

    Please Login or Register  to view this content.
    is far from ideal

    The effect of the Exit Sub in this context means as an end user if you remember to always update more than one cell simultaneously you can circumvent whatever code was put in place... it follows that this leaves the file (potentially) open to error pending actions being undertaken in the VBA

    If >1 cell being altered then either (based on whether or not permitted):

    Permitted: process all relevant cells within the Target

    or

    Not Permitted: Undo the action altogether* (toggling events as normal)
    *Of course it's generally worth checking for blanks if they have no impact on the code rather than preventing mass clear.

  6. #6
    Registered User
    Join Date
    11-17-2010
    Location
    Canberra, Australia
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Issue running an Event Worksheet_Change Macro in Excel 2010...

    Hi Paul, royUK and DonkeyOte,

    I've tried copying and pasting Paul's code into the sheet module as well as the workbook module and neither is working. As soon as I paste it and press run, it opens the Macro's dialogue box (see Word doc, Screen Dump). I'm trying to do what I thought would be the simplest of tasks by getting a macro to unhide lines depending on a selection in a dropdown box but its proving troublesome. (Excel sheet attached also)
    Attached Files Attached Files

  7. #7
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,887

    Re: Issue running an Event Worksheet_Change Macro in Excel 2010...

    The macro I gave you was an example. It was similar to your original code. The workbook you just provided is nothing like the sample code you provided.

    The code I gave you needed a number of adjustments for it to work in your actual workbook. See below.
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    11-17-2010
    Location
    Canberra, Australia
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Issue running an Event Worksheet_Change Macro in Excel 2010...

    Paul,

    Thanks so much for your help, I've finally got my issue sorted and it all appears to be working properly now. I couldn't have done it without your help, thanks very much!

    Gaz

  9. #9
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,887

    Re: Issue running an Event Worksheet_Change Macro in Excel 2010...

    Glad I could help, and that you have it working. If you wouldn't mind, please mark the thread as solved.

    How to mark a thread Solved
    Go to the first post
    Click Edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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