Hi
I have created a textbox change event that calls a sub. My sheet will end up with many textboxes so I'd like to simplify this so that when any text box changes it calls the sub so that I don't have to add the code to each textbox change event. I've found lots of posts similar to this when working with userforms and textboxes that have simple naming conventions but haven't managed to make anything work for my scenario. My textboxes have varied names with no pattern.
Can anyone help?
Private Sub K22_Change()
Call UpdateBackground(ctl:=Me.K22)
End Sub
Private Sub UpdateBackground(ctl As Object)
Dim dLevel_1 As Double
Dim dLevel_2 As Double
dLevel_1 = Sheet1.Range("E29").Value 'Orange
dLevel_2 = Sheet1.Range("E28").Value 'Green
Select Case ctl.Value
Case Is = 0
ctl.BackColor = &HFF8080 'Purple ERROR
ctl.ForeColor = &HFF8080
Case Is < dLevel_1
ctl.BackColor = &H8080FF 'Red
ctl.ForeColor = &H8080FF
Case Is > dLevel_2
ctl.BackColor = &H80FF80 'Green
ctl.ForeColor = &H80FF80
Case Is < dLevel_2
ctl.BackColor = &H80C0FF 'Orange
ctl.ForeColor = &H80C0FF
End Select
End Sub
Thanks very much,
Jo
Bookmarks