Listbox with Check Box Option??
 

Hi, I am trying to clear out all checkboxes, textboxes and listboxes with a
"cancel" button. With the help of this forum, I have the code below which
works great except for a couple of listboxes that have checkboxes (1-
fmliststyleoption). These two listboxes do not clear when I click on the
cancel button. Can someone please help me with this? Thank you.

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

End Sub

Listbox with Check Box Option??
 

Watch your typing. String comparisons are case sensitive.

If TypeName(cCont) = "ListBox" Then
cCont.ListIndex = -1
End If

ListBox with a capital B.

And I changed the the method for deselecting any items from the listbox.

I like this syntax to stop my typos:

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

It has the added benefit of being able to distinguis between textboxes on
worksheets--are they from the Drawing toolbar or from the control toolbox
toolbar.

TotallyConfused wrote:

Hi, I am trying to clear out all checkboxes, textboxes and listboxes with a
"cancel" button. With the help of this forum, I have the code below which
works great except for a couple of listboxes that have checkboxes (1-
fmliststyleoption). These two listboxes do not clear when I click on the
cancel button. Can someone please help me with this? Thank you.

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

End Sub

--

Dave Peterson

Listbox with Check Box Option??
 

Thank you Dave for your response. However, the ListBox checkboxes will not
clear with the code provided. I copied your code exactly and everything
else works fine except this listbox where I have it set to List style option
1. This option has checkboxes that will not uncheck when I click on the
cancel button. Can you please help? Thank you.

"Dave Peterson" wrote:

Watch your typing. String comparisons are case sensitive.

If TypeName(cCont) = "ListBox" Then
cCont.ListIndex = -1
End If

ListBox with a capital B.

And I changed the the method for deselecting any items from the listbox.

I like this syntax to stop my typos:

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

It has the added benefit of being able to distinguis between textboxes on
worksheets--are they from the Drawing toolbar or from the control toolbox
toolbar.

TotallyConfused wrote:

Hi, I am trying to clear out all checkboxes, textboxes and listboxes with a
"cancel" button. With the help of this forum, I have the code below which
works great except for a couple of listboxes that have checkboxes (1-
fmliststyleoption). These two listboxes do not clear when I click on the
cancel button. Can someone please help me with this? Thank you.

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

End Sub

--

Dave Peterson

Listbox with Check Box Option??
 

Any bets the problem list box(es) is/are multiselect ones, in which case
you have to go through each line:If TypeName(cCont) =
"ListBox" Then
If cCont.MultiSelect = 1 Or cCont.MultiSelect = 2 Then
For i = 0 To cCont.ListCount - 1
cCont.Selected(i) = False
Next i
Else
cCont.Value = ""
End If
End If


--
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

Listbox with Check Box Option??
 

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

"p45cal" wrote:


Any bets the problem list box(es) is/are multiselect ones, in which case
you have to go through each line:If TypeName(cCont) =
"ListBox" Then
If cCont.MultiSelect = 1 Or cCont.MultiSelect = 2 Then
For i = 0 To cCont.ListCount - 1
cCont.Selected(i) = False
Next i
Else
cCont.Value = ""
End If
End If


--
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

Listbox with Check Box Option??
 

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