Listbox selected state initialisation problem

[Johnny C]

I'm setting up a macro to strip names out of sheets (some of the sheets have hundred of names which point to defunct spreadhseets).

I've set up a 2 column listbox with multiselection which populates okay. When I added a line to automatically select one of the rows if the name text had a '#REF' in, it crashes with error 380 - Could not set the selected property; invalid property value

The line it's bombing out on is
If InStr(1, n, "#REF", vbTextCompare) Then ListBox1.Selected(tempnum) = True

The instr works okay, but even on it's own the "ListBox1.Selected(tempnum) = True" line causes the error. According to my VBA bible it should have a boolean value (false by default) and you can set it.

When I wrote the line and closed the parenthesis it offered the ' = True' as one of two options (the other being false of course).

Can anyone give me a clue what I've done wrong please?

Code is:-

Private Sub UserForm_Initialize()

With RedundantNamesForm.ListBox1
.RowSource = ""
.ColumnWidths = "150;"
End With

Dim NamedRanges()

Dim NameCounter%
NameCounter = 0
For Each n In ActiveWorkbook.Names
NameCounter = NameCounter + 1
Next

ReDim NamedRanges(1 To NameCounter, 1 To 2)

NameCounter = 0
For Each n In ActiveWorkbook.Names
NameCounter = NameCounter + 1
NamedRanges(NameCounter, 1) = n.Name
NamedRanges(NameCounter, 2) = n
If InStr(1, n, "#REF", vbTextCompare) Then ListBox1.Selected(NameCounter) = True
Next n

ListBox1.List = NamedRanges

End Sub

 

[Norie]

Johnny

Is the listbox populated when you run this code?

You appear to be populating it at the end here.

ListBox1.List = NamedRanges

So when you run the previous code there's nothing in the listbox to be selected.

Unless I'm missing something.

 

[Johnny C]

Dozy t**t, the listbox.list wasn't set up

 

[Johnny C]

Okay, got it working now. I've also got some buttons to select anything with a #Ref in and also to select external links (for where there are conflicting names).

I am having trouble accessing the second column in the list; I tried using BoundColumn=2 and rebuilding the list but it still only gives me the range name, not the range it points to.

Is there a way I can access the second column data directly? At the mo I'm building up the array again each time (not really a problem as no-one else can access the spreadsheet whilst I've got it open) but it's a bit of a crude way to do it, if I could get the range sting from the listbox directly it would make it far simpler.