Try it this way - keep the last name text box, and add a listbox. Add a label that says "Enter you last name here" Then after the user enters their last name, everything else to be entered is done using available values in the listbox - I'm
never sure how my name is entered (Bernard or Bernie), but I am sure of my last name - forget dates and times, too....
Option Explicit
Dim myF As Range
Dim shtSI As Worksheet
Dim varPossible As Variant
Dim varEach As Variant
Dim varList As Variant
Dim MyMonth As Integer
Dim MonthName As String
Dim strLastName As String
Dim strFirstName As String
Dim dteDate As Date
Dim dteTime As Date
Dim myA As Range
Dim myC As Range
Dim myD As Range
Dim myT As Range
Dim myN As Range
Dim myV As Integer
Private Sub CmdButtonUnbook_Click()
If Me.CmdButtonUnbook.Caption = "Choose Last Name" Then
strLastName = Me.TextBoxLastName.Value
Set myA = myF.Columns(3).Find(strLastName)
If myA Is Nothing Then
MsgBox "That last name was not found"
Exit Sub
End If
Me.TextBoxLastName.Enabled = False
Me.ListBox.Enabled = True
myF.AutoFilter Field:=3, Criteria1:=strLastName
Me.CmdButtonUnbook.Caption = "Choose First Name"
MakeList 1
Exit Sub
End If
If Me.CmdButtonUnbook.Caption = "Choose First Name" Then
strFirstName = Me.ListBox.Value
myF.AutoFilter Field:=1, Criteria1:=strFirstName
Me.CmdButtonUnbook.Caption = "Choose Date"
MakeList 7
Exit Sub
End If
If Me.CmdButtonUnbook.Caption = "Choose Date" Then
dteDate = Me.ListBox.Value
myF.AutoFilter Field:=7, Criteria1:=Format(dteDate, myF.Cells(2, 7).NumberFormat)
MyMonth = Month(dteDate)
MonthName = Format(dteDate, "MMMM")
Me.CmdButtonUnbook.Caption = "Choose Time"
MakeList 9
Exit Sub
End If
If Me.CmdButtonUnbook.Caption = "Choose Time" Then
dteTime = Me.ListBox.Value
myF.AutoFilter Field:=9, Criteria1:=Format(dteTime, myF.Cells(2, 9).NumberFormat)
Me.CmdButtonUnbook.Caption = "Proceed"
Exit Sub
End If
If Me.CmdButtonUnbook.Caption = "Proceed" Then
For Each myC In myF.Columns(5).SpecialCells(xlCellTypeVisible)
myV = 0
If myC.Value = "Adult" Then myV = 1
If myC.Value = "Student" Then myV = 2
If myC.Value = "Senior" Then myV = 3
If myV <> 0 Then
Set myD = Worksheets(MonthName).Range("C:L").Find(DateValue(dteDate))
If myD Is Nothing Then
MsgBox dteDate & " was not found on sheet " & MonthName
Else
Set myT = myD.Offset(1, -2).Find(dteTime)
If myT Is Nothing Then
MsgBox dteTime & " was not found on sheet " & MonthName
Else
Set myN = myT.EntireRow.Find(myV)
If myN Is Nothing Then
MsgBox myV & " was not found on " & MonthName & " in " & myT.EntireRow.Address
Else
myN.Value = "."
MsgBox myN.Address & " was changed "
End If
End If
End If
End If
Next myC
myF.Columns(5).SpecialCells(xlCellTypeVisible).EntireRow.Delete
myF.AutoFilter
myF.AutoFilter
End If
Unload Me
End Sub
Private Sub UserForm_Initialize()
Set shtSI = Worksheets("Student Information")
If shtSI.AutoFilterMode Then shtSI.ShowAllData
Set myF = shtSI.Range("A1", shtSI.Cells(shtSI.Rows.Count, 9).End(xlUp))
Me.CmdButtonUnbook.Caption = "Choose Last Name"
Me.ListBox.Enabled = False
Me.TextBoxLastName.Enabled = True
End Sub
Private Sub MakeList(c As Integer)
Set varPossible = myF.Columns(c).Offset(1).Resize(myF.Rows.Count - 1).Cells
With CreateObject("Scripting.Dictionary")
For Each varEach In varPossible
If varEach.Value <> "" And (Not varEach.EntireRow.Hidden) And Not .exists(varEach.Value) Then
.Add IIf(c = 9, Format(varEach.Value, "h:mm"), varEach.Value), Nothing
End If
Next
varList = .keys
End With
With Me.ListBox
.Clear ' clear the listbox content
.List = varList
.ListIndex = 0 ' select the first item
End With
End Sub
