MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds


  Asked By: Loretta    Date: Oct 31    Category: MS Office    Views: 1361

My goal is to create a spreadsheet that contains listboxes under specific
column names (D). I set-up a listbox in D2 to test my code. I keep
getting a Run-time error 91: Object variable or With block variable not
set. Could someone please review my code and let me know what I need to
do? Immediate assistance will be greatly appreciated.



4 Answers Found

Answer #1    Answered By: Rachel Fischer     Answered On: Oct 31

There is no code  included in the message to review.

However, a common reason for this error  is that you have tried to assign an
object reference to a variable  without using the "Set" keyword.

Answer #2    Answered By: Shannon Johnson     Answered On: Oct 31

Here is the code  for review:

Private Sub ListBox1_Click()

Dim i As Integer, iNumberSelected As Integer
'Create an array to hold the selected items.
ReDim sSelection(1 To ActiveDialog.Evaluate("ListBox1").ListCount) As

With ActiveDialog.ListBoxes("ListBox1").List = Array("CSE", "LAW")
'Add item to sSelection if its item is True
For i = 1 To ActiveDialog.Evaluate("ListBox1").ListCount
If ActiveDialog.Evaluate("ListBox1").Selected(i) = True Then
'Add the item to sSelection.
sSelection(iNumberSelected) =
End If
Next i

If iNumberSelected <> 0 Then
'Trim off elements not used.
ReDim Preserve sSelection(iNumberSelected)
'Return an array containing the selected item.
vSelection = sSelection
'Return False if no items were selected.
vSelection = False
End If
End With

End Sub

Answer #3    Answered By: Adelgiese Fischer     Answered On: Oct 31


is failing on my Excel 2000. In fact, ActiveDialog evaluates to Nothing.

Seeing you are in a click event for ListBox1, you simply need to use its name -
no need to try to look it up. E.g.

ReDim sSelection(1 To ListBox1.ListCount) As Single

The with statement is bad too. Even if I change it to

With ListBox1.List = Array("CSE", "LAW")

It still fails with a type mismatch. With statements can't be assignment
statements, this is actually trying to use the = as an equality check. But you
can't do an equality check between List and an Array.

No idea why you have a with statement anyway, you aren't making use of it.

I also note that you have a variable  called vSelection that is never defined.
Additionally, you set it in two statements. The first sets it to an array
(sSelection); the second sets it to false. This is not very nice code.

Lastly, please do not put "Urgent" in your message. While it might possibly be
urgent to you, it certainly isn't to us.

Answer #4    Answered By: Oliver Jones     Answered On: Oct 31

Spot-on responses on all counts. I hope it's helpful to you Mr/Mrs
Pulliam. If you're still having trouble, please ask.

Didn't find what you were looking for? Find more on ListBox Or get search suggestion and latest updates.