Textbox wont allow set focus on it after trying to clear textboxes

[ipbr21054]

When i try to clear the values on my userform i see the following error message
Type Mismatch,i debug & see the line of code in yellow highlighted

The Textbox in question that gives me the issue is called CustomerID

    Dim id As Integer, rowcount As Integer, foundcell As Range
    
    id = CustomerID.Value
    
    rowcount = Sheets("G INCOME").Cells(Rows.Count, 13).End(xlUp).Row ' THIS IS COLUMN NUMBER WHERE EMP ID LOCATED
    
    With Worksheets("G INCOME").Range("M1:M" & rowcount) ' THIS IS CELL REFERENCE OF WHERE THE TEXT EMP ID IS LOCATED
        Set foundcell = .Find(what:=id, LookIn:=xlValues)
        
        If Not foundcell Is Nothing Then
            TextBox1.Value = .Cells(foundcell.Row, 2)
            TextBox2.Value = .Cells(foundcell.Row, 3)
            TextBox3.Value = .Cells(foundcell.Row, 4)
            TextBox4.Value = .Cells(foundcell.Row, 5)
            TextBox5.Value = .Cells(foundcell.Row, 6)
        
         Else
            TextBox1.Value = ""
            TextBox2.Value = ""
            TextBox3.Value = ""
            TextBox4.Value = ""
            TextBox5.Value = ""
            
        End If
        
    End With
    
End Sub

This is how i clear & try to set focus in the textboxes.

Private Sub ClearValues_Click()
CustomerID = ""
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
CustomerID.SetFocus


End Sub

 

[Micron]

There is no yellow highlight. The message means something cannot accept the value you're trying to set it to. Try Null or 0 to see if that gets rid of the error.

 

[ipbr21054]

Well I’m not sure then.
The Textbox is called CustomerID

My code shows to set focus to CustomerID

It doesn’t set focus & the line of code in yellow is id = CustomerID.Value

So not sure what to try or do

 

[Micron]

I'm more familiar with Access vba, but that error message is not specific to Access. All I can think of is that the cell or table where you are trying to set that value ("") is formatted as a number. Did you try what I suggested?

 

[ipbr21054]

Its on my userform

Not sure how to set it to what you advised

 

[Micron]

As in
CustomerID = 0
or
CustomerID = Null

 

[ipbr21054]

Im sorry but i have ;earning issue so sometiomes when you give me the answer im then struggling to see where it needs to be be placed.

At present no matter what i try this line of code is shown in yellow all the time.

Private Sub CustomerID_AfterUpdate()

id = CustomerID.Value

 

[Micron]

OK, next time try using code tags (VBA button on forum posting toolbar) and not Rich BB tags. Then comment where the line of code fails, like this

    Dim id As Integer, rowcount As Integer, foundcell As Range
    id = CustomerID.Value '<<< error is raised here
    rowcount = Sheets("G INCOME").Cells(Rows.Count, 13).End(xlUp).Row ' THIS IS COLUMN NUMBER WHERE EMP ID LOCATED
   
    With Worksheets("G INCOME").Range("M1:M" & rowcount) ' THIS IS CELL REFERENCE OF WHERE THE TEXT EMP ID IS LOCATED
        Set foundcell = .Find(what:=id, LookIn:=xlValues)

Note how the comment is green and easy to spot and how the comment clearly id's the problem line. Up to now, I thought it was somewhere else.
OK, you have declared id as an integer so it cannot be "" or Null. This means if the userform control does not contain a value that can be interpreted as an integer the code will fail. This probably also includes numbers with decimals (e.g. 2.5) because integers cannot be fractions. Try declare it as a variant:

Dim id As Variant

 

[ipbr21054]

So as a poor workaround which works but you have a visible close then open form i have this in place.

Private Sub ClearValues_Click()
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
Unload SAMECUSTOMER
SAMECUSTOMER.Show


End Sub

The above i tried
CustomerID = ""
CustomerID = null & also Null
Neither worked & gave me that run time error.

Trying CustomerID = 0 also didnt work as the code thought it was an ID number from my sheet & threw up another error.

 

[ipbr21054]

OK, next time try using code tags (VBA button on forum posting toolbar) and not Rich BB tags. Then comment where the line of code fails, like this

    Dim id As Integer, rowcount As Integer, foundcell As Range
    id = CustomerID.Value '<<< error is raised here
    rowcount = Sheets("G INCOME").Cells(Rows.Count, 13).End(xlUp).Row ' THIS IS COLUMN NUMBER WHERE EMP ID LOCATED
  
    With Worksheets("G INCOME").Range("M1:M" & rowcount) ' THIS IS CELL REFERENCE OF WHERE THE TEXT EMP ID IS LOCATED
        Set foundcell = .Find(what:=id, LookIn:=xlValues)

Note how the comment is green and easy to spot and how the comment clearly id's the problem line. Up to now, I thought it was somewhere else.
OK, you have declared id as an integer so it cannot be "" or Null. This means if the userform control does not contain a value that can be interpreted as an integer the code will fail. This probably also includes numbers with decimals (e.g. 2.5) because integers cannot be fractions. Try declare it as a variant:

Dim id As Variant

I mentioned this before & got different advice on how to post ???????
So im lost as 1 person said this & another said that ?