UserForm TextBox.Value type mismatch with variable

gravanoc

Active Member
Joined
Oct 20, 2015
Messages
346
Office Version
  1. 365
Platform
  1. Windows
  2. 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.

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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
One more thing that I should mention. When I hit the Stop button in the VBE, it is causing Excel to crash. Any ideas why?
 
Upvote 0
Try
Code:
boxValue = TextBox1.Value
c = jobRange.Rows.Count


If IsNumeric(Application.Match(boxValue,jobRange.Columns(1),False)) Then
    TextBox1.Text = ""
End If

Application.EnableEvents = True
NoEvents = False
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top