Create listbox with code but can't select any items in list box

[OssieMac]

I actually posted this in a previous thread about a different issue that Dave
Peterson answered but because it is really an entirely different question I
thought it might be advisable to re-post in a separate thread.

Refer to my code example below with comments.

I create a list box on a worksheet using code but I am unable to then select
any of the items in the list box unless I manually turn Design Mode On then
Off again.

Therefore I thought that a work around would be to turn Design Mode On then
Off again with code. However, I can turn it On with code but have not been
successful in turning it Off.

I will very much appeciate any help I can get to overcome this problem.

Private Sub CommandButton2_Click()
Dim lListBox As Double
Dim tListBox As Double
Dim hListBox As Double
Dim wListBox As Double
Dim lstBox As OLEObject
Dim i As Long

With Me
lListBox = .Cells(2, 2).Left
tListBox = .Cells(2, 2).Top
wListBox = 100
hListBox = 150

Set lstBox = .OLEObjects.Add _
(ClassType:="Forms.ListBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=lListBox, _
Top:=tListBox, _
Width:=wListBox, _
Height:=hListBox)

With lstBox.Object
For i = 1 To 10
.AddItem "Test" & i
Next i
.MultiSelect = fmMultiSelectMulti
End With

End With

Set lstBox = Nothing

'**********************************
'Tested the following code with minimal success
'Following line turns Design Mode ON in xl2007
Application.CommandBars.ExecuteMso ("DesignMode")

'Following line turns Design Mode ON in xl2002
'(Also works in xl2007)
'Application.CommandBars.FindControl(ID:=1605).Execute

'Following lines turn Design Mode OFF in xl2002
'but will only work if run in a separate sub
'after this sub has finished.

'With CommandBars("Exit Design Mode").Controls(1)
' If .State = msoButtonDown Then .Execute
'End With
'*************************************

End Sub

 

[Bob Phillips]

Put this in a standard code module

Public Sub TurnDesignModeOff()
With CommandBars("Exit Design Mode").Controls(1)
If .State = msoButtonDown Then .Execute
End With
End Sub

and change the button code to

Private Sub CommandButton2_Click()
Dim lListBox As Double
Dim tListBox As Double
Dim hListBox As Double
Dim wListBox As Double
Dim lstBox As OLEObject
Dim i As Long

With Me
lListBox = .Cells(2, 2).Left
tListBox = .Cells(2, 2).Top
wListBox = 100
hListBox = 150

Set lstBox = .OLEObjects.Add _
(ClassType:="Forms.ListBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=lListBox, _
Top:=tListBox, _
Width:=wListBox, _
Height:=hListBox)

With lstBox.Object
For i = 1 To 10
.AddItem "Test" & i
Next i
.MultiSelect = fmMultiSelectMulti
End With

End With

Set lstBox = Nothing

Application.OnTime Now() + TimeSerial(0, 0, 1), "TurnDesignModeOff"

Application.CommandBars.FindControl(ID:=1605).Execute
End Sub

HTH

Bob

 

[p45cal]

re:"but I am unable to then select any of the items in the list bo
unless I manually turn Design Mode On then Off again."

I found that activating another sheet then re-activating the one wit
the list box in also allowed selections to be made in the listbox.


Code
-------------------

Sheets("update").Activate '"update" is just another sheet in my workbook
Me.Activate

-------------------

Trying to enclose these statements in an Application.ScreenUpdating
True/False to mitigate screen flicker just made things worse.. you coul
select things but couldn't see what you were selecting until after you'
selected them.

If this had been on a userform and exhibited the same behaviour I'
have been looking at a .repaint statement. I can't find an equivalen
for a sheet or for the control itself. So, for the moment, this wor
around should do - let's hope your workbook doesn't only have 1 sheet
I'm sure there'll be a better solution - this is just the first thing
tried.

..but there are lots of problems with activex controls on a worksheet
You could use a listbox from the Forms toolbar instead, but you wouldn'
have the same flexibility and it would insist on having a vertica
scroll bar, needed or not

 

[p45cal]

Better solution

lstBox.Activat

before setting it to Nothing

 

[OssieMac]

Hi Bob,

Excellent! So simple now; I never gave it a thought to use the timer to run
the second sub to turn it off. Thank you for your efforts.

 

[Varne]

Hi!

Just a trick;

On Error Resume Next
Application.CommandBars.ExecuteMso ("Something")
On Error GoTo 0

That will turn off the design mode!

M Varnendra

 

[OssieMac]

Bullseye! That was some real sharp shooting. Thankyou.

 

[OssieMac]

Thanks for your effort Varne. It works in conjunction with Bob Phillips
answer. However, I think that p45cal's answer is the one to use.