exit sub when textbox contains certain item

[kelly mort]

Hi all
I have a textbox called Reg1 on a userform and i want to exit sub when certain numbers or unique IDs are in it and i still click the back button. The numbers are started with texts like wkx1001 , wkx2001, wkx3001 up to wkx52001.

Adding 52 if statements is too long i think. So i have decided to post it here for a simpler approach.
Thanks in advance for reading and willing to help

 

[dmt32]

Suggestion was a build a single table with all excluded numbers that your code checked against.

Issue with Application.Match could be a case sensitivity issue

Try this update using find function:

     Dim rng As Range
    Set rng = Sheets("Sheet2").Columns(1).Find(Me.Reg1.Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If Not rng Is Nothing Then Exit Sub

Dave

 

[kelly mort]

Okay. My power is out at the moment. Will test it when the power is back. Thanks very much

 

[kelly mort]

Suggestion was a build a single table with all excluded numbers that your code checked against.

Issue with Application.Match could be a case sensitivity issue

Try this update using find function:

     Dim rng As Range
    Set rng = Sheets("Sheet2").Columns(1).Find(Me.Reg1.Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If Not rng Is Nothing Then Exit Sub

Dave

Still it is not working.

 

[dmt32]

Still it is not working.

are you able to place copy of your workbook in a dropbox?

Dave

 

[kelly mort]

Suggestion was a build a single table with all excluded numbers that your code checked against.

Issue with Application.Match could be a case sensitivity issue

Try this update using find function:

     Dim rng As Range
    Set rng = Sheets("Sheet2").Columns(1).Find(Me.Reg1.Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If Not rng Is Nothing Then Exit Sub

Dave

Not yet. I dont know how to place the copy there. I am on mobile phone. And when i try it does not work

 

[kelly mort]

I think giving the actual code i have here will help.

Private Sub CmdBackData_Click()
  Dim FindRow As Range, cRow As String, cNum As Integer, x As Integer, sht As String
sht = ComboBox1.Value

On Error Resume Next

cRow = Me.Reg1.Value
Set FindRow = Sheets(sht).Range("B3:B303").Find(What:=cRow, LookIn:=xlValues).Offset(-1, 0)
'I use this to exit the sub but i want it short
If Me.Reg1.Value = "WKX1001" Then Exit Sub
If Me.Reg1.Value = "WKX2001" Then Exit Sub
If Me.Reg1.Value = "WKX3001" Then Exit Sub
If Me.Reg1.Value = "WKX4001" Then Exit Sub
'This goes on up to WKX52001 and makes the code too long so i want i short version
cNum = 11
For x = 1 To cNum
Me.Controls("Reg" & x ).Value = FindRow.Text
Set FindRow = FindRow.Offset(0, 1)

Next

On Error GoTo 0
End Sub

 

[kelly mort]

I need someone to look at this code for me in order to make it short. Thanks

 

[JLGWhiz]

I need someone to look at this code for me in order to make it short. Thanks

You can replace all this

If Me.Reg1.Value = "WKX1001" Then Exit Sub
If Me.Reg1.Value = "WKX2001" Then Exit Sub
If Me.Reg1.Value = "WKX3001" Then Exit Sub
If Me.Reg1.Value = "WKX4001" Then Exit Sub
'This goes on up to WKX52001 and makes the code too long so i want i short version

With this

If CLng(Mid(Reg1.Value, 4)) Mod 1000 <> 1 Then Exit Sub

 

[JLGWhiz]

Sorry, should have been

If CLng(Mid(Reg1.Value, 4)) Mod 1000 = 1 Then Exit Sub

 

[kelly mort]

Hmmmm, it did not move. It always exits sub