Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Unresponding ListBox on a worksheet (XL2003)

    I've added a ListBox to a worksheet programmatically, but am finding that it initially acts as though it's not enabled (but it is). After handing control back to the user, items can only be selected after the user activates a different worksheet and then re-activates the sheet in question. From that point on it behaves normally. Doing the same thing in the code does not have the same effect - it has to be done through the GUI.
    I can't find anything in the knowledge base. Has anybody come across the same thing, and if so, did you find any kind of work-around?
    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unresponding ListBox on a worksheet (XL2003)

    What method do you use to add the list box? AddFormControl or AddOLEObject?

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Unresponding ListBox on a worksheet (XL2003)

    Oops. I did mean to mention that it's an ActiveX control. The relevant code is:
    '** Add the Overlay ListBox...
    With wksData
    .OLEObjects.Add Classtype:="Forms.ListBox.1", _
    Left:=.Cells(1, cOverlay).Left, Top:=.Cells(1, cOverlay).Top + 12, Width:=180, Height:=41.25
    With .OLEObjects(.OLEObjects.Count)
    .Name = "lstOverlay"
    .ListFillRange = "tblOverlay"
    .Object.ListIndex = 0
    End With
    End With
    The list box *IS* correctly populated (with the items from the tblOverlay range) - with the first item selected - but a different item cannot be selected until the user first "visits" another sheet.
    Thanks for the help...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unresponding ListBox on a worksheet (XL2003)

    You can activate the list box: add the line

    .Activate

    immediately below the line

    .Object.ListIndex = 0

    If you don't want the list box to remain activated, add the following line:

    ActiveWindow..RangeSelection.Activate

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Unresponding ListBox on a worksheet (XL2003)

    Thks, but I tried that and still get the same behaviour. I thought it might be related to the fact that the listbox is bound to the workheet range, but the same thing happens when I populate it using the AddItem method.
    Any other ideas? I commented out code I have behind the sheet to rule that out as a factor. I'm on Windows XP Professional in case that's relevant.
    Regards,
    Colin.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unresponding ListBox on a worksheet (XL2003)

    I still suspect something else is interfering. I created a small demo based on your code; it works OK for me in Excel 2002 (XP) on Windows XP Home and in Excel 2003 on Windows XP Pro. What happens if you try it?

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    FreezePains (sic)

    Good idea, thanks. It works as it should, so I went back and narrowed down which piece of my code was interfering. It's tied into FreezePanes being switched on. It seems to be more of a GUI bug, though ... I found that even after I got it to initially work OK, I could still get it to revert to its original unresponding behaviour. I modified your attachment to demonstrate what I mean.

    Any other suggestions gratefully received, but I'm thinking it's something I probably have to live with...
    Many thanks for your help.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: FreezePains (sic)

    You could turn off FreezePanes at the begining of the code, then at the end, select the appropriate cell (F6) and turn on FreezePanes again. That works for me in the workbook you attached.

    Private Sub CommandButton1_Click()
    '** Add the Overlay ListBox...
    Dim wksData As Worksheet
    Dim cOverlay As Long
    Dim blnFreeze As Boolean
    Set wksData = Me
    cOverlay = 7
    blnFreeze = ActiveWindow.FreezePanes
    ActiveWindow.FreezePanes = False
    With wksData
    .OLEObjects.Add Classtype:="Forms.ListBox.1", _
    Left:=.Cells(1, cOverlay).Left, Top:=.Cells(1, cOverlay).Top + 12, _
    Width:=180, Height:=41.25
    With .OLEObjects(.OLEObjects.Count)
    .Name = "lstOverlay"
    .ListFillRange = "tblOverlay"
    .Object.ListIndex = 0
    .Activate
    End With
    End With
    Set wksData = Nothing
    Cells(6, 6).Select
    ActiveWindow.FreezePanes = blnFreeze
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •