Hi Everyone,
I would like to set up my sheet this way: in column A a date & time; in column B a list, i.e., "15 Minute Break," "911 Call," "BHS Assist," etc, and the other columns along the same row would fill in dependent on the event chosen in column B. I originally set one part of it in a module but I would like to have it as a Selection Change for all the above details. For Example: in column B, 15 Minute Break is chosen, the date & time should automatically show up in column A, with the word "~Break~" filling in on the same row but in another column....
Whatever help I can get will be much appreciated!
Windows 7; Excel 2010....
If ActiveCell = "15 Minute Break" Then
ActiveCell.Offset(0, 3).Value = "15 Minute Break"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "30 Minute Lunch" Then
ActiveCell.Offset(0, 3).Value = "30 Minute Lunch"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "911 Call" Then
ActiveCell.Offset(0, 3).Value = "Type of problem…."
ActiveCell.Offset(0, 4).Value = "Have they called 911 or are you referring them to call 911…"
ElseIf ActiveCell = "911_Paramedics" Then
ActiveCell.Offset(0, 3).Value = "From Suite number or deparment…"
ActiveCell.Offset(0, 4).Value = "If known, determine the type of distress or problem….."
ElseIf ActiveCell = "Badging/Denise" Then
ActiveCell.Offset(0, 3).Value = "Exchge/New/Request?"
ActiveCell.Offset(0, 4).Value = "If Denise is not here, did you help them by giving them the appropriate form to fill out and/or telling them her hours of operations on Tues & Thurs"
ElseIf ActiveCell = "BHS Assist" Then
ActiveCell.Offset(0, 3).Value = "For Medicate/State Specific Reason?"
ActiveCell.Offset(0, 4).Value = "If known, level 1 or 2..what room number?"
ElseIf ActiveCell = "BHS Patient Escort" Then
ActiveCell.Offset(0, 3).Value = "From BHS to >"
ActiveCell.Offset(0, 4).Value = "Name of Destination here…"
ElseIf ActiveCell = "Caller" Then
ActiveCell.Offset(0, 3).Value = "General, not associated with other events"
ActiveCell.Offset(0, 4).Value = "Notes, if any, to jotted down for the nature of the call; once the nature of the event is known, enter the event in column B in a new row…"
ElseIf ActiveCell = "Cash Escort Café" Then
ActiveCell.Offset(0, 3) = "Location From and To"
ActiveCell.Offset(0, 4).Value = ""
ElseIf ActiveCell = "Cash Escort Foundation" Then
ActiveCell.Offset(0, 3).Value = "Location From/To"
ActiveCell.Offset(0, 4).Value = "Special detail from 505 building, SJMP, and St Joseph Hospital finance"
ElseIf ActiveCell = "Code Blue" Then
ActiveCell.Offset(0, 3).Value = "Location/Room #"
ActiveCell.Offset(0, 4).Value = "Officer should be sent up for crowd control problems, if any…"
ElseIf ActiveCell = "Code Gray" Then
ActiveCell.Offset(0, 3).Value = "Room/Section/Area"
ActiveCell.Offset(0, 4).Value = "After original Code Gray event, all other events in relation to this should fall under Status or other type of events"
ElseIf ActiveCell = "Code Pink" Then
ActiveCell.Offset(0, 3).Value = "Baby / Tag? Is it an Exit or Tamper Alarm"
ActiveCell.Offset(0, 4).Value = "Check Hugs computer for a location of last exciter"
ElseIf ActiveCell = "Code Red" Then
ActiveCell.Offset(0, 3).Value = "Area/Location"
ActiveCell.Offset(0, 4).Value = "Device #....smoke or flame detector…was the fire department called off? Yes or No"
ElseIf ActiveCell = "Code White" Then
ActiveCell.Offset(0, 3).Value = "Location/Room #"
ActiveCell.Offset(0, 4).Value = "Officer should be sent up for crowd control problems, if any…"
ElseIf ActiveCell = "Delivery Gate" Then
ActiveCell.Offset(0, 3).Value = "Company Name"
ActiveCell.Offset(0, 4).Value = "Granted Access to Loading Dock"
ElseIf ActiveCell = "Disorderly Patient" Then
ActiveCell.Offset(0, 3).Value = ""
ActiveCell.Offset(0, 4).Value = ""
ElseIf ActiveCell = "Disorderly Person" Then
ActiveCell.Offset(0, 3).Value = "Building/Room, Etc"
ActiveCell.Offset(0, 4).Value = "After original Disorderly Person event, all other events in relation to this should fall under Status or other type of events"
ElseIf ActiveCell = "Elevator Trouble" Then
ActiveCell.Offset(0, 3).Value = "Telephone/Persons Stuck"
ActiveCell.Offset(0, 4).Value = "if unknown location, correspond Elevator number with map in the control center…"
ElseIf ActiveCell = "ER Patient Escort" Then
ActiveCell.Offset(0, 3).Value = "From ER Room # to >"
ActiveCell.Offset(0, 4).Value = "Name of Location"
ElseIf ActiveCell = "Fire System Test" Then
ActiveCell.Offset(0, 3).Value = "For what facility?"
ActiveCell.Offset(0, 4).Value = ""
ElseIf ActiveCell = "Graffiti" Then
ActiveCell.Offset(0, 3).Value = "What Area or Location?"
ActiveCell.Offset(0, 4).Value = "A report should be generated if it could not be sufficiently removed or specify who cleaned it up…"
ElseIf ActiveCell = "Henry Brothers Issue" Then
ActiveCell.Offset(0, 3).Value = "Door/Reader #"
ActiveCell.Offset(0, 4).Value = "this was called into HBE for a work order; if it was referred back to SJH, specify why here…."
ElseIf ActiveCell = "House Supervisor" Then
ActiveCell.Offset(0, 3).Value = "Problem/Concern"
ActiveCell.Offset(0, 4).Value = ""
ElseIf ActiveCell = "Hugs Comp Down" Then
ActiveCell.Offset(0, 3).Value = "Which One?"
ActiveCell.Offset(0, 4).Value = ""
ElseIf ActiveCell = "Hugs System" Then
ActiveCell.Offset(0, 3).Value = "Discharge/Loose Tag?"
ActiveCell.Offset(0, 4).Value = ""
ElseIf ActiveCell = "Jumpstart" Then
ActiveCell.Offset(0, 3).Value = "Area/Location"
ActiveCell.Offset(0, 4).Value = "who handled the call, either Security or Minuteman?"
ElseIf ActiveCell = "Jumpstart Charger" Then
ActiveCell.Offset(0, 3).Value = "Minuteman Employee"
ActiveCell.Offset(0, 4).Value = "specify whether they are taking the charger out or bringing it back in…"
ElseIf ActiveCell = "Key Assist/Unlock" Then
ActiveCell.Offset(0, 3).Value = "Type of Office/Room"
ActiveCell.Offset(0, 4).Value = "specify whether they are taking the charger out or bringing it back in…"
ElseIf ActiveCell = "Loud Noise" Then
ActiveCell.Offset(0, 3).Value = "Where? Who?"
ActiveCell.Offset(0, 4).Value = "specify whether they are taking the charger out or bringing it back in…"
ElseIf ActiveCell = "Mail Delivery" Then
ActiveCell.Offset(0, 3).Value = ""
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Medicate Patient" Then
ActiveCell.Offset(0, 3) = "Area/Location"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Minuteman Related" Then
ActiveCell.Offset(0, 3).Value = ""
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Morgue Access" Then
ActiveCell.Offset(0, 3).Value = "Granted"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Mortuary Escort" Then
ActiveCell.Offset(0, 3).Value = "Patient's Name and MM # here"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Officer Recall" Then
ActiveCell.Offset(0, 3).Value = "For Shift Coverage/Give Officer name in Dispo cell"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Patrol Check" Then
ActiveCell.Offset(0, 3) = "Building or Location"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Pkg Enforcement" Then
ActiveCell.Offset(0, 3).Value = "Location "
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Power Loss" Then
ActiveCell.Offset(0, 3).Value = "Area/Location"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Radio Check Offsite" Then
ActiveCell.Offset(0, 3).Value = "Check in from offsite property…"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Radio/Pager Check" Then
ActiveCell.Offset(0, 3).Value = "To All Officers"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Reader and/or Door problems" Then
ActiveCell.Offset(0.3).Value = "Door or Reader number?"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Resource Center" Then
ActiveCell.Offset(0, 3).Value = ""
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Restricted Patient Information/Request" Then
ActiveCell.Offset(0.3).Value = "Patient Name/Room #"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Safety Checklist" Then
ActiveCell.Offset(0, 3).Value = "Room/Section/Area"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Sec Sup Request" Then
ActiveCell.Offset(0, 3).Value = "Type of Request"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "So Lobby Card Only" Then
ActiveCell.Offset(0, 3).Value = ""
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "So Lobby Unlock Mode" Then
ActiveCell.Offset(0, 3).Value = ""
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Status" Then
ActiveCell.Offset(0, 3).Value = "Area/Location"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Student Badge Return" Then
ActiveCell.Offset(0, 3).Value = "School or University…"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Transport" Then
ActiveCell.Offset(0, 3).Value = "example: SJMP to Sister Elizabeth…in this cell"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Unknown Trouble" Then
ActiveCell.Offset(0, 3).Value = "Area/Location"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Visitor Accident" Then
ActiveCell.Offset(0, 3).Value = "Area/Location"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Visitor Control" Then
ActiveCell.Offset(0, 3).Value = "Name of Pat/Room #"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Water Flow" Then
ActiveCell.Offset(0, 3).Value = "Area/Location"
ActiveCell.Offset(0, 4).Value = "~Break~"
End If
I would like to set up my sheet this way: in column A a date & time; in column B a list, i.e., "15 Minute Break," "911 Call," "BHS Assist," etc, and the other columns along the same row would fill in dependent on the event chosen in column B. I originally set one part of it in a module but I would like to have it as a Selection Change for all the above details. For Example: in column B, 15 Minute Break is chosen, the date & time should automatically show up in column A, with the word "~Break~" filling in on the same row but in another column....
Whatever help I can get will be much appreciated!
Windows 7; Excel 2010....
If ActiveCell = "15 Minute Break" Then
ActiveCell.Offset(0, 3).Value = "15 Minute Break"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "30 Minute Lunch" Then
ActiveCell.Offset(0, 3).Value = "30 Minute Lunch"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "911 Call" Then
ActiveCell.Offset(0, 3).Value = "Type of problem…."
ActiveCell.Offset(0, 4).Value = "Have they called 911 or are you referring them to call 911…"
ElseIf ActiveCell = "911_Paramedics" Then
ActiveCell.Offset(0, 3).Value = "From Suite number or deparment…"
ActiveCell.Offset(0, 4).Value = "If known, determine the type of distress or problem….."
ElseIf ActiveCell = "Badging/Denise" Then
ActiveCell.Offset(0, 3).Value = "Exchge/New/Request?"
ActiveCell.Offset(0, 4).Value = "If Denise is not here, did you help them by giving them the appropriate form to fill out and/or telling them her hours of operations on Tues & Thurs"
ElseIf ActiveCell = "BHS Assist" Then
ActiveCell.Offset(0, 3).Value = "For Medicate/State Specific Reason?"
ActiveCell.Offset(0, 4).Value = "If known, level 1 or 2..what room number?"
ElseIf ActiveCell = "BHS Patient Escort" Then
ActiveCell.Offset(0, 3).Value = "From BHS to >"
ActiveCell.Offset(0, 4).Value = "Name of Destination here…"
ElseIf ActiveCell = "Caller" Then
ActiveCell.Offset(0, 3).Value = "General, not associated with other events"
ActiveCell.Offset(0, 4).Value = "Notes, if any, to jotted down for the nature of the call; once the nature of the event is known, enter the event in column B in a new row…"
ElseIf ActiveCell = "Cash Escort Café" Then
ActiveCell.Offset(0, 3) = "Location From and To"
ActiveCell.Offset(0, 4).Value = ""
ElseIf ActiveCell = "Cash Escort Foundation" Then
ActiveCell.Offset(0, 3).Value = "Location From/To"
ActiveCell.Offset(0, 4).Value = "Special detail from 505 building, SJMP, and St Joseph Hospital finance"
ElseIf ActiveCell = "Code Blue" Then
ActiveCell.Offset(0, 3).Value = "Location/Room #"
ActiveCell.Offset(0, 4).Value = "Officer should be sent up for crowd control problems, if any…"
ElseIf ActiveCell = "Code Gray" Then
ActiveCell.Offset(0, 3).Value = "Room/Section/Area"
ActiveCell.Offset(0, 4).Value = "After original Code Gray event, all other events in relation to this should fall under Status or other type of events"
ElseIf ActiveCell = "Code Pink" Then
ActiveCell.Offset(0, 3).Value = "Baby / Tag? Is it an Exit or Tamper Alarm"
ActiveCell.Offset(0, 4).Value = "Check Hugs computer for a location of last exciter"
ElseIf ActiveCell = "Code Red" Then
ActiveCell.Offset(0, 3).Value = "Area/Location"
ActiveCell.Offset(0, 4).Value = "Device #....smoke or flame detector…was the fire department called off? Yes or No"
ElseIf ActiveCell = "Code White" Then
ActiveCell.Offset(0, 3).Value = "Location/Room #"
ActiveCell.Offset(0, 4).Value = "Officer should be sent up for crowd control problems, if any…"
ElseIf ActiveCell = "Delivery Gate" Then
ActiveCell.Offset(0, 3).Value = "Company Name"
ActiveCell.Offset(0, 4).Value = "Granted Access to Loading Dock"
ElseIf ActiveCell = "Disorderly Patient" Then
ActiveCell.Offset(0, 3).Value = ""
ActiveCell.Offset(0, 4).Value = ""
ElseIf ActiveCell = "Disorderly Person" Then
ActiveCell.Offset(0, 3).Value = "Building/Room, Etc"
ActiveCell.Offset(0, 4).Value = "After original Disorderly Person event, all other events in relation to this should fall under Status or other type of events"
ElseIf ActiveCell = "Elevator Trouble" Then
ActiveCell.Offset(0, 3).Value = "Telephone/Persons Stuck"
ActiveCell.Offset(0, 4).Value = "if unknown location, correspond Elevator number with map in the control center…"
ElseIf ActiveCell = "ER Patient Escort" Then
ActiveCell.Offset(0, 3).Value = "From ER Room # to >"
ActiveCell.Offset(0, 4).Value = "Name of Location"
ElseIf ActiveCell = "Fire System Test" Then
ActiveCell.Offset(0, 3).Value = "For what facility?"
ActiveCell.Offset(0, 4).Value = ""
ElseIf ActiveCell = "Graffiti" Then
ActiveCell.Offset(0, 3).Value = "What Area or Location?"
ActiveCell.Offset(0, 4).Value = "A report should be generated if it could not be sufficiently removed or specify who cleaned it up…"
ElseIf ActiveCell = "Henry Brothers Issue" Then
ActiveCell.Offset(0, 3).Value = "Door/Reader #"
ActiveCell.Offset(0, 4).Value = "this was called into HBE for a work order; if it was referred back to SJH, specify why here…."
ElseIf ActiveCell = "House Supervisor" Then
ActiveCell.Offset(0, 3).Value = "Problem/Concern"
ActiveCell.Offset(0, 4).Value = ""
ElseIf ActiveCell = "Hugs Comp Down" Then
ActiveCell.Offset(0, 3).Value = "Which One?"
ActiveCell.Offset(0, 4).Value = ""
ElseIf ActiveCell = "Hugs System" Then
ActiveCell.Offset(0, 3).Value = "Discharge/Loose Tag?"
ActiveCell.Offset(0, 4).Value = ""
ElseIf ActiveCell = "Jumpstart" Then
ActiveCell.Offset(0, 3).Value = "Area/Location"
ActiveCell.Offset(0, 4).Value = "who handled the call, either Security or Minuteman?"
ElseIf ActiveCell = "Jumpstart Charger" Then
ActiveCell.Offset(0, 3).Value = "Minuteman Employee"
ActiveCell.Offset(0, 4).Value = "specify whether they are taking the charger out or bringing it back in…"
ElseIf ActiveCell = "Key Assist/Unlock" Then
ActiveCell.Offset(0, 3).Value = "Type of Office/Room"
ActiveCell.Offset(0, 4).Value = "specify whether they are taking the charger out or bringing it back in…"
ElseIf ActiveCell = "Loud Noise" Then
ActiveCell.Offset(0, 3).Value = "Where? Who?"
ActiveCell.Offset(0, 4).Value = "specify whether they are taking the charger out or bringing it back in…"
ElseIf ActiveCell = "Mail Delivery" Then
ActiveCell.Offset(0, 3).Value = ""
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Medicate Patient" Then
ActiveCell.Offset(0, 3) = "Area/Location"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Minuteman Related" Then
ActiveCell.Offset(0, 3).Value = ""
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Morgue Access" Then
ActiveCell.Offset(0, 3).Value = "Granted"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Mortuary Escort" Then
ActiveCell.Offset(0, 3).Value = "Patient's Name and MM # here"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Officer Recall" Then
ActiveCell.Offset(0, 3).Value = "For Shift Coverage/Give Officer name in Dispo cell"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Patrol Check" Then
ActiveCell.Offset(0, 3) = "Building or Location"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Pkg Enforcement" Then
ActiveCell.Offset(0, 3).Value = "Location "
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Power Loss" Then
ActiveCell.Offset(0, 3).Value = "Area/Location"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Radio Check Offsite" Then
ActiveCell.Offset(0, 3).Value = "Check in from offsite property…"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Radio/Pager Check" Then
ActiveCell.Offset(0, 3).Value = "To All Officers"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Reader and/or Door problems" Then
ActiveCell.Offset(0.3).Value = "Door or Reader number?"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Resource Center" Then
ActiveCell.Offset(0, 3).Value = ""
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Restricted Patient Information/Request" Then
ActiveCell.Offset(0.3).Value = "Patient Name/Room #"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Safety Checklist" Then
ActiveCell.Offset(0, 3).Value = "Room/Section/Area"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Sec Sup Request" Then
ActiveCell.Offset(0, 3).Value = "Type of Request"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "So Lobby Card Only" Then
ActiveCell.Offset(0, 3).Value = ""
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "So Lobby Unlock Mode" Then
ActiveCell.Offset(0, 3).Value = ""
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Status" Then
ActiveCell.Offset(0, 3).Value = "Area/Location"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Student Badge Return" Then
ActiveCell.Offset(0, 3).Value = "School or University…"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Transport" Then
ActiveCell.Offset(0, 3).Value = "example: SJMP to Sister Elizabeth…in this cell"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Unknown Trouble" Then
ActiveCell.Offset(0, 3).Value = "Area/Location"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Visitor Accident" Then
ActiveCell.Offset(0, 3).Value = "Area/Location"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Visitor Control" Then
ActiveCell.Offset(0, 3).Value = "Name of Pat/Room #"
ActiveCell.Offset(0, 4).Value = "~Break~"
ElseIf ActiveCell = "Water Flow" Then
ActiveCell.Offset(0, 3).Value = "Area/Location"
ActiveCell.Offset(0, 4).Value = "~Break~"
End If