Prgrammatically added OLE Listbox, now cannot select from it

[happyheth]

Hi,

I have a macro that creates an OLEobject list box and popualtes it. Once the macro ends, I cannot select from the listbox unless I switch into design mode and back out.

I have a single activex button that runs this code.

Private Sub CommandButton1_Click()
Set lb1 = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", _
Link:=False, DisplayAsIcon:=False, Left:=10, Top:=currX, Width:=560, Height:=60)

With lb1.Object
.AddItem
.ColumnCount = 4
.ColumnWidths = "100;150;150;150"
.List(0, 0) = " "
.List(0, 1) = "Database"
.List(0, 2) = "Server"
.List(0, 3) = "Version"
End With
End Sub

Once complete, I what to select the row displayed but cannot. Help?

 

[GS]

Hi,

I have a macro that creates an OLEobject list box and popualtes it.
Once the macro ends, I cannot select from the listbox unless I switch
into design mode and back out.

I have a single activex button that runs this code.

Private Sub CommandButton1_Click()
Set lb1 =
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", _
Link:=False, DisplayAsIcon:=False, Left:=10, Top:=currX,
Width:=560, Height:=60)

With lb1.Object
.AddItem
.ColumnCount = 4
.ColumnWidths = "100;150;150;150"
.List(0, 0) = " "
.List(0, 1) = "Database"
.List(0, 2) = "Server"
.List(0, 3) = "Version"
End With
End Sub

Once complete, I what to select the row displayed but cannot. Help?

If you mean you want to select 1 of the subitems in the row.., you
can't! You can only select the 1st column and doing so should highlight
the entire row. Change you code so you have access to each item in its
own row...

With lb1.Object
.AddItem: .AddItem "Database": .AddItem "Server": .AddItem
"Version"
End With

...so you get...

blank (row1)
Database (row2)
Server (row3)
Version (row4)

OR
use a ComboBox (dropdown)

OR
use a DataValidation list!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[happyheth]

No that's not what I mean.

When the script exits I cannot click on any part of the listbox unless I click on 'design mode' twice. I can see a fat cross when I hover over the listbox, and if I click the cursor temporarily turns to an arrowed cross. But it doesn't select the rows, instead it seems to be in another mode altogether.

 

[Peter T]

Hi,

I have a macro that creates an OLEobject list box and popualtes it. Once
the macro ends, I cannot select from the listbox unless I switch into
design mode and back out.

I have a single activex button that runs this code.

Private Sub CommandButton1_Click()
Set lb1 = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", _
Link:=False, DisplayAsIcon:=False, Left:=10, Top:=currX,
Width:=560, Height:=60)

With lb1.Object
.AddItem
.ColumnCount = 4
.ColumnWidths = "100;150;150;150"
.List(0, 0) = " "
.List(0, 1) = "Database"
.List(0, 2) = "Server"
.List(0, 3) = "Version"
End With
End Sub

Once complete, I what to select the row displayed but cannot. Help?

Try toggling design mode with code, eg

Dim cb As CommandBarButton
Set cb = Application.VBE.CommandBars.FindControl(ID:=212)
cb.Execute
cb.Execute

Can also toggle the ribbon button with
CommandBars.ExecuteMso "DesignMode"
though it can be a bit fiddly to avoid the code terminating on that
particular call

Regards,
Peter T

 

[GS]

No that's not what I mean.

When the script exits I cannot click on any part of the listbox
unless I click on 'design mode' twice. I can see a fat cross when I
hover over the listbox, and if I click the cursor temporarily turns
to an arrowed cross. But it doesn't select the rows, instead it seems
to be in another mode altogether.

You need to enter design mode to create it, then leave design mode to
use it. That precludes 2 clicks are required!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[happyheth]

I've tried programmatically switch but it seems to end the script when you go into design mode.

 

[GS]

I've tried programmatically switch but it seems to end the script

when you go into design mode.

That's correct! What is it, exactly, that you're trying to accomplish?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[happyheth]

Its pretty simple. I'm trying to produce something I can give to an inexperienced user and they can click. I'd have used proper VB but my company wontbuy it.

I'm create a worksheet, adding a listbox, populating the listbox (for oracle). Then the user can select one or more entries from the listbox and they then perform other queries. But as I've said, when I finish running the first script, the user cannot immediate select the entries from the listbox. No very user friendly. The code I showed earlier demonstrates this. I'm sureI use a textbox or not a oleobject etc etc but I want to do it this way, and I dont think its too much to ask. I'm sure this must be something in my settings within excel that prevents an ole listbox from selecting after itsbeen created programmatically.

 

[Peter T]

Its pretty simple. I'm trying to produce something I can give to an
inexperienced user and they can click. I'd have used proper VB but my
company wont buy it.

I'm create a worksheet, adding a listbox, populating the listbox (for
oracle). Then the user can select one or more entries from the listbox and
they then perform other queries. But as I've said, when I finish running the
first script, the user cannot immediate select the entries from the listbox.
No very user friendly. The code I showed earlier demonstrates this. I'm sure
I use a textbox or not a oleobject etc etc but I want to do it this way, and
I dont think its too much to ask. I'm sure this must be something in my
settings within excel that prevents an ole listbox from selecting after its
been created programmatically.
===========================

Did you try what I suggested?

Peter T

 

[happyheth]

Peter T, your the man. Great Suggestion and it does work. The first bit. I dont suppose there is an explanation but I'm happy. Thanks.