User form, search by typing.

[USAMax]

I have an existing User Form that allows me to make a selection by using a drop-down menu or by pasting a value in and having it search. I want to have it search for a match as I type as well but I am not sure where to add this into my existing ComboBox_Change routine. I know this is long but can someone suggest where to add this routine?

<Code>
Private Sub ComboBox2_Change()
Const FloSht As String = "Flow"
Dim ValCT As Integer 'Variable Count for Tasks found
Dim i As Long 'Current Row
Dim TotTime As Double 'Total Time from the Task Flow worksheet
Dim TotDays As Integer 'Total Days from the Task Flow worksheet
Dim TotHrs As Integer 'Total Hours calculated from the Task Flow worksheet
Dim TotMin As String 'Total Minutes calculated from the Task Flow worksheet
Dim CurTime As String 'Display of Total Days, Total Hours and Total Minutes

'FIND THE ROW THE SELECTED TASK WAS ON
ValCT = 0 'Initialize the Value Count for the GlobalArray to 0
Do While GlobalArray(ValCT).cellStr <> ComboBox2 And _
ValCT < 100 'Is the GlobalArray the same as the Combo Box?
ValCT = ValCT + 1 ' NO:Increase the count to check the next value in the GlobalArray
Loop 'Check again

If GlobalArray(ValCT).cellStr <> ComboBox2 Then
Application.ScreenUpdating = False
CurSht = ActiveSheet.Name
Sheets("Flow").Select
Set rgLast = Range("A1").SpecialCells(xlCellTypeLastCell) ' Locate the Last Cell
LstRow = rgLast.Row ' Get the Last Row
If Cells(LstRow, 3) = "" Then ' Is the last row in the task Description column blank?
LstRow = Cells(Rows.Count, 3).End(xlUp).Row ' YES: Scroll up to the first none blank cell in the task Description column.
End If '

GlobalArray(ValCT).RowColId = FindText(3, LstRow, ComboBox2)
Sheets(CurSht).Select
Application.ScreenUpdating = True

If GlobalArray(ValCT).RowColId <> 0 Then
GlobalArray(ValCT).cellStr = Sheets("Flow").Cells(GlobalArray(ValCT).RowColId, 3)
Else
TskPrmpt1 = "The selected task is not in the Task Flow. " & _
"Would you like to add it?" 'First Task Prompt
TskPrmpt2 = " " 'Second Task Prompt. Spacer before the new task.
TskPrmpt3 = "Click CANCEL to select a new task." 'Third Task Prompt
TskChk = MsgBox(TskPrmpt1 & vbCrLf & vbCrLf & _
TskPrmpt2 & ComboBox2 & vbCrLf & vbCrLf & _
TskPrmpt3, vbYesNoCancel, "NEW TASK FLOW") 'Prompt YES to add new task, NO to update ITL without adding new task and CANCEL to choose existing task again.

Do While TskChk = "" 'Did the user choose CANCEL to choose existing task again?
TskChk = MsgBox(TskPrmpt1 & vbCrLf & vbCrLf & _
TskPrmpt2 & ComboBox2 & vbCrLf & vbCrLf & _
TskPrmpt3, vbYesNoCancel, "NEW TASK FLOW") 'YES: Prompt YES to add new task, NO to update ITL without adding new task and CANCEL to choose existing task again.
Loop ' CHECK AGAIN

If TskChk = 6 Then ' Did the user choose YES?
Sheets(FloSht).Cells(LstRow + 1, 3) = ComboBox2 ' Copy the new task onto the task Flow worksheet
Sheets(FloSht).Cells(LstRow + 1, 3).IndentLevel = 2 ' Indent the new task
Sheets(FloSht).Cells(LstRow + 1, 1) = _
"Added by " & Application.UserName & _
" @ " & Format(Now(), "mm/dd/yy hh:mm") ' Show who entered the new task and when
ActiveCell.Offset(0, 1) = LstRow + 1 ' Enter the row on the task Flow worksheet it was entered on.
ActiveCell.Offset(0, 1).NumberFormat = "####"
Else
If TskChk = 2 Then
Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, 4)).ClearContents
End
End If
End If
ActiveCell = ComboBox2 ' Enter the new task into the ITL
ActiveCell.WrapText = True
Unload Me ' Close the user form without doing anything
GoTo UserEntryExit ' Exit because of User Entry
End If
End If

i = GlobalArray(ValCT).RowColId 'Get the row after the last value selected in the Combo Box

