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

 

[kelly mort]

I still need help with this please

 

[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

So it is like this: stop when the ID in Reg1 equals the data in cell B3 because the IDs start from B3

 

[JLGWhiz]

So it is like this: stop when the ID in Reg1 equals the data in cell B3 because the IDs start from B3

I think you are going to have to put some images of your worksheet, userform, etc. where we can see them and explain where you are running the code from. The feedback is not sufficient to give good adivice.

 

[kelly mort]

I dont know how to add the image. How do i do it

 

[dmt32]

No it did not work. I think what you have here is a rang of nimbers. I want only the numbers i showed above to be exited

Then my other suggestion of creating a table where you can add numbers to be excluded & get your code to check that could do what you want?

Untested but something like following maybe:

     Dim rng As Range, m As Variant    
     Set rng = Sheets("Sheet1").Columns(1)
    m = Application.Match(Me.Reg1.Value, rng, False)
    If Not IsError(m) Then Exit Sub

You place all excluded numbers in column 1 of worksheet (change name as required) & if match found, exits your sub.

Dave

 

[kelly mort]

Okay i think this one has solved the problem. Wow thanks for the time , energy and patience with me. I am most grateful

 

[dmt32]

Okay i think this one has solved the problem. Wow thanks for the time , energy and patience with me. I am most grateful

most welcome - many thanks for feedback very much appreciated.

Dave

 

[kelly mort]

It worked when the sheet named week_01 is selected. But for the other sheets it does not.

Did you say i should place in column 1 of all the sheets or on just one sheet should have all the numbers to be excluded??

 

[dmt32]

It worked when the sheet named week_01 is selected. But for the other sheets it does not.

Did you say i should place in column 1 of all the sheets or on just one sheet should have all the numbers to be excluded??

You have one reference sheet (Sheet1 or whatever you name it) & in Column 1 list all the numbers you want excluded. Entry is case sensitive.

Dave

 

[kelly mort]

Before, i modified it to

     Dim sht As String 
      sht = ComboBox1.Value
     Dim rng As Range, m As Variant    
     Set rng = Sheets(sht).Columns(1)
    m = Application.Match(Me.Reg1.Value, rng, False)
    If Not IsError(m) Then Exit Sub

Then i placed the numbers i column 1 of each sheet and one the week_01 sheet when selected in the combobox it worked fine but the rest did not exit sub.


Now when i have it this way

Dim rng As Range, m As Variant    
     Set rng = Sheets("Sheet2").Columns(1)
    m = Application.Match(Me.Reg1.Value, rng, False)
    If Not IsError(m) Then Exit Sub

And place all numbers to exclude it does not work fine. It always exits sub