+ Reply to Thread
Results 1 to 8 of 8

Indexing Through ACTIVEX Comboboxes

  1. #1
    Registered User
    Join Date
    07-06-2012
    Location
    Arkansas, USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Indexing Through ACTIVEX Comboboxes

    I am relatively new to VBA in Excel and it has been a few years since I had VB and C+. I am needing to loop through several items including ACTIVE X comboboxes and checkboxes. Here is a sample of what is going on:
    If Len(Range("A3").Value) = 0 Then
    Me.ComboBox3.Visible = False
    Me.ComboBox3.Value = ""
    Me.CheckBox3.Visible = False
    Me.ComboBox13.Visible = False
    Me.ComboBox13.Value = ""
    Me.CheckBox13.Visible = False
    Else
    If Len(Range("f3").Value) = 0 Then
    Me.ComboBox3.Visible = True
    Me.ComboBox3.Value = ""
    Me.CheckBox3.Visible = True
    Me.CheckBox3.Value = False
    Else
    Me.ComboBox3.Visible = False
    Me.ComboBox3.Value = ""
    Me.CheckBox3.Visible = False
    End If
    If Len(Range("I3").Value) = 0 Then
    Me.ComboBox13.Visible = True
    Me.ComboBox13.Value = ""
    Me.CheckBox13.Visible = True
    Me.CheckBox13.Value = False
    Else
    Me.ComboBox13.Visible = False
    Me.ComboBox13.Value = ""
    Me.CheckBox13.Visible = False
    End If
    End If

    I have several comboboxes and checkboxes throughout the sheet and need to index through them to shorten the code and make it easier to follow. I want to do something like:
    dim x as integer
    x=3 'start with combobox3 to make combobox relative to row
    do
    Me.("CheckBox"&x).Visible = False
    'do more stuff
    x+1
    do until x=50

    I may have nomenclature wrong, however I hope you understand the direction I am heading.
    loop

  2. #2
    Registered User
    Join Date
    06-19-2012
    Location
    Canada Eh
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    40

    Re: Indexing Through ACTIVEX Comboboxes

    Try something like:
    Please Login or Register  to view this content.
    EF killed Mordred

    46 & 2 is just ahead of me!

  3. #3
    Registered User
    Join Date
    07-06-2012
    Location
    Arkansas, USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Indexing Through ACTIVEX Comboboxes

    I tried that before posting and again after your suggestion. I get compile error method or data not found on the .controls portion. I think this would work fine for a userform, just not an ACTIVEX control. Thanks for the thoughts though keep em coming.

  4. #4
    Registered User
    Join Date
    06-19-2012
    Location
    Canada Eh
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    40

    Re: Indexing Through ACTIVEX Comboboxes

    Sorry, you posted ActiveX and my little mind ignored that. My bad.

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Indexing Through ACTIVEX Comboboxes

    Hemimud,

    For ActiveX controls:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Registered User
    Join Date
    07-06-2012
    Location
    Arkansas, USA
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Indexing Through ACTIVEX Comboboxes

    Tigeravatar, forgive me I am not current on the OLEobjects so I need a little guidance as to what the code is doing so I inturn know how to properly manipulate it for my given situation. If you could descripe what is happening would help tremendously. It appears as the code you have given will run through each object on the sheet and preform a preset function, but I do not know.Thanks for replying!

  7. #7
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Indexing Through ACTIVEX Comboboxes

    The code I provided is meant to be an example that you can adapt. It simply goes through each ActiveX object on the worksheet. In code, ActiveX objects are called OLEobjects. And then for each OLEobject, it displays a messagebox containing the object's name, type, and value. The code shows how to get each object's name, type, and value so that you can work with each object as needed.

    Give it a try to adapt your code with that information, and post back with your progress if you get stuck.

  8. #8
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Indexing Through ACTIVEX Comboboxes

    Quote Originally Posted by Hemimud View Post
    I think this would work fine for a userform, just not an ACTIVEX control.
    FortySixAndTwo's code is for ActiveX controls. The controls on a userform are the same as you are using, the code fails since there is no controls collection of the Worksheet Object.

    The other type of controls are forms controls, which are IMO better choices for use on a worksheet as they tend to be more stable than ActiveX controls

    Using tiger's explanation, you can do this:
    Please Login or Register  to view this content.
    Last edited by Kyle123; 07-13-2012 at 11:48 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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