'UPDATE THE HEADER
If i >= 3 Then 'Is the current row defined by, "i" below the header in row 3?
If Sheets(FloSht).Cells(i, 5) <> "" Then 'YES: Was there an estimated task time?
CurTime = Format(Sheets(FloSht) _
.Cells(i, 5), "hh:mm") '
TotTime = Sheets(FloSht).Cells(i, 5) ' Get the time
TotDays = Int(TotTime)
TotHrs = Int((TotTime - TotDays) * 24) ' Calculate the Hours
TotMin = Int((TotTime - TotDays - _
(TotHrs / 24)) * 1440) ' Calculate the Minutes

If TotDays < 1 Then
UserFormFlow.Label1.Caption = _
"Select the task to be performed." & _
vbCrLf & vbCrLf & "Duration: " _
& CurTime ' Return the default label with the task time from the Task Flow worksheet
Else
If TotDays < 2 Then
UserFormFlow.Label1.Caption = _
"Select the task to be performed." & _
vbCrLf & vbCrLf & "Duration: " _
& TotDays & " Day " & _
TotHrs & " Hr and " & TotMin & " Min" ' Return the default label with the task time from the Task Flow worksheet
Else
UserFormFlow.Label1.Caption = _
"Select the task to be performed." & _
vbCrLf & vbCrLf & "Duration: " _
& TotDays & " Days " & _
TotHrs & " Hr and " & TotMin & " Min" ' Return the default label with the task time from the Task Flow worksheet
End If
End If
Else ' NO:
UserFormFlow.Label1.Caption = _
"Select the task to be performed." ' NO:Return the default label only.
End If '
End If '
UserEntryExit:
End Sub
</code>

Thanks,

 

[apo]

Hi..

Your code is hard to read (no code tags/indents).. but.. try to add this..

It is a Userform that, when a string is typed into the textbox.. the autocomplete results are returned in the Listbox.. (It will search through Column A)..

[/URL][/IMG]

Option Compare Text


Private Sub TextBox1_Change()
    Dim rng As Range, e
    With Me
        .ListBox1.Clear
        If Len(.TextBox1.Value) Then
            For Each e In Sheets("sheet1").Cells(1).CurrentRegion.Columns(1).Offset(1).Value
                If (e <> "") * (e Like "*" & .TextBox1.Value & "*") Then
                    .ListBox1.AddItem e
                End If
            Next
            With .ListBox1
                If .ListCount > 0 Then .ListIndex = 0
            End With
        End If
    End With
End Sub




Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
MsgBox ListBox1.Value  ' Change this to do something....
End Sub

Example of data...

Sheet1

*	A
1	Names
2	Apo
3	Mike
4	Jim
5	Jessy
6	Bill
7	Superman

<tbody>
</tbody>

Excel tables to the web - Excel Jeanie Html 4

 

[Rick Rothstein]

I have an existing User Form that allows me to make a selection by using a drop-down menu or by pasting a value in and having it search. I want to have it search for a match as I type as well...

When you say "drop-down menu", do you mean a ComboBox control? If so, does setting the ComboBox's MatchEntry property in the Properties windwo to "1-fmMatchEntryComplete" make the ComboBox typed-in entry work the way you want?

 

[USAMax]

Thank you apo, I searched the internet for 20 minutes to find the code tags for Mr Excel but the only ones I found were HTML and as you know, it didn't work.

I am sure your code will work, the problem is where to insert it into what I have. If you give me the code tags I will resubmit what I have. I know there is a slash after or before the word code but I am not sure.

 

[USAMax]

Yes, I am using a ComboBox control.

I guess I could set it to "1-fmMatchEntryComplete" but would that affect the way my existing code works?

 

[apo]

Hi..

The code tags option on Mr Excel is found when you press the "Go Advanced" button (to the right of "Post Quick Reply" button)..

I am sure your code will work, the problem is where to insert it into what I have. If you give me the code tags I will resubmit what I have. I know there is a slash after or before the word code but I am not sure.

Having said that.. you have caught the attention of one of the Gurus here (Rick Rothstein).. so i would probably go with what he is inferring..

 

[Rick Rothstein]

Yes, I am using a ComboBox control.

I guess I could set it to "1-fmMatchEntryComplete" but would that affect the way my existing code works?

I cannot think of any reason that it would... with the property set that way, as you type, the control shows you what you typed and the first entry in the list that matches those beginning letters (the remaining not-yet-typed letters are show highlighted). I would suggest trying it and seeing... you have nothing to lose as you can change the property back to one of the other options if you do not like how it works.