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.
Bookmarks