Procedure after "exit sub" not running

D

Dungadin

New Member
Joined
Mar 16, 2016
Messages
7
Trying to create a parking lot database that is editable through a multipage user form. Since 1 parking spot can have up to 3 different vehicles parked in it, I'm trying to figure out how to be able to search multiple columns at once.

This is the original layout. i.e. I need to be able to search for a license plate or fragment of a plate across the entire DB. But everything I've researched says the search will find the first instance, then isolate the search to that column.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Lot[/TD]
[TD]Space[/TD]
[TD]Sched[/TD]
[TD]Last[/TD]
[TD]First[/TD]
[TD]ID[/TD]
[TD]Grade[/TD]
[TD]V1 Year[/TD]
[TD]V1 Color[/TD]
[TD]V1 Make[/TD]
[TD]V1 Model[/TD]
[TD]V1 Lic. Plate[/TD]
[TD]V2 Year[/TD]
[TD]V2 Color[/TD]
[TD]V2 Make[/TD]
[TD]V2 Model[/TD]
[TD]V2 Lic. Plate[/TD]
[TD]V3 Year[/TD]
[TD]V3 Color[/TD]
[TD]V3 Make[/TD]
[TD]V3 Model[/TD]
[TD]V3 Lic. Plate[/TD]
[/TR]
[TR]
[TD]Gold[/TD]
[TD]440[/TD]
[TD][/TD]
[TD]Doe[/TD]
[TD]John[/TD]
[TD]1234[/TD]
[TD]12[/TD]
[TD]2007[/TD]
[TD]Grey[/TD]
[TD]Honda[/TD]
[TD]Odyssey[/TD]
[TD]ABC1234[/TD]
[TD]2006[/TD]
[TD]Blue[/TD]
[TD]Chrysler[/TD]
[TD]PT Cruiser[/TD]
[TD]DEF2345[/TD]
[TD]2016[/TD]
[TD]Grey[/TD]
[TD]Dodge[/TD]
[TD]Journey[/TD]
[TD]GHI7689[/TD]
[/TR]
</tbody>[/TABLE]

