Solved

Loop Through Specific ActiveX Controls

Posted on 2013-06-03
13
1,360 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

628 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