Results 1 to 7 of 7

Using multiple 'Worksheet_Change' commands

Threaded View

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

    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;
    Private Sub Worksheet_Change(ByVal Target As Range)
        Application.Calculate
        Dim c As Range
        Application.EnableEvents = False
        For Each c In Target
            If Not Intersect(Target, Range("H2:I100")) Is Nothing Then c.Value = UCase(c.Value)
            If Not Intersect(Target, Range("E2:E100")) Is Nothing Then
                ActiveSheet.Unprotect
                If c = "" And c.Value = "S.Triage Ticket" Then c.Offset(0, 1).Resize(, 5).Locked = False
                If c <> "" And c.Value <> "S.Triage Ticket" Then c.Offset(0, 1).Resize(, 5).Locked = True
                ActiveSheet.Protect
            End If
        Next c
        Application.EnableEvents = True
    End Sub
    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.

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