Hide/Show Listbox in Worksheet

[Sthrncali]

I am seeing some strange behavior with a multi-select list box (ActiveX) that I have embedded in a worksheet.

I have a button that toggles the visible property of my list box to show/hide it from the user. The behavior I am seeing is that when I hide then show the list box, it won't let me select anything in it unless I toggle on design mode, then back off. Then I can make selections in the list box as normal. Then if I hit the button that hides it, it doesn't hide it remains on the screen allowing me to select/change it. Then again if I toggle design mode and back off, now the box goes away..

Is there a known issue or workaround to prevent this type of behavior?

 

[Andrew Poulsom]

I can't reproduce that behaviour in Excel 2003. What version of Excel are you using and what's your code?

 

[Sthrncali]

Excel 2007.

Code is

'Show Box

Sub Picture1_Click()
Sheet1.Shapes("ChartClose").Visible = msoTrue
Sheet1.Shapes("Chartbg").Visible = msoTrue
Sheet1.Shapes("SiteList").Visible = msoTrue
FillBox
Sheet1.Shapes("Chart1").Visible = msoTrue
End Sub

'Hide Box

Sub ChartClose_Click()
Sheet1.Shapes("ChartClose").Visible = msoFalse
Sheet1.Shapes("Chartbg").Visible = msoFalse
Sheet1.Shapes("SiteList").Visible = msoFalse
FillBox
Sheet1.Shapes("Chart1").Visible = msoFalse
End Sub

 

[Andrew Poulsom]

I used code like this:

Private Sub CommandButton1_Click()
    Me.ListBox1.Visible = Not Me.ListBox1.Visible
End Sub

My ListBox was from the Control Toolbox (as was the CommandButton).

 

[Sthrncali]

Word!

That worked.

Thank you!!