Userform listbox visibility

[wwbwb]

Greetings all,

I have a userform that I use to input names on a sheet. When the userform opens, textbox1 will populate depending on if a certain cell contains a name based on this line.

UserForm2.TextBox1.Value = Worksheets("list").Range("e" & res).Value

This works fine. I recently added the following code to the userform to have a listbox appear to give the user suggestions as they type a name into textbox1.

Private Sub textbox1_change()
'Code adapted from youtube.com/watch?v=e-3DrNiQ31I
Dim i As Integer

ListBox1.Clear

For i = 2 To 900
If UCase(Left(Worksheets("list").Cells(i, 5), Len(TextBox1.Text))) = UCase(TextBox1.Text) Then
 ListBox1.AddItem Worksheets("List").Cells(i, 5)
End If
Next i
ListBox1.Visible = True

End Sub

Private Sub TextBox1_Exit(ByVal cancel As MSForms.ReturnBoolean)

On Error Resume Next
ListBox1.Visible = False
End Sub

Private Sub listbox1_dblclick(ByVal cancel As MSForms.ReturnBoolean)

TextBox1.Text = ListBox1.List(ListBox1.ListIndex)
ListBox1.Visible = False
End Sub

Private Sub UserForm_Initialize()
ListBox1.Visible = False
End Sub

Everything works fine, with one little hang up. When the userform opens, and textbox1 populate with nothing, great. It works as expected. When the userform opens, and textbox1 populates with something, the listbox is visible. It goes away if I go to another box like it should. I understand it does because textbox1 changed, but I'd like to find a way for it to only appear when a user types something. Any thoughts?

 

[Fluff]

At what point are you populating TextBox1?

 

[Akuini]

Try using TextBox1_KeyDown event Or TextBox1_Enter event for ListBox1.Visible = True

 

[wwbwb]

I'm populating the textbox just before the userform is shown.

 

[Fluff]

How & where?
Is it from code in a regular module? a worksheet event? another userform?

 

[wwbwb]

On sheet A, there is a button. Pressing it runs the following code to populate and show the userform.

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim res As Variant
On Error Resume Next
Err.Clear
res = Application.WorksheetFunction.Match(ActiveCell.Value, Worksheets("List").Range("b:b"), 0)
'Populates userform with saved data
If Err.Number = 0 Then
 UserForm2.TextBox1.Value = Worksheets("list").Range("e" & res).Value
 UserForm2.TextBox2.Value = Worksheets("list").Range("d" & res).Value
 UserForm2.TextBox3.Value = Worksheets("list").Range("f" & res).Value
 UserForm2.TextBox4.Value = Format(Worksheets("list").Range("g" & res).Value, "dd-mmm-yyyy")
 UserForm2.TextBox5.Value = Format(Worksheets("list").Range("h" & res).Value, "dd-mmm-yyyy")
 UserForm2.TextBox7.Value = Worksheets("list").Range("i" & res).Value
 UserForm2.TextBox8.Value = Worksheets("list").Range("j" & res).Value
 UserForm2.TextBox9.Value = Worksheets("list").Range("k" & res).Value
 UserForm2.TextBox10.Value = Format(Worksheets("list").Range("l" & res).Value, "dd-mmm-yyyy")
 UserForm2.TextBox11.Value = Format(Worksheets("list").Range("m" & res).Value, "dd-mmm-yyyy")
 If Worksheets("list").Range("n" & res).Value = "x" Then
  UserForm2.CheckBox1.Value = True
  Else
  UserForm2.CheckBox1.Value = False
 End If
 UserForm2.Label6.Caption = ActiveCell.Value
 
 'Checks for full AF126
 If Sheets("Af126").Range("a21").Value <> "" Then
  msg = MsgBox("AF126 if full. Can not continue until AF126 is printed." & Chr(13) & Chr(13) & "Press Yes to print.", vbYesNo, "AF126 Error")
  If msg = vbYes Then
   Sheets("AF126").PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
   Sheets("AF126").Range("a6:j21").ClearContents
   UserForm2.Show
   Else
   End If
  Else
  UserForm2.Show
 End If
 'End Checks for full AF126
 
 Else
 MsgBox "You must select one of the serial numbers to continue.", , "Select Serial Number"
