Mistake in Code??

charllie

Well-known Member
Joined
Apr 6, 2005
Messages
986
Hi Folks,

Could someone tell me where i am going wrong please with the following code.

Code:
Private Sub TextBox4_Change()
    If Workbooks("Beck.xls").Worksheets("Time Sheet Compile").Range("BL6") = "HANDOVER" Then
        If Len(Me.TextBox4.Value) > 5 Then   'only fire off if the code is 6 characters long or more
            Dim ctl As MSForms.Control
            Dim i As Integer
                With Workbooks("Team Leader.xls").Sheets("QC Check Sheet Archive")
                    Private Sub TextBox4_Change()
    If Workbooks("Beck.xls").Worksheets("Time Sheet Compile").Range("BL6") = "HANDOVER" Then
        If Len(Me.TextBox4.Value) > 5 Then   'only fire off if the code is 6 characters long or more
            Dim ctl As MSForms.Control
            Dim i As Integer
                With Workbooks("Team Leader.xls").Sheets("QC Check Sheet Archive")
                    For i = 5 To 200
                        If Me.TextBox4.Text = .Cells(i, "C") Then
                            Exit Sub
                        Else
                            MsgBox "HELP"
                            Exit Sub
                        End If
                    Next
                End With
        End If
    End If
End Sub

With this part of the code:
Code:
If Me.TextBox4.Text = .Cells(i, "C") Then
                            Exit Sub
                        Else
                            MsgBox "HELP"
                            Exit Sub
                        End If



I am trying to say TextBox4.text is the same as what is in rows C then do nothing.
But if it is does not match what is in row c then show msgbox"HELP".


The error i am getting is no matter what i put in the textbox4 the msgbox"HELP" always shows.

Thanks
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hi charllie,

I noticed that the code, as is, will only check cell C5, & wont loop to check C6:C200.
 
Upvote 0
charllie said:
Thanks, how do i get it to check all of row C from 5 Up?
your code reads "exit sub" when the item is found
seems like you could use "FIND" instead, but perhaps you have a reason to use this syntax ?
Code:
                    For i = 5 To 200 
                        If Me.TextBox4.Text = .Cells(i, "C") Then 
                            Exit Sub 
                        Else 
                            MsgBox "HELP" 
                            Exit Sub 
                        End If 
                    Next

best regards,
Erik
 
Upvote 0
just now I saw you had also Exit Sub in the "else"-part
Code:
                With Workbooks("Team Leader.xls").Sheets("QC Check Sheet Archive") 
                    For i = 5 To 200 
                        If Me.TextBox4.Text = .Cells(i, "C") Then 
                            Exit Sub 
                        Else 
                            MsgBox "HELP" 
                            Exit Sub 
                        End If 
                    Next 
                End With
of course this wouldn't loop !!
but let's use FIND
what do you want to do when an the Me.textBox4.text is found ?
continue ?
OR
exit the FIND-loop ?
is there only one occurence or can there be more ?
 
Upvote 0
Hi Erik,

Thanks for your reply

what do you want to do when an the Me.textBox4.text is found ?
continue ?
OR
exit the FIND-loop
?

The answer to this is to exit the find loop and let the op continue what they were doing.

is there only one occurence or can there be more ?

There can be more than one occurrance but i am only interested in the last one that was entered.

Thanks

Charllie
 
Upvote 0

Forum statistics

Threads
1,226,727
Messages
6,192,686
Members
453,746
Latest member
Bruce at Pubdarts

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