Solved

Loop Through Specific ActiveX Controls

Posted on 2013-06-03
13
1,155 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

786 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