Results 1 to 11 of 11

Combining two Worksheet_SelectionChange Events help!

Threaded View

  1. #1
    Registered User
    Join Date
    07-28-2016
    Location
    notts
    MS-Off Ver
    2010
    Posts
    10

    Question Combining two Worksheet_SelectionChange Events help!

    Hi Guys, I have one worksheet_selectionchange which tracks and logs changes to a worksheet on a different worksheet. I have a second worksheet_selectionchange which prevents users from selecting multiple cells.
    I cannot for the life of me get them to work together, I can either track the changes or prevent multiple cells selection. I've copied my code below, is there anyone that can help get this working?
    The code to track changes;

    Option Explicit
    Dim StrtValue As String
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TargetTrain As String
    With Sheets("LogSheet")
    
    If StrtValue = Empty Then Exit Sub
    
    .Range("A65536").End(xlUp).Offset(1).Value = Target.Address
    .Range("B65536").End(xlUp).Offset(1).Value = StrtValue
    .Range("C65536").End(xlUp).Offset(1).Value = Target.Value
    .Range("D65536").End(xlUp).Offset(1).Value = Range("C" & Target.Row).Value
    
    If Target.Column = 1 Then .Range("E65536").End(xlUp).Offset(1).Value = "First Name"
    If Target.Column = 2 Then .Range("E65536").End(xlUp).Offset(1).Value = "Surname"
    If Target.Column = 3 Then .Range("E65536").End(xlUp).Offset(1).Value = "Employee Number"
    If Target.Column = 4 Then .Range("E65536").End(xlUp).Offset(1).Value = "CityCare Corporate Induction"
    If Target.Column = 5 Then .Range("E65536").End(xlUp).Offset(1).Value = "CityCare Connect in-house Induction"
    If Target.Column = 6 Then .Range("E65536").End(xlUp).Offset(1).Value = "Care Certificate"
    If Target.Column = 7 Then .Range("E65536").End(xlUp).Offset(1).Value = "Health and Safety"
    If Target.Column = 8 Then .Range("E65536").End(xlUp).Offset(1).Value = "Fire Safety"
    If Target.Column = 9 Then .Range("E65536").End(xlUp).Offset(1).Value = "COSHH"
    If Target.Column = 10 Then .Range("E65536").End(xlUp).Offset(1).Value = "First Aid"
    If Target.Column = 11 Then .Range("E65536").End(xlUp).Offset(1).Value = "Safe Use of Bed Rails"
    If Target.Column = 12 Then .Range("E65536").End(xlUp).Offset(1).Value = "Manual Handling"
    If Target.Column = 13 Then .Range("E65536").End(xlUp).Offset(1).Value = "Food Hygiene and Hygiene - Level 2"
    If Target.Column = 14 Then .Range("E65536").End(xlUp).Offset(1).Value = "Falls & Osteoporosis Awareness"
    If Target.Column = 15 Then .Range("E65536").End(xlUp).Offset(1).Value = "Infection Prevention"
    If Target.Column = 16 Then .Range("E65536").End(xlUp).Offset(1).Value = "Safeguarding"
    If Target.Column = 17 Then .Range("E65536").End(xlUp).Offset(1).Value = "Coroners Training"
    If Target.Column = 18 Then .Range("E65536").End(xlUp).Offset(1).Value = "Falls & Osteoporosis Awareness"
    If Target.Column = 19 Then .Range("E65536").End(xlUp).Offset(1).Value = "Infection Prevention, control & hand hygeine"
    If Target.Column = 20 Then .Range("E65536").End(xlUp).Offset(1).Value = "Fire Marshall"
    If Target.Column = 21 Then .Range("E65536").End(xlUp).Offset(1).Value = "Customer Service"
    If Target.Column = 22 Then .Range("E65536").End(xlUp).Offset(1).Value = "Food Safety Level 2"
    If Target.Column = 23 Then .Range("E65536").End(xlUp).Offset(1).Value = "Record Keeping"
    If Target.Column = 24 Then .Range("E65536").End(xlUp).Offset(1).Value = "Food and Nutrition"
    If Target.Column = 25 Then .Range("E65536").End(xlUp).Offset(1).Value = "Equality"
    If Target.Column = 26 Then .Range("E65536").End(xlUp).Offset(1).Value = "Spiritual & Pastoral Care"
    If Target.Column = 27 Then .Range("E65536").End(xlUp).Offset(1).Value = "Risk Assessment"
    If Target.Column = 28 Then .Range("E65536").End(xlUp).Offset(1).Value = "Supervision and Appraisal"
    If Target.Column = 29 Then .Range("E65536").End(xlUp).Offset(1).Value = "Managing sickness absence"
    If Target.Column = 30 Then .Range("E65536").End(xlUp).Offset(1).Value = "Level 2 Award Legionella Awareness"
    If Target.Column = 31 Then .Range("E65536").End(xlUp).Offset(1).Value = "Falls Care Planning"
    If Target.Column = 32 Then .Range("E65536").End(xlUp).Offset(1).Value = "Tissue Viability"
    If Target.Column = 33 Then .Range("E65536").End(xlUp).Offset(1).Value = "Continence"
    If Target.Column = 34 Then .Range("E65536").End(xlUp).Offset(1).Value = "Catheter Care"
    If Target.Column = 35 Then .Range("E65536").End(xlUp).Offset(1).Value = "Care Planning"
    If Target.Column = 36 Then .Range("E65536").End(xlUp).Offset(1).Value = "PCP"
    If Target.Column = 37 Then .Range("E65536").End(xlUp).Offset(1).Value = "MCA and DoLS"
    If Target.Column = 38 Then .Range("E65536").End(xlUp).Offset(1).Value = "Tobacco/Alcohol/ Substance Misuse"
    If Target.Column = 39 Then .Range("E65536").End(xlUp).Offset(1).Value = "Reablement Shadowing"
    If Target.Column = 40 Then .Range("E65536").End(xlUp).Offset(1).Value = "MUST"
    If Target.Column = 41 Then .Range("E65536").End(xlUp).Offset(1).Value = "Safeguarding of Vulnerable Adults"
    If Target.Column = 42 Then .Range("E65536").End(xlUp).Offset(1).Value = "Mental Capacity act"
    If Target.Column = 43 Then .Range("E65536").End(xlUp).Offset(1).Value = "Person Centred Approaches"
    If Target.Column = 44 Then .Range("E65536").End(xlUp).Offset(1).Value = "Safe moving and Handling of people"
    If Target.Column = 45 Then .Range("E65536").End(xlUp).Offset(1).Value = "End of life care"
    If Target.Column = 46 Then .Range("E65536").End(xlUp).Offset(1).Value = "SSKIN"
    If Target.Column = 47 Then .Range("E65536").End(xlUp).Offset(1).Value = "Spinal Injuries & Dysreflexia"
    If Target.Column = 48 Then .Range("E65536").End(xlUp).Offset(1).Value = "Reablement"
    If Target.Column = 49 Then .Range("E65536").End(xlUp).Offset(1).Value = "Pressure Ulcer Prenfg"
    If Target.Column = 50 Then .Range("E65536").End(xlUp).Offset(1).Value = "Speech & Language Therapy (SALT)"
    If Target.Column = 51 Then .Range("E65536").End(xlUp).Offset(1).Value = "Dementia"
    If Target.Column = 52 Then .Range("E65536").End(xlUp).Offset(1).Value = "Documentation Training-EKOS"
    If Target.Column = 53 Then .Range("E65536").End(xlUp).Offset(1).Value = "Catheter Care"
    If Target.Column = 54 Then .Range("E65536").End(xlUp).Offset(1).Value = "care home workshop"
    If Target.Column = 55 Then .Range("E65536").End(xlUp).Offset(1).Value = "CityCare General Administration of Medicines"
    If Target.Column = 56 Then .Range("E65536").End(xlUp).Offset(1).Value = "CityCare Safe Use and Application of Creams and Ointments"
    If Target.Column = 57 Then .Range("E65536").End(xlUp).Offset(1).Value = "Boots Medication System's Training: Manrex/MDS/ Monitored dosage training"
    If Target.Column = 58 Then .Range("E65536").End(xlUp).Offset(1).Value = "Boots Care of Medicines Foundation"
    If Target.Column = 59 Then .Range("E65536").End(xlUp).Offset(1).Value = "Boots Care of Medicines Advanced"
    If Target.Column = 60 Then .Range("E65536").End(xlUp).Offset(1).Value = "Medication Awareness"
    If Target.Column = 61 Then .Range("E65536").End(xlUp).Offset(1).Value = "Medication Training for acting overnight care Staff"
    If Target.Column = 62 Then .Range("E65536").End(xlUp).Offset(1).Value = "CCG Manditory Medication Training"
    If Target.Column = 63 Then .Range("E65536").End(xlUp).Offset(1).Value = "Nutrition and Hydration"
    If Target.Column = 64 Then .Range("E65536").End(xlUp).Offset(1).Value = "Special Dietary Needs"
    If Target.Column = 65 Then .Range("E65536").End(xlUp).Offset(1).Value = "Systems and Equipment"
    If Target.Column = 66 Then .Range("E65536").End(xlUp).Offset(1).Value = "Food Processing Level 2"
    If Target.Column = 67 Then .Range("E65536").End(xlUp).Offset(1).Value = "Level 3 Professional Cookery"
    If Target.Column = 68 Then .Range("E65536").End(xlUp).Offset(1).Value = "Level 3 Food Safety & Hygiene"
    If Target.Column = 69 Then .Range("E65536").End(xlUp).Offset(1).Value = "Understanding Infection Control"
    If Target.Column = 70 Then .Range("E65536").End(xlUp).Offset(1).Value = "NVQ Level 2 Kitchen Services"
    If Target.Column = 71 Then .Range("E65536").End(xlUp).Offset(1).Value = "JLA Systems and Equipment"
    If Target.Column = 72 Then .Range("E65536").End(xlUp).Offset(1).Value = "PPE"
    If Target.Column = 73 Then .Range("E65536").End(xlUp).Offset(1).Value = "Rotowash"
    If Target.Column = 74 Then .Range("E65536").End(xlUp).Offset(1).Value = "Chlor-clean"
    If Target.Column = 75 Then .Range("E65536").End(xlUp).Offset(1).Value = "Vileda"
    If Target.Column = 76 Then .Range("E65536").End(xlUp).Offset(1).Value = "Integral Induction"
    If Target.Column = 77 Then .Range("E65536").End(xlUp).Offset(1).Value = "L8 (Legionella)"
    If Target.Column = 78 Then .Range("E65536").End(xlUp).Offset(1).Value = "Portable Appliance Testing"
    If Target.Column = 79 Then .Range("E65536").End(xlUp).Offset(1).Value = "IOSHH Working Safely"
    If Target.Column = 80 Then .Range("E65536").End(xlUp).Offset(1).Value = "Electrical Safety"
    
    End With
    End Sub
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    
    StrtValue = Target.Value
    End Sub
    
    And the code to prevent multiple cell selection;

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then
    MsgBox "Please select only one cell", vbOKOnly, "Multiple cell detection"
    Range("A1").Select
    End If
    End Sub
    Thanks in advance guys, of course let me know if I've broken any rules or anything.
    Last edited by tabbytomo; 07-29-2016 at 03:35 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Combine Worksheet_SelectionChange and Worksheet_Change events
    By quetzalc0atl in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 01-10-2020, 12:55 AM
  2. Combining two worksheet change events
    By djfergie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-14-2013, 11:07 AM
  3. Combining two Worksheet_Change Events
    By Fett2oo5 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-09-2013, 01:05 PM
  4. [SOLVED] Combining two opening events when file is opened
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-05-2013, 03:28 PM
  5. Need help combining multiple Worksheet_Change events
    By rlbush2 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-27-2012, 04:51 PM
  6. [SOLVED] Combining Two Change Events
    By computerdan in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-25-2012, 02:00 AM
  7. Replies: 0
    Last Post: 01-21-2006, 11:20 PM

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