Solved

Loop Through Specific ActiveX Controls

Posted on 2013-06-03
13
1,083 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now