gravanoc
Active Member
- Joined
- Oct 20, 2015
- Messages
- 348
- Office Version
- 365
- Platform
- Windows
- Mobile
I've got a simple script that is giving me lots of pain. I am simply trying to validate the number entered into my first TextBox to ensure that there is not a duplicate of a job number for a project. If there is, it sets the value of the field to "". In some variations of my code it would work, but there would be a problem with something else like Vlookup causing a non-skippable error.
Anyway, here's the code in its current form. Any help determining the cause of this spaz out is welcome. The type mismatch occurs when comparing boxValue to jobRange.Offset(i, 0).Value.
Anyway, here's the code in its current form. Any help determining the cause of this spaz out is welcome. The type mismatch occurs when comparing boxValue to jobRange.Offset(i, 0).Value.
Code:
Private Sub TextBox1_AfterUpdate()
Dim sh As Worksheet
Dim lRange As Range
Dim uRange As Range
Dim jobRange As Range
Dim boxValue As Long
Dim i As Long
Dim c As Long
If NoEvents Then Exit Sub
NoEvents = True
Application.EnableEvents = False
Set sh = Worksheets("Job Settings")
If sh.Range("B3").Value <> "" Then
Set lRange = sh.Range("B2")
Set uRange = sh.Range("B2").End(xlDown)
Set jobRange = sh.Range(lRange, uRange)
Else
Set jobRange = sh.Range("B2")
End If
boxValue = TextBox1.Value
c = jobRange.Rows.Count
For i = 0 To c
If jobRange.Offset(i, 0).Value = boxValue Then
TextBox1.Text = ""
Exit For
End If
Next
Application.EnableEvents = True
NoEvents = False
End Sub