Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Loop Through Specific ActiveX Controls

Posted on 2013-06-03
13
Medium Priority
?
1,435 Views
Last Modified: 2013-07-15
Need to understand how to loop through specific active x controls ie textboxes which have a specific naming convention and using an interger suffix and loop through them.

For example

I have 8 textboxes all named textbox1, textbox2, texbox3 and so on.

For i = 1 to 8

 vTotal = Textbox & i  + Vtotal

Next i
0
Comment
Question by:kriskyk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39217362
Are the textboxes on a worksheet, or on a userform?
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 39217371
Sub sample()
Dim objX As Object
 
With ActiveSheet
    For Each objX In .OLEObjects
        If TypeName(objX.Object) = "TextBox" Then
            MsgBox objX.Name
            'DO STUFF HERE
        End If
    Next
End With

End Sub

Open in new window

0
 

Author Comment

by:kriskyk
ID: 39217372
Worksheet
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 13

Expert Comment

by:Shanan212
ID: 39217376
The sub I posted loops through all active- x objects named 'TextBox'. You may change this line to run through 1 - 8

For Each objX In .OLEObjects
0
 

Author Comment

by:kriskyk
ID: 39217384
In the above proposed answer the code is evaluating all objects in the active worksheet. I would prefer if it only evaluated the 8 in the example.
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 39217402
Sub sample()
Dim objX As Object, c As Long

For c = 1 To 8
    On Error Resume Next
    ActiveSheet.Shapes.Range(Array("TextBox" & c)).Select
    On Error GoTo 0
    MsgBox Selection.Name
    'Do stuff here
Next c

End Sub
0
 

Author Comment

by:kriskyk
ID: 39220539
Getting closer, i'm looking to be able to set and get the objects properties as well.

Also I noticed that it cannot find the object if it's grouped with other objects. (Select Object > Format Tab > Arrange Section > Group Button)

Dim i As Integer
Dim objX As Object
Dim vObjName As String
i = 1


For Each objX In Worksheets("PSF").OLEObjects
    If objX.Name = "cboSoftware" & i Then
        'Debug.Print objX.Value
        objX.Value = "Test"
        objX.AddItem "Test 2"
        i = i + 1
        If i = 11 Then Exit For
    End If
   
Next
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 39222328
I assume some of your columns/rows are grouped and are closed.

In this case, you need to ungroup.

If you know which columns/rows are grouped, then you can ungroup them using this.

    On error resume next
    Columns("H:N").Select            'or rows
    Selection.EntireColumn.Hidden = False
    On error goto 0

Open in new window


Put this code before going into the For-Loop
0
 

Author Comment

by:kriskyk
ID: 39223219
I think you miss-understood what I was saying by grouping.

The following menu path (Select Object > Format Tab > Arrange Section > Group Button)
 allows you to group active x objects so when you move one they move all othe items grouped together.


if someone could tell me how to loop through specific activex objects on a worksheet not  a form and set the objects properties i would be very appreciative.

Kindly,

kriskyk

Dim i As Integer
Dim objX As Object
Dim vObjName As String
i = 1


For Each objX In Worksheets("PSF").OLEObjects
    If objX.Name = "cboSoftware" & i Then
        'Debug.Print objX.Value
        objX.Value = "Test"
        objX.AddItem "Test 2"
        i = i + 1
        If i = 11 Then Exit For
    End If
    
Next 

Open in new window

0
 
LVL 13

Expert Comment

by:Shanan212
ID: 39223330
Try this

I am re-running through your code at the bottom to go through shapes that are not grouped. IF all of your shapes are grouped, then delete the block at the bottom

Sub GroupedOnWorksheet()

    Dim Shp As Excel.Shape
    Dim shpRng As Excel.ShapeRange
    Dim arrShps() As Variant 'required
    Dim x As Long, N As Long
    Dim i As Integer, objX As Object
    Dim vObjName As String, m As Long
    i = 1

    
    For Each Shp In ActiveSheet.Shapes
    
        If Shp.Type = msoGroup Then
            x = Shp.GroupItems.Count
            ReDim arrShps(1 To x)
            For N = 1 To x
                arrShps(N) = Shp.GroupItems(N).Name
            Next
            Shp.Ungroup
            Set shpRng = ActiveSheet.Shapes.Range(arrShps)

            For m = 1 To UBound(arrShps)
                If arrShps(m) = "cboSoftware" & m Then
                    'Debug.Print objX.Value
                    objX.Value = "Test"
                    objX.AddItem "Test 2"
                    i = i + 1
                    If i = 11 Then Exit For
                End If
            Next m
        
            shpRng.Regroup

        End If
    Next
    
    For Each objX In Worksheets("Routing").OLEObjects
        If objX.Name = "cboSoftware" & i Then
                'Debug.Print objX.Value
                objX.Value = "Test"
                objX.AddItem "Test 2"
                i = i + 1
                If i = 11 Then Exit For
        End If
    Next objX
End Sub

Open in new window

0
 

Author Comment

by:kriskyk
ID: 39223687
Sitll having trouble setting the objects properties.

Added a combobox to the worksheet that is not grouped.

The debug.print objX.name returned combobox22 as expected

objx.value = "Test" 'Errors out: Run time error 438 Object doesn't support this property or method.

Separately i tried

Worksheets("PSF").ComboBox22.value = "test" and was succesfully.


Dim objX As Object

For Each objX In Worksheets("PSF").OLEObjects
    Debug.Print objX.Name
    objX.Value = "Test"
Next objX

Open in new window

0
 
LVL 13

Accepted Solution

by:
Shanan212 earned 2000 total points
ID: 39223757
Actually I am getting an error using your bottom code block as well.

Try this

ActiveSheet.OLEObjects(objX.Name).ListFillRange = "myData"

^ So make a named range out of the list you want to populate the combo box with. Call that myData and it works
0
 

Author Closing Comment

by:kriskyk
ID: 39328458
Never could get it to work....
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question