TotallyConfused;489936 Wrote:
Thank you for responding. There are over 350 lines I would have to go
through if I follow your code. This is what I have now and it seems to
work.
However, I am new to code in Excel and would like your opinion. Thank
you
very much .

Dim cCont As Control
If MsgBox("Do you really want to cancel?", vbYesNo, "Cancel Action") =
vbYes Then
For Each cCont In Me.Controls
If TypeOf cCont Is MSForms.CheckBox Then
cCont.Value = False
End If
If TypeOf cCont Is MSForms.TextBox Then
cCont.Value = ""
End If
If TypeOf cCont Is MSForms.ListBox Then
cCont.Value = ""
End If
If TypeName(cCont) = "ListBox" Then
cCont.ListStyle = 0
End If
Next cCont
End If
Me.ListBox4.ListStyle = 1
Me.ListBox5.ListStyle = 1
Me.txtFormDate = Format(Date, "mm/dd/yyyy")
Exit Sub

3 points:
1. I tried with 500+ lines and it didn't take significant time. But
your method is faster.
2. Rather than reset the two listboxes by name at the end of the code
back to liststyle=1, do it directly after setting it to
0: If TypeName(cCont) = "ListBox" Then
If cCont.MultiSelect = 1 Or cCont.MultiSelect = 2 And
cCont.ListStyle = 1 Then
cCont.ListStyle = 0
cCont.ListStyle = 1
Else
cCont.Value = ""
End If
End If

3. Using the ListStyle method I noticed that every time I cleared the
selections from the listbox in that way the listbox height got shorter
on the form, by about 1 line! Right until it was just 1 line high. An
Excel 'feature', no doubt. However, using your idea of losing the
selection by changing a property back and forth, I circumvented the
listbox shrinking with: If TypeName(cCont) = "ListBox"
Then
Select Case cCont.MultiSelect
Case 1: cCont.MultiSelect = 0: cCont.MultiSelect = 1
Case 2: cCont.MultiSelect = 0: cCont.MultiSelect = 2
Case 0: cCont.Value = ""
End Select
End If
which changes the multiselect property back and forth.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=134925