Solved

Loop Through Specific ActiveX Controls

Posted on 2013-06-03
13
1,289 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
Windows running painfully slow? Try these tips..

Stay away from Speed Up Computer Programs that do more harm than good.
Try these tips instead.
Step by step instructions in trouble shooting Windows Performance issues.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

738 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