Tidy up VBA code.

[MarkOW]

Hi I have VBA code that I have written.......

It works but looks untidy.

Can someone offer advice on how to tidy it up with a loop so I can extend the ranges that are compared? (Extend to B3:30 & C3:C30)

Private Sub CommandButton1_Click()
'Security Pin
            


    If Worksheets("Datasheet").Range("C3").Value = CStr(Me.PIN2.Text) And Worksheets("Datasheet").Range("B3").Value = CStr(Me.ComboBox1.Value) Then
    GoTo ByPass


    
    ElseIf Worksheets("Datasheet").Range("C4").Value = CStr(Me.PIN2.Text) And Worksheets("Datasheet").Range("B4").Value = CStr(Me.ComboBox1.Value) Then
    GoTo ByPass


    
    ElseIf Worksheets("Datasheet").Range("C5").Value = CStr(Me.PIN2.Text) And Worksheets("Datasheet").Range("B5").Value = CStr(Me.ComboBox1.Value) Then
    GoTo ByPass


  
    ElseIf Worksheets("Datasheet").Range("C6").Value = CStr(Me.PIN2.Text) And Worksheets("Datasheet").Range("B6").Value = CStr(Me.ComboBox1.Value) Then
    GoTo ByPass


    
    ElseIf Worksheets("Datasheet").Range("C7").Value = CStr(Me.PIN2.Text) And Worksheets("Datasheet").Range("B7").Value = CStr(Me.ComboBox1.Value) Then
    GoTo ByPass


    
    ElseIf Worksheets("Datasheet").Range("C8").Value = CStr(Me.PIN2.Text) And Worksheets("Datasheet").Range("B8").Value = CStr(Me.ComboBox1.Value) Then
    GoTo ByPass


    ElseIf Worksheets("Datasheet").Range("C9").Value = CStr(Me.PIN2.Text) And Worksheets("Datasheet").Range("B9").Value = CStr(Me.ComboBox1.Value) Then
    GoTo ByPass


    
    ElseIf Worksheets("Datasheet").Range("C10").Value = CStr(Me.PIN2.Text) And Worksheets("Datasheet").Range("B10").Value = CStr(Me.ComboBox1.Value) Then
    GoTo ByPass


    
    ElseIf Worksheets("Datasheet").Range("C11").Value = CStr(Me.PIN2.Text) And Worksheets("Datasheet").Range("B11").Value = CStr(Me.ComboBox1.Value) Then
    GoTo ByPass


    
    ElseIf Worksheets("Datasheet").Range("C12").Value = CStr(Me.PIN2.Text) And Worksheets("Datasheet").Range("B12").Value = CStr(Me.ComboBox1.Value) Then
    GoTo ByPass
 
    
    ElseIf Worksheets("Datasheet").Range("C13").Value = CStr(Me.PIN2.Text) And Worksheets("Datasheet").Range("B13").Value = CStr(Me.ComboBox1.Value) Then
    GoTo ByPass


    
    ElseIf Worksheets("Datasheet").Range("C14").Value = CStr(Me.PIN2.Text) And Worksheets("Datasheet").Range("B14").Value = CStr(Me.ComboBox1.Value) Then
    GoTo ByPass
    
    ElseIf Worksheets("Datasheet").Range("C15").Value = CStr(Me.PIN2.Text) And Worksheets("Datasheet").Range("B15").Value = CStr(Me.ComboBox1.Value) Then
    GoTo ByPass
    Else
    
    
     MsgBox Prompt:="Incorrect PIN", Buttons:=vbCritical
     
     Me.PIN2.Value = ""


     Unload Me
     GoTo ReTry
     
ByPass:


     End If
     
    
    Call Signnow


ReTry:


    End Sub

thanks again

Mark

Office 365 Windows 10

 

[mole999]

like this maybe

Private Sub CommandButton1_Click()
'Security Pin
  Dim n As interger

For n = 3 To 15 Step 1

    If Worksheets("Datasheet").Range("C" & n).Value = CStr(Me.PIN2.Text) And Worksheets("Datasheet").Range("B" & n).Value = CStr(Me.ComboBox1.Value) Then
    GoTo ByPass

    Else
    
    
     MsgBox Prompt:="Incorrect PIN", Buttons:=vbCritical
     
     Me.PIN2.Value = ""


     Unload Me
     GoTo ReTry
     
ByPass:


     End If
     
    
    Call Signnow


ReTry:


    End Sub

 

[MarkOW]

Hi, and thanks.

I am getting the error for without next. Where does the next go?

Thanks again

 

[mole999]

Private Sub CommandButton1_Click()
'Security Pin
  Dim n As interger

For n = 3 To 15 Step 1

    If Worksheets("Datasheet").Range("C" & n).Value = CStr(Me.PIN2.Text) And Worksheets("Datasheet").Range("B" & n).Value = CStr(Me.ComboBox1.Value) Then
    GoTo ByPass
    
[COLOR=#ff0000]    Next n
[/COLOR]
    Else
    
    
     MsgBox Prompt:="Incorrect PIN", Buttons:=vbCritical
     
     Me.PIN2.Value = ""


     Unload Me
     GoTo ReTry
     
ByPass:


     End If
     
    
    Call Signnow


ReTry:


    End Sub

thats what happens when you can't test it , item in red i think

 

[MarkOW]

Hi again ,

I tried that first but end up with error

Next without For.

Mark

 

[mole999]

a different tweak

Private Sub CommandButton1_Click()
'Security Pin
  Dim n As Integer

    If Worksheets("Datasheet").Range("C3").Value = CStr(Me.PIN2.Text) And Worksheets("Datasheet").Range("B3").Value = CStr(Me.ComboBox1.Value) Then
    GoTo ByPass
    
For n = 4 To 15 Step 1
     
     ElseIf Worksheets("Datasheet").Range("C" & n).Value = CStr(Me.PIN2.Text) And Worksheets("Datasheet").Range("B" & n).Value = CStr(Me.ComboBox1.Value) Then
    GoTo ByPass
    
    Next n

    Else
    
     MsgBox Prompt:="Incorrect PIN", Buttons:=vbCritical
     
     Me.PIN2.Value = ""

     Unload Me
     GoTo ReTry
ByPass:
     End If
    Call Signnow
ReTry:
    End Sub

 

[MarkOW]

Hi Again

Another error I'm afraid.

Else without If

 

[mole999]

currently out of ideas then, it will be something on that theme

 

[MarkOW]

Thanks for your help.

I have tried a lot of variations but hit a wall!

Mark

 

[Fluff]

How about

Private Sub CommandButton1_Click()
'Security Pin
   Dim i As Long
   Dim Flg As Boolean
   
   With Worksheets("Datasheet")
      For i = 3 To 15
         If .Range("C" & i).Value = CStr(Me.PIN2.Text) And .Range("B" & i).Value = CStr(Me.ComboBox1.Value) Then
            Flg = True
            Exit For
         End If
      Next i
   End With
   If Flg = False Then
      MsgBox prompt:="Incorrect PIN", Buttons:=vbCritical
      Me.PIN2.Value = ""
      Unload Me
      Exit Sub
   End If
      
   Call Signnow
End Sub