End If
Application.ScreenUpdating = True
End Sub

The previous code is in the userform itself.

 

[wwbwb]

Try using TextBox1_KeyDown event Or TextBox1_Enter event for ListBox1.Visible = True

I tried replacing the Textbox1_Change with the Key down. That produced a more desirable result visually, but the listbox would populate only after the second letter was typed in. So if I typed in S for SMITH, the listbox wouldn't populate until I got to the second letter.

 

[Fluff]

Ok, put this at the top of a regular module (before any code)

Public MyDisableEvents As Boolean

Add the two lines as shown

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim res As Variant
On Error Resume Next
Err.Clear
res = Application.WorksheetFunction.Match(ActiveCell.Value, Worksheets("List").Range("b:b"), 0)
'Populates userform with saved data
If Err.Number = 0 Then
 [COLOR=#ff0000]MyDisableEvents = True[/COLOR]
 UserForm2.TextBox1.Value = Worksheets("list").Range("e" & res).Value
 UserForm2.TextBox2.Value = Worksheets("list").Range("d" & res).Value
 UserForm2.TextBox3.Value = Worksheets("list").Range("f" & res).Value
 UserForm2.TextBox4.Value = Format(Worksheets("list").Range("g" & res).Value, "dd-mmm-yyyy")
 UserForm2.TextBox5.Value = Format(Worksheets("list").Range("h" & res).Value, "dd-mmm-yyyy")
 UserForm2.TextBox7.Value = Worksheets("list").Range("i" & res).Value
 UserForm2.TextBox8.Value = Worksheets("list").Range("j" & res).Value
 UserForm2.TextBox9.Value = Worksheets("list").Range("k" & res).Value
 UserForm2.TextBox10.Value = Format(Worksheets("list").Range("l" & res).Value, "dd-mmm-yyyy")
 UserForm2.TextBox11.Value = Format(Worksheets("list").Range("m" & res).Value, "dd-mmm-yyyy")
 If Worksheets("list").Range("n" & res).Value = "x" Then
  UserForm2.CheckBox1.Value = True
  Else
  UserForm2.CheckBox1.Value = False
 End If
 UserForm2.Label6.Caption = ActiveCell.Value
 
 'Checks for full AF126
 If Sheets("Af126").Range("a21").Value <> "" Then
  Msg = MsgBox("AF126 if full. Can not continue until AF126 is printed." & Chr(13) & Chr(13) & "Press Yes to print.", vbYesNo, "AF126 Error")
  If Msg = vbYes Then
   Sheets("AF126").PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
   Sheets("AF126").Range("a6:j21").ClearContents
   UserForm2.Show
   Else
   End If
  Else
  UserForm2.Show
  [COLOR=#ff0000]MyDisableEvents = False[/COLOR]
 End If
 'End Checks for full AF126
 
 Else
 MsgBox "You must select one of the serial numbers to continue.", , "Select Serial Number"
End If
Application.ScreenUpdating = True
End Sub

finally add this

Private Sub textbox1_change()
'Code adapted from youtube.com/watch?v=e-3DrNiQ31I
Dim i As Integer
[COLOR=#ff0000]If MyDisableEvents Then Exit Sub
[/COLOR]
ListBox1.Clear

 

[wwbwb]

Added the lines. Listbox does not show at all.

 

[wwbwb]

I think I got it.

I changed the MyDisaleEvents = True to the following:

 If Worksheets("list").Range("e" & res).Value <> "" Then
  MyDisableEvents = True
  Else
  MyDisableEvents = False
 End If

I removed the MyDisabeEvents = False

I replaced the If MyDisableEvents then Exit Sub with:

If MyDisableEvents = True Then
 MyDisableEvents = False
 Exit Sub
 Else
 MyDisableEvents = False
End If