Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,564
- Office Version
- 365
- 2016
- Platform
- Windows
Sorry about the title ... I couldn't come up with an appropriate title to explain my issue.
The code I am posting below I have posted before in another thread. Of course it has evolved since then, but I wasn't sure if I should start a new thread fearing a "duplicate post" or if it was OK to start a new thread. The code is the same, the problem I believe to be different. My apologies in advance if I've double posted.
The above code is executed by a worksheet change event when the user changes the value of a cell in a defined range. In testing, lets use K8. (just one of 11 possible cells open for the user to change to trigger this module). These cells have a data validation list of values for the user to select from.
The user changes the value of K8 and this routine is triggered. I assume it is working as I am getting the results I'm expecting. However, if the user deletes the value resulting in the cell being blank, my code (in blue) that I thought would protect my application from that comes back with an error "Type mismatch" with the line in red.
sctarget is publically defined as a range.
sctarget is set at the beginning of my worksheet change module ...
This erronous line doesn't fail when the value is a permitted value (ie from the validation list), only when cell value of the cell that changed is "empty". When I watch the value of sctarget, if usually indicates the value of the cell although it is supposed to be a range so that is why I felt I culd use sctarget this way. But when the cell is empty, it appears I can't use it that way. I use the value of sctarget in other lines of code that have not created problems when the cell value is appropriate.
Anyone able to offer up a solution?
The code I am posting below I have posted before in another thread. Of course it has evolved since then, but I wasn't sure if I should start a new thread fearing a "duplicate post" or if it was OK to start a new thread. The code is the same, the problem I believe to be different. My apologies in advance if I've double posted.
Rich (BB code):
Sub chg_dsup()
Stop
If Not mbevents Then Exit Sub
'MsgBox sctarget.Address
If sctarget = "" Then
MsgBox "Please ensure a value is set."
mbevents = False
Application.Undo
Exit Sub
End If
With ws_form
.Unprotect
br = .Range(sctarget.Address).Row - 7
bc = .Range(sctarget.Address).Column + 16
compval = ws_blocks.Cells(br, bc)
'if a cell identified as having missing info
If .Range(sctarget.Address).Interior.Color = RGB(242, 146, 146) And sctarget <> "" Then
.Range(sctarget.Address).Interior.Color = RGB(221, 235, 247)
ws_blocks.Cells(br - 7, bc) = .Range(sctarget.Address).Value
' user change
Else
If sctarget <> ws_blocks.Cells(br, bc).Value Then
chg = chg + 1
With .Range(sctarget.Address)
.Font.Color = RGB(13, 58, 247)
.Font.Bold = True
End With
Else
chg = chg - 1
With .Range(sctarget.Address)
.Font.Color = vbBlack
.Font.Bold = False
End With
End If
End If
.Protect
End With
Stop
End Sub
The above code is executed by a worksheet change event when the user changes the value of a cell in a defined range. In testing, lets use K8. (just one of 11 possible cells open for the user to change to trigger this module). These cells have a data validation list of values for the user to select from.
The user changes the value of K8 and this routine is triggered. I assume it is working as I am getting the results I'm expecting. However, if the user deletes the value resulting in the cell being blank, my code (in blue) that I thought would protect my application from that comes back with an error "Type mismatch" with the line in red.
sctarget is publically defined as a range.
Code:
public sctarget as range
Code:
Set sctarget = target
This erronous line doesn't fail when the value is a permitted value (ie from the validation list), only when cell value of the cell that changed is "empty". When I watch the value of sctarget, if usually indicates the value of the cell although it is supposed to be a range so that is why I felt I culd use sctarget this way. But when the cell is empty, it appears I can't use it that way. I use the value of sctarget in other lines of code that have not created problems when the cell value is appropriate.
Anyone able to offer up a solution?