Results 1 to 4 of 4

Thread: Hidden Combo

  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good morning

    I have a hidden combo that is populated as follows (by clicking Run in the VB editor) and used to work on about 9 different worksheets, I have added 2 items to the combo list and now it works in only one worksheet, 'Directors'

    Code:
    Sub Populate_Combo()
    
    With ActiveSheet.MyCombo
    .ListFillRange = Worksheets("ComboFill") _
    .Range("A2:A23").Address(external:=True)
    End With
    
    End Sub
    If I now activate the hidden combo in say my worksheet called 'Sales' it shows but (a) stays visible and (b) does not populate the cell with the combo selection.

    Stepping through the code in the 'Sales' worksheet it halts as shown below

    Code:
    Private Sub Worksheet_BeforeDoubleClick _
    (ByVal Target As Range, Cancel As Boolean)
    
    If Not Intersect(Range("D6:AI147"), Target) Is Nothing Then
    
    End If
    
    Cancel = True
    
    
    ActiveSheet.MyCombo.Visible = True
    End Sub

    But 'Sales' is the active sheet?

    Any ideas please
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Could you post a sample?
    There doesn't appear to be any code there that would populate a cell anyway.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Rory

    Sorry but I could not strip down a version enough to post and leave all of the funcionality going. I have 'dibbled and dabbled' and by running the sub when each of the worksheets have been open it seems to have done what I wanted it to do, one of those more luck than judgement moments.

    Thanks and Happy New Year to you and yours.

    Cheers
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    And to you too!

    Glad you got it working but, for the record, I would advise against using ActiveX controls (from the Control Toolbox) on worksheets if possible. They can be a little unstable and the Forms versions are generally preferable, IMO.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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