UserForm function acting irratically

[ebraatz]

Hi, I have a conundrum.
I created a Database program for a small school (about 100 students). I created a userform with approx 60 fields (on 4 different tabs) to input student data. I then replicated that same form to be used to edit student data. This is the form I'm having issues with.

There is a listbox that fills with all the student names. When a name is selected, the click event is triggered which pulls all the entry values from the worksheet. The problem I'm running into is that sometimes, the information on the 3rd tab doesn't fill correctly, sometimes it does, sometimes it doesn't. It seems that if I scroll in the listbox and then click that it will then adjust the entries for the third tab, however this doesn't always work. I also have it set up so that the user can type the name in and it will find the match. The code for those fields isn't any different than the code for the other fields that are filling correctly. There's a lot of code this beast, so I'll share what I think is most pertinent to the situation.

In General Module,Sub called when clicking button on spreadsheet:

Public Sub LaunchDBEdit()
 '--example showing passing of table object and lookup field
 '  to database input userform.
 
 '--declare variable frm to be a DBEdit object
 Dim frm As DBEdit
 
 '--create a new instance of DBEdit
 Set frm = New DBEdit
 
 '--pass table object to public property of frm
 Set frm.DataTable = ThisWorkbook.Sheets("Database").ListObjects("Database")
 
 '--pass header of table field to be used for lookup
 frm.LookupField = "Full Name"
 
 '--show frm to allow user to interact
 frm.Show


End Sub

In the userform Module:

'Code launched when a selection is made:
Private Sub LISTStuName_Click()
 'with each user click of listbox, store selected row
 'then get corresponding data
 
 If LISTStuName.ListIndex > -1 Then
   '--store selected row in module-scoped variable
   mlRowIndex = LISTStuName.ListIndex + 1
   
   '--populate textboxes
   Call GetData
 End If
End Sub

'Code to retrieve data from spreadsheet
 

Private Sub GetData()
 '--populate text boxes using a lookup function
 With Me
    'Get ChildInfo
    .LISTClass.Value = sGetValue(sHeader:="Class")
    .TXTFirst = sGetValue(sHeader:="First")
    .TXTLast = sGetValue(sHeader:="Last")
    .TXTSchoolName = sGetValue(sHeader:="School Name")
    .TXTDOB = sGetValue(sHeader:="DOB")
    .TXTBirthPlace = sGetValue(sHeader:="Birth Place")
    .TXTLanguage = sGetValue(sHeader:="Language")
    .TXTOrigin = sGetValue(sHeader:="Ethnicity")
    If sGetValue(sHeader:="M/F") = "M" Then OBMale = True
    If sGetValue(sHeader:="M/F") = "f" Then OBFemale = True
    
    'Get Address
    .TXTStreet = sGetValue(sHeader:="Street")
    .TXTCity = sGetValue(sHeader:="City")
    .TXTZip = sGetValue(sHeader:="Zip")
    .TXTHomePhone = sGetValue(sHeader:="Home Phone")
    
    'Get Mother's Info
    .TXTMFirst = sGetValue(sHeader:="MFirst")
    .TXTMLast = sGetValue(sHeader:="MLast")
    .TXTMCell = sGetValue(sHeader:="MCell")
    .TXTMEmployer = sGetValue(sHeader:="MEmployer")
    .TXTMOccupation = sGetValue(sHeader:="MOccupation")
    .TXTMWork = sGetValue(sHeader:="MWork")
    .TXTMEmail = sGetValue(sHeader:="MEmail")
    If sGetValue(sHeader:="Marital Status") = "Single" Then .OBSingle = True
    If sGetValue(sHeader:="Marital Status") = "Married" Then .OBMarried = True
    If sGetValue(sHeader:="Marital Status") = "Separated" Then .OBSeparated = True
    If sGetValue(sHeader:="Marital Status") = "Divorced" Then .OBDivorced = True
    
    'Get Father's Info
    .TXTFFirst = sGetValue(sHeader:="FFirst")
    .TXTFLast = sGetValue(sHeader:="FLast")
    .TXTFCell = sGetValue(sHeader:="FCell")
    .TXTFEmployer = sGetValue(sHeader:="FEmployer")
    .TXTFOccupation = sGetValue(sHeader:="FOccupation")
    .TXTFWork = sGetValue(sHeader:="FWork")
    .TXTFEmail = sGetValue(sHeader:="FEmail")


'*****The following information isn't always pulled correctly.  It is on the third tab in the form*****    
    'Get Health and Diet Info
    If sGetValue(sHeader:="Premie") = "Yes" Then .Premie = True
    If sGetValue(sHeader:="Asthma") = "Yes" Then .Asthma = True
    If sGetValue(sHeader:="Glasses") = "Yes" Then .Glasses = True
    If sGetValue(sHeader:="R-Hand") = "X" Then .Rhand = True
    If sGetValue(sHeader:="L-Hand") = "X" Then .LHand = True
    If sGetValue(sHeader:="Food Allergies") <> "None" Then
        .Food = True
        .TXTFood = sGetValue(sHeader:="Food Allergies")
    End If
    If sGetValue(sHeader:="Other Allergies") <> "None" Then
        .Allergy = True
        .TXTAllergy = sGetValue(sHeader:="Other Allergies")
    End If
    If sGetValue(sHeader:="Medications") <> "None" Then
        .Med = True
        .TXTMed = sGetValue(sHeader:="Medications")
    End If
    If sGetValue(sHeader:="Other Health") <> "None" Then
        .OtherHealth = True
        .TXTOtherHealth = sGetValue(sHeader:="Other Health")
    End If
    If sGetValue(sHeader:="Veg") = "Yes" Then .Vegetarian = True
    If sGetValue(sHeader:="GF") = "Yes" Then .GF = True
    If sGetValue(sHeader:="Other Diet") <> "None" Then
        .OtherDiet = True
        .TXTOtherDiet = sGetValue(sHeader:="Other Diet")
    End If
    
    'Get Misc
    If sGetValue(sHeader:="Photo") = "Yes" Then .Photos = True
    If sGetValue(sHeader:="Directory") = "Yes" Then .Directory = True
    If sGetValue(sHeader:="Member?") = "Yes" Then
        .Member = True
        .TXTMember = sGetValue(sHeader:="Member?")
    End If
    If sGetValue(sHeader:="Visit?") = "Yes" Then .Visit = True
    .TXTRegDate = sGetValue(sHeader:="Reg. Date")
    
    'get emergency info
    .TXTNameEC1 = sGetValue(sHeader:="Name1")
    .TXTRelationEC1 = sGetValue(sHeader:="Relationship1")
    .TXTStreetEC1 = sGetValue(sHeader:="Street1")
    .TXTCityEC1 = sGetValue(sHeader:="City1")
    .TXTZipEC1 = sGetValue(sHeader:="zip1")
    .TXTHomeEC1 = sGetValue(sHeader:="Home Phone1")
    .TXTWorkEC1 = sGetValue(sHeader:="work phone1")
    .TXTNameEC2 = sGetValue(sHeader:="Name2")
    .TXTRelationEC2 = sGetValue(sHeader:="Relationship2")
    .TXTStreetEC2 = sGetValue(sHeader:="Street2")
    .TXTCityEC2 = sGetValue(sHeader:="City2")
    .TXTZipEC2 = sGetValue(sHeader:="zip2")
    .TXTHomeEC2 = sGetValue(sHeader:="Home Phone2")
    .TXTWorkEC2 = sGetValue(sHeader:="work phone2")
    .TXTDRName = sGetValue(sHeader:="Physician")
    .TXTDrPhone = sGetValue(sHeader:="Dr Phone")
    If sGetValue(sHeader:="WATCH") = "Yes" Then .CBWatch = True
 End With
    
End Sub

'Function used in the GetData sub


Private Function sGetValue(sHeader As String) As String
 '--get values from lookup table using
 '    coordinates of selected row index and
 '    lookup of column number in header
 
 Dim vFieldColumn As Variant
 
 vFieldColumn = Application.Match(sHeader, mvHeaders, 0)
 
 If IsNumeric(vFieldColumn) Then
   sGetValue = mtblLookup.DataBodyRange(mlRowIndex, CLng(vFieldColumn)).Value
 End If


End Function