Listbox

[ranswrt]

I have a listbox on a worksheet. When I leave the worksheet and go to
another worksheet or go to a userform from the worksheet and return back, how
do I make it so that none of the items in the listbox are selected?
Thanks

 

[JLGWhiz]

If it is from the Control Toolbox and is single select:

Listbox1.ListIndex = -1

 

[ranswrt]

I tried what you suggested and got

Run-time error '424':
object required

I tried

Sheets("Home").OLEObjects("ListBox1").ListIndex = -1
'Home' is the sheet that contains 'ListBox1'

I got this error

Run-time error '438':
Object doesn't support this property or method.

I put the listbox in using the control toolbox. Any Ideas as to what I am
doing wrong?

 

[JLGWhiz]

Yes, I gave you as much as you gave me. You didn't say where it was located.
But by now, I thought you would know that you must specify the parent object
such as a sheet or a UserForm.

 

[JLGWhiz]

Sheerts("Home").ListBox1.ListIndex = -1

Will work with a Control Toolbox listbox but it does not go blank, it just
gives it a Null value. You can include a blank cell in your rowsource for
the first cell and use

Sheets("Home").ListBox1.ListIndex = 0

That will select the empty cell to display.

 

[ranswrt]

Thanks I'll try that.

Sheerts("Home").ListBox1.ListIndex = -1

Will work with a Control Toolbox listbox but it does not go blank, it just
gives it a Null value. You can include a blank cell in your rowsource for
the first cell and use

Sheets("Home").ListBox1.ListIndex = 0

That will select the empty cell to display.

 

[ranswrt]

I know there is an easy answer to this but I'm not getting it. I did a real
simple code to check this

Sub testlistbox()
Dim a As String
Sheets("Home").OLEObjects("ListBox1").ListIndex = -1
a = Sheets("Home").OLEObjects("ListBox1").Value
MsgBox (a)
End Sub

When I run this I get an error on the first line. I put a ' to skip the
first line and I get the same error. When I run this there is a item
highlighted in the listbox. The error I get in the two instances is:

Run-time error '438':

Object doesn't support this property or method.

I know there is an easy solution, but what am I doing wrong?

 

[Dave Peterson]

Worksheets("Home").OLEObjects("ListBox1").Object.ListIndex = -1

But if you set the .listindex to -1, then the next line (.value) won't work.

Option Explicit
Sub testlistbox()
Dim a As String

With Worksheets("Home").OLEObjects("ListBox1").Object
.ListIndex = -1 'or 3
If .ListIndex < 0 Then
a = "Nothing chosen"
Else
a = .Value
End If
End With
MsgBox a
End Sub

 

[ranswrt]

Thanks that explains it better

Worksheets("Home").OLEObjects("ListBox1").Object.ListIndex = -1

But if you set the .listindex to -1, then the next line (.value) won't work.

Option Explicit
Sub testlistbox()
Dim a As String

With Worksheets("Home").OLEObjects("ListBox1").Object
.ListIndex = -1 'or 3
If .ListIndex < 0 Then
a = "Nothing chosen"
Else
a = .Value
End If
End With
MsgBox a
End Sub