+ Reply to Thread
Results 1 to 7 of 7

Using multiple 'Worksheet_Change' commands

  1. #1
    Forum Contributor
    Join Date
    11-08-2012
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2016
    Posts
    140

    Using multiple 'Worksheet_Change' commands

    Hello,

    I am trying to use the 'Worksheet_Change' sub routine twice. From what I understand the sub routine itself can only be used once so I need to merge my code into the one which is where I am having the trouble.

    The first part works by itself and changes the case from lower case to upper case;
    Please Login or Register  to view this content.
    The second part I cannot get the coding correct and also having problems merging it into the 'Worksheet_Change' sub routine.
    What I want to do is look at the current cell, which is a drop-down, and depending on the cell contents I want to protect / lock several other cells. The code I currently have is;
    Please Login or Register  to view this content.
    Please can I have help on the second part of the code and help merging it into the 'Worksheet_Change' sub routine.

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Using multiple 'Worksheet_Change' commands

    Try
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Using multiple 'Worksheet_Change' commands

    Try ending your If in the first section, before starting your next If statement.

    Please Login or Register  to view this content.
    Not sure what your workbook looks like... If you attach a workbook, it will help.

  4. #4
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Using multiple 'Worksheet_Change' commands

    Ooooh... Olly, much cleaner... I like it

  5. #5
    Forum Contributor
    Join Date
    11-08-2012
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2016
    Posts
    140

    Re: Using multiple 'Worksheet_Change' commands

    Hi Olly,

    Thanks for the help so far. The edited it slightly to get the correct cells to lock and now that bit is working. I didn't think it through before but I also need to unlock the cells if the cell becomes blank or contains "S.Triage Ticket".
    I have had a go but can't get the code correct, what I have is;
    Please Login or Register  to view this content.
    When I change the cell to blank or "S.Triage Ticket", the compiler skips past that line.
    The range "E2.E11" are the cells containing the drop down list. Thanks.
    Last edited by mowens74; 02-09-2016 at 10:19 AM.

  6. #6
    Registered User
    Join Date
    01-15-2016
    Location
    Dallas, TX
    MS-Off Ver
    2007
    Posts
    65

    Re: Using multiple 'Worksheet_Change' commands

    You are telling the application that If c="" AND c.value="S.Triage Ticket"... c will not likely be both blank and contain a value. In your statement above you indicate that this should be "OR"

  7. #7
    Forum Contributor
    Join Date
    11-08-2012
    Location
    Manchester, UK
    MS-Off Ver
    Excel 2016
    Posts
    140

    Re: Using multiple 'Worksheet_Change' commands

    What a tool, as soon as I saw your reply Raymundus I realized
    Thank you Olly and Raymundus for your help, this is not resolved.

+ 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. multiple offset commands
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-18-2015, 03:02 PM
  2. [SOLVED] VBA commands for multiple cells?
    By JLoKyM in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-29-2013, 07:32 AM
  3. Multiple IF & +IF commands
    By simpleton58 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-29-2013, 01:37 PM
  4. Macro Recording Chart Format commands missing all important commands!!!!
    By nounours in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-05-2013, 09:20 PM
  5. Multiple Commands
    By jpxexcelforum in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-08-2011, 04:37 AM
  6. Multiple Commands with Variables
    By JLucoff in forum Excel General
    Replies: 3
    Last Post: 07-26-2011, 04:26 PM
  7. Multiple If Commands
    By markswan20 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-17-2008, 05:54 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