Listbox Index Problem

T

Todd Huttenstine

With Worksheets("Wkly Renewals").ListBox1
For LstBxItem = 0 To .ListCount - 1
If .Selected(LstBxItem) Then
MsgBox .List(LstBxItem)
MsgBox LstBxItem.Value
end with
When it hits the line MsgBox .List(LstBxItem) it displays
the value. In this case its index item 0 so it displays
the index item 0 which is the value "Jan Week 1" When it
gets to the MsgBox LstBxItem.Value line, I get the error
Runtime error 424 object required.

Why is this?
 
C

Chip Pearson

Todd,

LstBxItem is an integer or long, not an object, and thus does not
have a Value property. This would be clear if you explicitly
declare your variables.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
B

Bob Phillips

Todd,

LstBxItem is just a index variable, it doesn't have a value property. What
are you trying to show with the second MsgBox, as it seems you are trying to
show the same thing to me. If you want the in dex itself, just use

Msgbox LstBxItem

maybe plus 1 if yhou don 't want it starting at 0

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Todd Huttenstine

Ok I didnt know that. The value of .List(LstBxItem) was
what I needed for the pivot table data field and I was
wondering why the pivot table couldnt see this value. I
was using the msgbox to try and help me figure it out. So
what I did was set the variable PvtLstBxItem = .List
(LstBxItem). This way it was converted I guess to an
object??? I then used that variable in the pivot table
code and it worked. below is the code I used...


With Worksheets("Wkly Renewals").ListBox1
For LstBxItem = 0 To .ListCount - 1
If .Selected(LstBxItem) Then
PvtCounter = PvtCounter + 1
PvtLstBxItem = .List(LstBxItem)
MsgBox PvtLstBxItem

With ActiveSheet.PivotTables("PivotTable4").PivotFields
(PvtLstBxItem)
.Orientation = xlDataField
.Caption = PvtLstBxItem
.Position = PvtCounter
.Function = xlSum
End With

End If
Next LstBxItem
End With
 
Top