I want to create a command button that will default back my listbox to "All"
when a user clicks the command button. How would I write the VBA code for
this button to have it select "All" from my listbox. I just want to create an
easy way for users to select that particular value.

That depends - is your listbox in a UserForm, or embedded in a worksheet? If
on a worksheet, is it created by the control toolbox or the forms toolbar?

If the list and commandbutton are in a UserForm or the listbox and
commandbutton are created using the control toolbox, just set the value of
the Listbox:

Private Sub CommandButton1_Click()
Me.ListBox1.Value = "all"
End Sub

Listbox name and the CommandButton1_Click depend on the actual names of the
listbox and command button you create.

If your listbox and command button are created w/the forms toolbar, assign
your button to a macro similar to:

Sub test()
ActiveSheet.Shapes("List Box 1").ControlFormat.ListIndex = 5
End Sub

where "all" is the 5th item in the list. Of course, change the name of the
list box to whatever yours is/will be called. If it's the only one on your
sheet, it is probably List Box 1.



"Secret Squirrel" wrote:

I want to create a command button that will default back my listbox to "All"
when a user clicks the command button. How would I write the VBA code for
this button to have it select "All" from my listbox. I just want to create an
easy way for users to select that particular value.

My listbox is embedded on my worksheet and was created using the control
toolbox. Your instructions worked great.

Thanks again for your help tonight!

"JMB" wrote:

That depends - is your listbox in a UserForm, or embedded in a worksheet? If
on a worksheet, is it created by the control toolbox or the forms toolbar?

If the list and commandbutton are in a UserForm or the listbox and
commandbutton are created using the control toolbox, just set the value of
the Listbox:

Private Sub CommandButton1_Click()
Me.ListBox1.Value = "all"
End Sub

Listbox name and the CommandButton1_Click depend on the actual names of the
listbox and command button you create.

If your listbox and command button are created w/the forms toolbar, assign
your button to a macro similar to:

Sub test()
ActiveSheet.Shapes("List Box 1").ControlFormat.ListIndex = 5
End Sub

where "all" is the 5th item in the list. Of course, change the name of the
list box to whatever yours is/will be called. If it's the only one on your
sheet, it is probably List Box 1.



"Secret Squirrel" wrote:

I want to create a command button that will default back my listbox to "All"
when a user clicks the command button. How would I write the VBA code for
this button to have it select "All" from my listbox. I just want to create an
easy way for users to select that particular value.