So my workaround is to enter the data for one parking space on multiple rows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Lot[/TD]
[TD]Space[/TD]
[TD]Sched[/TD]
[TD]Last[/TD]
[TD]First[/TD]
[TD]ID[/TD]
[TD]Grade[/TD]
[TD]V1 Year[/TD]
[TD]V1 Color[/TD]
[TD]V1 Make[/TD]
[TD]V1 Model[/TD]
[TD]V1 Lic. Plate[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Gold[/TD]
[TD]440[/TD]
[TD][/TD]
[TD]Doe[/TD]
[TD]John[/TD]
[TD]1234[/TD]
[TD]12[/TD]
[TD]2007[/TD]
[TD]Grey[/TD]
[TD]Honda[/TD]
[TD]Odyssey[/TD]
[TD]ABC1234[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Gold[/TD]
[TD]440[/TD]
[TD][/TD]
[TD]Doe[/TD]
[TD]John[/TD]
[TD]1234[/TD]
[TD]12[/TD]
[TD]2006[/TD]
[TD]Blue[/TD]
[TD]Chrysler[/TD]
[TD]PT Cruiser[/TD]
[TD]DEF2345[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Gold[/TD]
[TD]440[/TD]
[TD][/TD]
[TD]Doe[/TD]
[TD]John[/TD]
[TD]1234[/TD]
[TD]12[/TD]
[TD]2016[/TD]
[TD]Grey[/TD]
[TD]Dodge[/TD]
[TD]Journey[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]GHI7689[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The code I have for this is:

Code: 
Private Sub cmdAddStudent_Click()


    Dim DataSH As Worksheet
    Dim Addme As Range
    Set DataSH = Sheet1
    On Error GoTo errHandler:
    Set Addme = DataSH.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0)
    Application.ScreenUpdating = False
    If Me.txtLast = "" Or Me.txtfirst = "" Or Me.txtStudentID = "" Then
        MsgBox "There is insufficient data, Please return and add the needed information"
    Exit Sub
    End If



    With DataSH
    
        
            Addme.Value = Me.txtLast
            Addme.Offset(0, 1).Value = Me.txtfirst
            Addme.Offset(0, 2).Value = Me.txtStudentID.Value
            
                If obSenior.Value = True Then
                        Addme.Offset(0, 3).Value = "12"
                    ElseIf obJunior.Value = True Then
                        Addme.Offset(0, 3).Value = "11"
                    ElseIf obSoph.Value = True Then
                        Addme.Offset(0, 3).Value = "10"
                    Else
                        Addme.Offset(0, 3).Value = ""
                End If
                
                If obRegular.Value = True Then
                        Addme.Offset(0, -1).Value = "Regular"
                    ElseIf obEVIT.Value = True Then
                        Addme.Offset(0, -1).Value = "EVIT"
                    ElseIf obER.Value = True Then
                        Addme.Offset(0, -1).Value = "ER"
                    ElseIf obLS.Value = True Then
                        Addme.Offset(0, -1).Value = "LS"
                    Else
                        Addme.Offset(0, -1).Value = ""
                End If
                
            Addme.Offset(0, 4).Value = Me.cboYear1
            Addme.Offset(0, 5).Value = Me.cboColor1
            Addme.Offset(0, 6).Value = Me.cboMake1
            Addme.Offset(0, 7).Value = Me.cboModel1
            Addme.Offset(0, 8).Value = Me.txtLicPlate1
            
[SIZE=4] [COLOR=#ff0000]               If Me.cboYear2 = "" Then
                    Exit Sub[/COLOR][/SIZE]
                        Else
                    Addme.Offset(1, 0) = Me.txtLast
                    Addme.Offset(1, 1).Value = Me.txtfirst
                    Addme.Offset(1, 2).Value = Me.txtStudentID.Value
            
                    If obSenior.Value = True Then
                            Addme.Offset(1, 3).Value = "12"
                        ElseIf obJunior.Value = True Then
                            Addme.Offset(1, 3).Value = "11"
                        ElseIf obSoph.Value = True Then
                            Addme.Offset(1, 3).Value = "10"
                        Else
                            Addme.Offset(1, 3).Value = ""
                    End If
        
                    If obRegular.Value = True Then
                            Addme.Offset(1, -1).Value = "Regular"
                        ElseIf obEVIT.Value = True Then
                            Addme.Offset(1, -1).Value = "EVIT"
                        ElseIf obER.Value = True Then
                            Addme.Offset(1, -1).Value = "ER"
                        ElseIf obLS.Value = True Then
                            Addme.Offset(1, -1).Value = "LS"
                        Else
                            Addme.Offset(1, -1).Value = ""
                    End If
                    
                    Addme.Offset(1, 4).Value = Me.cboYear2
                    Addme.Offset(1, 5).Value = Me.cboColor2
                    Addme.Offset(1, 6).Value = Me.cboMake2
                    Addme.Offset(1, 7).Value = Me.cboModel2
                    Addme.Offset(1, 8).Value = Me.txtLicPlate2
                End If
          
[SIZE=4]            [COLOR=#ff0000]    If Me.cboYear3 = "" Then
                    Exit Sub[/COLOR][/SIZE]
                        Else
                    Addme.Offset(2, 0) = Me.txtLast
                    Addme.Offset(2, 1).Value = Me.txtfirst
                    Addme.Offset(2, 2).Value = Me.txtStudentID.Value
            
                    If obSenior.Value = True Then
                            Addme.Offset(2, 3).Value = "12"
                        ElseIf obJunior.Value = True Then
                            Addme.Offset(2, 3).Value = "11"
                        ElseIf obSoph.Value = True Then
                            Addme.Offset(2, 3).Value = "10"
                        Else
                            Addme.Offset(2, 3).Value = ""
                    End If
        
                    If obRegular.Value = True Then
                            Addme.Offset(2, -1).Value = "Regular"
                        ElseIf obEVIT.Value = True Then
                            Addme.Offset(2, -1).Value = "EVIT"
                        ElseIf obER.Value = True Then
                            Addme.Offset(2, -1).Value = "ER"
                        ElseIf obLS.Value = True Then
                            Addme.Offset(2, -1).Value = "LS"
                        Else
                            Addme.Offset(2, -1).Value = ""
                    End If
         
                    Addme.Offset(2, 4).Value = Me.cboYear3
                    Addme.Offset(2, 5).Value = Me.cboColor3
                    Addme.Offset(2, 6).Value = Me.cboMake3
                    Addme.Offset(2, 7).Value = Me.cboModel3
                    Addme.Offset(2, 8).Value = Me.txtLicPlate3
                End If
        End With
    Exit Sub

    Clear

    MsgBox "Your parking data was successfully added"

    On Error GoTo 0
    Exit Sub


errHandler:

    MsgBox "Error " & Err.Number & _
    " (" & Err.Description & ")in procedure cmdClear_Click of Form ParkinglotDB"


End Sub



The problem is now that I've added the IF statement for

Code: 
If Me.cboYear2 = "" Then[INDENT]Exit Sub[/INDENT]
         Else

It's not going to the bottom of the sub to

Code: 
Clear

    MsgBox "Your parking data was successfully added"


My questions:

1. How do I search/filter multiple columns of entry? (I can post what I have for the filter/search, but basically it searches and copy's the data to another part of the SS into a named area and then puts that data into a listbox in the userform.)
or
2. Why is it not going to the Clear and msgbox at the end of the routine?

Thank you in advance
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Exit Sub does exactly what it says on the tin. ;)
try
Code: 
   With DataSH
   
      
      Addme.Value = Me.txtLast
      Addme.Offset(0, 1).Value = Me.txtfirst
      Addme.Offset(0, 2).Value = Me.txtStudentID.Value
      
      If obSenior.Value = True Then
         Addme.Offset(0, 3).Value = "12"
      ElseIf obJunior.Value = True Then
         Addme.Offset(0, 3).Value = "11"
      ElseIf obSoph.Value = True Then
         Addme.Offset(0, 3).Value = "10"
      Else
         Addme.Offset(0, 3).Value = ""
      End If
      
      If obRegular.Value = True Then
         Addme.Offset(0, -1).Value = "Regular"
      ElseIf obEVIT.Value = True Then
         Addme.Offset(0, -1).Value = "EVIT"
      ElseIf obER.Value = True Then
         Addme.Offset(0, -1).Value = "ER"
      ElseIf obLS.Value = True Then
         Addme.Offset(0, -1).Value = "LS"
      Else
         Addme.Offset(0, -1).Value = ""
      End If
      
      Addme.Offset(0, 4).Value = Me.cboYear1
      Addme.Offset(0, 5).Value = Me.cboColor1
      Addme.Offset(0, 6).Value = Me.cboMake1
      Addme.Offset(0, 7).Value = Me.cboModel1
      Addme.Offset(0, 8).Value = Me.txtLicPlate1
      
      If Me.cboYear2 <> "" Then
      
         Addme.Offset(1, 0) = Me.txtLast
         Addme.Offset(1, 1).Value = Me.txtfirst
         Addme.Offset(1, 2).Value = Me.txtStudentID.Value
      
         If obSenior.Value = True Then
            Addme.Offset(1, 3).Value = "12"
         ElseIf obJunior.Value = True Then
            Addme.Offset(1, 3).Value = "11"
         ElseIf obSoph.Value = True Then
            Addme.Offset(1, 3).Value = "10"
         Else
            Addme.Offset(1, 3).Value = ""
         End If
      
         If obRegular.Value = True Then
            Addme.Offset(1, -1).Value = "Regular"
         ElseIf obEVIT.Value = True Then
            Addme.Offset(1, -1).Value = "EVIT"
         ElseIf obER.Value = True Then
            Addme.Offset(1, -1).Value = "ER"
         ElseIf obLS.Value = True Then
            Addme.Offset(1, -1).Value = "LS"
         Else
            Addme.Offset(1, -1).Value = ""
         End If
      
         Addme.Offset(1, 4).Value = Me.cboYear2
         Addme.Offset(1, 5).Value = Me.cboColor2
         Addme.Offset(1, 6).Value = Me.cboMake2
         Addme.Offset(1, 7).Value = Me.cboModel2
         Addme.Offset(1, 8).Value = Me.txtLicPlate2
      End If
      
      If Me.cboYear3 <> "" Then
         Addme.Offset(2, 0) = Me.txtLast
         Addme.Offset(2, 1).Value = Me.txtfirst
         Addme.Offset(2, 2).Value = Me.txtStudentID.Value
               
         If obSenior.Value = True Then
            Addme.Offset(2, 3).Value = "12"
         ElseIf obJunior.Value = True Then
            Addme.Offset(2, 3).Value = "11"
         ElseIf obSoph.Value = True Then
            Addme.Offset(2, 3).Value = "10"
         Else
            Addme.Offset(2, 3).Value = ""
         End If
         
         If obRegular.Value = True Then
            Addme.Offset(2, -1).Value = "Regular"
         ElseIf obEVIT.Value = True Then
            Addme.Offset(2, -1).Value = "EVIT"
         ElseIf obER.Value = True Then
            Addme.Offset(2, -1).Value = "ER"
         ElseIf obLS.Value = True Then
            Addme.Offset(2, -1).Value = "LS"
         Else
            Addme.Offset(2, -1).Value = ""
         End If
         
         Addme.Offset(2, 4).Value = Me.cboYear3
         Addme.Offset(2, 5).Value = Me.cboColor3
         Addme.Offset(2, 6).Value = Me.cboMake3
         Addme.Offset(2, 7).Value = Me.cboModel3
         Addme.Offset(2, 8).Value = Me.txtLicPlate3
      End If
   End With
   
   Clear
   
   MsgBox "Your parking data was successfully added"
   
   On Error GoTo 0
   Exit Sub
   
   
errHandler:
   
   MsgBox "Error " & Err.Number & _
   " (" & Err.Description & ")in procedure cmdClear_Click of Form ParkinglotDB"
   

End Sub
 
Upvote 0
Thank you so much...feel a bit silly something so simple. Still trying to wrap my head around this stuff, it is so fun!!
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

Share this page

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