+ Reply to Thread
Results 1 to 10 of 10

ActiveX combo box control populating many of them

  1. #1
    Registered User
    Join Date
    02-17-2011
    Location
    Providence, RI
    MS-Off Ver
    Excel 2007
    Posts
    6

    ActiveX combo box control populating many of them

    Hello All,

    I've searched the internet all day for a solution to this but I cannot find an answer. If anyone knows the proper syntax to do this I would be greatfull. In any case this is what I am trying to do conceptually. Keep in mind I am not using a form control but an ActiveX combo box control in excel 2007.

    I want to code some vba for excel. Specifically I have several combo boxes in a spreadsheet that I want to populate with the same items lets say:

    Item1
    Item2
    Item3

    I want this to populate when the workbook opens. So far no problem. I attach the following in the Workbook_Activate routine and it works fine:

    Please Login or Register  to view this content.
    But this doesn’t work. The ComboBox(XLoopVar) portion is obviously wrong. But I can’t seem to find the correct syntax to do this anywhere. I find some VB examples but they don’t seem to work with the VBA in Excel and the forms examples don't seem to have the coresponding additem method with the activex control. Has anyone attempted this before and know of a way to do this?

    Thanks,
    Eric
    Last edited by pike; 02-18-2011 at 02:33 AM. Reason: code tags for newbie pm reminder to read the rules

  2. #2
    Registered User
    Join Date
    04-14-2010
    Location
    NZ
    MS-Off Ver
    2007,2010
    Posts
    86

    Re: ActiveX combo box control populating many of them

    Found Here: http://excel.bigresource.com/Track/excel-BnCrXlpA/

    Adjusted to suit comboboxes:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    02-17-2011
    Location
    Providence, RI
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: ActiveX combo box control populating many of them

    Actually that code seems to make sense. But it generates a runtime error 70 permission denied error.

  4. #4
    Registered User
    Join Date
    02-17-2011
    Location
    Providence, RI
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: ActiveX combo box control populating many of them

    Ok - a fellow colleague of mine came up with a workable solution:
    Dim i As Integer
    For i = 1 To 2
    Dim obj As OLEObject

    Set obj = ActiveSheet.OLEObjects("ComboBoxTeam1Player" & i)

    obj.ListFillRange = "A1:A2"


    Next

    But the additem method still will not work and is unavailable. So it looks as if the additem method is my problem. It is just not doable. I can work with the named range - but I find it curious to have a combo box and not be able to populate it with the additem method. A non populated combo box is, well, fairly useless.

  5. #5
    Forum Contributor
    Join Date
    02-07-2011
    Location
    netherlands
    MS-Off Ver
    Excel 2003
    Posts
    128

    Re: ActiveX combo box control populating many of them

    <Fixed>
    <Placed it in the workbook not in module>


    i have a diffrent question if i may interupt this for short.
    i got problems with the triggering event of the opening of workbook
    Please Login or Register  to view this content.
    even tough i got m ysecurity on designers mode the script wont trigger.

    is this because i placed this script i na module?
    i need to place this script somewhere else like in a userform?

    tx in advance
    Last edited by vin1; 02-18-2011 at 02:10 PM.

  6. #6
    Registered User
    Join Date
    04-14-2010
    Location
    NZ
    MS-Off Ver
    2007,2010
    Posts
    86

    Re: ActiveX combo box control populating many of them

    @whineywright
    Are you using Excel 07?
    Have you got the workbook password protected or something?

    Testing the code i posted above works fine for me whether it be in Workbook_activate or Workbook_Open.

    I've attached spreadsheet with the above code
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-17-2011
    Location
    Providence, RI
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: ActiveX combo box control populating many of them

    yes - Office 2007. I generate a runtime error 70 when adapting your code. I went with the other code and got around it. Now if I can find a way to stop the combo boxes from mmoving on the sheet when I hide and unhide cells I'll be good to go.

  8. #8
    Registered User
    Join Date
    02-17-2011
    Location
    Providence, RI
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: ActiveX combo box control populating many of them

    Your's doesn't generate the error and seems to work. However, you passworded the sheet so I cannot see your code and cannot tell if I just "Addapted" it wrong.

    Eric

  9. #9
    Registered User
    Join Date
    02-17-2011
    Location
    Providence, RI
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: ActiveX combo box control populating many of them

    Vin,

    I am by no means an expert. But you should place that in the workbook portion. Not so sure if the module would work especially if you make it private. The action is attached to the workbook object.

    Eric

  10. #10
    Registered User
    Join Date
    04-14-2010
    Location
    NZ
    MS-Off Ver
    2007,2010
    Posts
    86

    Re: ActiveX combo box control populating many of them

    Quote Originally Posted by whineywright View Post
    Your's doesn't generate the error and seems to work. However, you passworded the sheet so I cannot see your code and cannot tell if I just "Addapted" it wrong.

    Eric
    Whoops, sorry bout that, thought i'd taken it off before posting.
    At any rate the password = abc

    What adaptations are you trying to make?

    To stop them from moving, set the Placement value of each combobox to 3 or xlFreeFloating
    Last edited by Deamo; 02-26-2011 at 03:37 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1