Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Tidy up VBA code.

  1. #1
    New Member
    Join Date
    Jun 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Tidy up VBA code.

    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)

    Code:
    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
    Last edited by MarkOW; Mar 23rd, 2019 at 05:54 AM. Reason: Extra Info

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,856
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Tidy up VBA code.

    like this maybe
    Code:
    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
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    New Member
    Join Date
    Jun 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Tidy up VBA code.

    Hi, and thanks.

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

    Thanks again

  4. #4
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,856
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Tidy up VBA code.

    Code:
    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
        
        Next n
    
        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
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  5. #5
    New Member
    Join Date
    Jun 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Tidy up VBA code.

    Hi again ,

    I tried that first but end up with error

    Next without For.

    Mark

  6. #6
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,856
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Tidy up VBA code.

    a different tweak
    Code:
    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
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  7. #7
    New Member
    Join Date
    Jun 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Tidy up VBA code.

    Hi Again

    Another error I'm afraid.

    Else without If

  8. #8
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,856
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Tidy up VBA code.

    currently out of ideas then, it will be something on that theme
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  9. #9
    New Member
    Join Date
    Jun 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Tidy up VBA code.

    Thanks for your help.

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

    Mark

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,306
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Tidy up VBA code.

    How about
    Code:
    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •