Lobo1943
09-06-2017, 02:35 PM
Hello,
This is the first VBS script that I have tied to write and it seem to be not working. What my little project is to write a script that will automatically lock a cell after the letter x is type into the cell. I am using the x as a place holder for a appointment. After the customer and I agree on a date and time for the appointment then I would enter the x into the place holder and schedule the appointment using a different scheduling package. Excel does not communicate with the scheduling software. My little grid chart allows me, at a glance, to see where open dates and time are without searching through the scheduler. In addition, There are times when a customer will call and cancel the appointment, and then I would have to edit the grid cell to remove the x and unlock the cell so it would be available for the next customer.
I should mention that there are two of us using the excel grid and sometimes we walk on the other's place marker by inadvertently forgetting where the cursor is and accidently either erasing the x from the cell or maybe placing an x into the cell without realizing that the cursor is occupying a cell. When this happens either of us would assume the place the x would indicate that the schedule slot is taken when it's not.
This is my script that I have tried to writ that would allow the cell to be locked after the x is entered. I want a warning that will say "Are you sure Y or N.
Private Sub Worksheet_change (ByVal Target AS Range)
Dim cl As Range
ActiveSheet.unprotected Password:="bv124"
For Each cl In Target
If cl.Value <> " " Then
Check =MsgBox("Is this entry correct? This cell cannot be edited after enteringthe letter X or x.", vbyesorno, "Cell Lock Notification")
If Check =vbx Then
cl.Locked= True
Else
cl.Value =" "
End If
End If
Next cl
Activeworksheet.Protect Password:="bv124"
End Sub
This is the first VBS script that I have tied to write and it seem to be not working. What my little project is to write a script that will automatically lock a cell after the letter x is type into the cell. I am using the x as a place holder for a appointment. After the customer and I agree on a date and time for the appointment then I would enter the x into the place holder and schedule the appointment using a different scheduling package. Excel does not communicate with the scheduling software. My little grid chart allows me, at a glance, to see where open dates and time are without searching through the scheduler. In addition, There are times when a customer will call and cancel the appointment, and then I would have to edit the grid cell to remove the x and unlock the cell so it would be available for the next customer.
I should mention that there are two of us using the excel grid and sometimes we walk on the other's place marker by inadvertently forgetting where the cursor is and accidently either erasing the x from the cell or maybe placing an x into the cell without realizing that the cursor is occupying a cell. When this happens either of us would assume the place the x would indicate that the schedule slot is taken when it's not.
This is my script that I have tried to writ that would allow the cell to be locked after the x is entered. I want a warning that will say "Are you sure Y or N.
Private Sub Worksheet_change (ByVal Target AS Range)
Dim cl As Range
ActiveSheet.unprotected Password:="bv124"
For Each cl In Target
If cl.Value <> " " Then
Check =MsgBox("Is this entry correct? This cell cannot be edited after enteringthe letter X or x.", vbyesorno, "Cell Lock Notification")
If Check =vbx Then
cl.Locked= True
Else
cl.Value =" "
End If
End If
Next cl
Activeworksheet.Protect Password:="bv124"
End Sub