Type Mismatch Error Populating a userform

I'm a super rookie at excel vba coding, about a week in. LOL. But i'm trying to self teach. I'm currently having a problem with populating a user form. I'm sure the answer is simple but I've been staring at it for 2 days and finally had the courage to ask for help.

Problem: when I put in a ticket # and press search, i want the userform to populate. But when I try to run it, a type mismatch occurs. I've tried setting the text box's as integer or string and change the source in the worksheet to the respected type but then a invalid qualifier occurs.

whats funny is I tried to simplify it, to test the code and it works so I'm not sure what I'm doing wrong.

PHP Code: 

Option Explicit
Private Sub cbSearch_Click()

Dim vResult

'not sure if these are needed or not

'Dim TextBox2 As Integer

'Dim TextBox9 As Integer

'Dim TextBox4 As String

vResult = Application.CountIf(Worksheets("Journal").Range("A2:A10000"), Me.TextBox1.Value)
If vResult > 0 Then

 'Error occurs here. "Could not set the Value properly. type mismatch. But when I try to set it(TextBox2, TextBox9, etc..)
 'as a string or Integer it comes back as invalid qualifier
TextBox2.Value = Application.VLookup(Me.TextBox1.Value, Sheets("Journal").Range("A:BA"), 14, False)
TextBox9.Value = Application.VLookup(Me.TextBox1.Value, Sheets("Journal").Range("A:BA"), 15, False)
TextBox4.Value = Application.VLookup(Me.TextBox1.Value, Sheets("Journal").Range("A:BA"), 5, False)

'I also want the data to be imported to the userform to check the checkbox if it applies. I used 1 for true and 0 for false
CheckBox1.Value = Application.VLookup(Me.TextBox1.Value, Sheets("Journal").Range("A:BA"), 30, False)
CheckBox2.Value = Application.VLookup(Me.TextBox1.Value, Sheets("Journal").Range("A:BA"), 31, False)

Else

MsgBox "No match found. Please try again."
End If

End Sub 


Wow! soooo much red. haha it doesn't look like that in VBA Editor :/

You are making your code to complicated.

You need to stop work for now so I can show you how to do this in a much simpler and clearer way.

Unfortunately I cannot help you at this time because you have not inserted code tags.

I have your code ready to post.


Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

This should get you in the right direction.

Please Login or Register  to view this content.

Ok first rename your controls so that the names reflect the column that stores its data.

So textboxes 5, 14 and 15 and checkboxes 31 and 32

Trust me this is a powerful solution and will work for countless controls without change.

All you need to change is the line that lists your columns ( Controls).

Please Login or Register  to view this content.

Thanks to everybody for their assistance and input.

mehmetcik - Your absolutely right about naming the objects. This was a sample of a much larger project. At first I thought the error was with my names and a misspelling error. That's why I started again with the original names. I wanted to eliminate any stupid errors before I started asking for help. Thank you for your assistance. Its greatly appreciated.

BTW, I tried the code but cant seem to get it to work. I've replaced"TextBox" with the respected object names & There is the appropriate number of columns as objects. The error works but the values don't show in the textbox's

Logit - Not exactly what I wanted but I will be needing this code in the future. Thank you so much.


I did find an alternate solution after some studying at work. lol! Don't tell the boss.

Please Login or Register  to view this content.

Please let me know if this is an appropriate coding. I'm a rookie so i'm not sure.

I am attaching some samples for you to look at.

The sheet "Load Controls" is the Userform that i would like you to see.

There are other techniques that you can use to speed up excel.

Create a new thread asking how to speed up macros and PM me the link.

I will then post you my macro for speeding up macros.