for each loop

Jefferson2512

Board Regular
Joined
Sep 16, 2019
Messages
53
how to do it in for each loop??
Code:
Private Sub awp()
    Set wks = Sheet9
    If wks.Range("I3").Offset(0, 0).Text = "Yes" Then
    obYes.Value = True
    ElseIf wks.Range("I3").Offset(0, 0).Text = "No" Then
    obNo.Value = True
    ElseIf wks.Range("I3").Offset(0, 0).Text = "N/A" Then
    obNa.Value = True
    End If
    If wks.Range("J3").Offset(0, 0).Text = "Yes" Then
    obYes1.Value = True
    ElseIf wks.Range("J3").Offset(0, 0).Text = "No" Then
    obNo1.Value = True
    ElseIf wks.Range("J3").Offset(0, 0).Text = "N/A" Then
    obNa1.Value = True
    End If
    If wks.Range("K3").Offset(0, 0).Text = "Yes" Then
    obYes2.Value = True
    ElseIf wks.Range("K3").Offset(0, 0).Text = "No" Then
    obNo2.Value = True
    ElseIf wks.Range("K3").Offset(0, 0).Text = "N/A" Then
    obNa2.Value = True
    End If
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,481
What control type have you used for your "ob" controls - i.e. Form Controls or ActiveX Controls?
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,481
im using radio button for my '"ob"
That doesn't answer my question :confused:

Controls (in your case a worksheet radio button control) can either be a Form or ActiveX control. The code to set each is different. The following assumes Form controls were used:

Code:
Option Explicit
Private Sub awp()

    Dim rngMyCell As Range
    Dim strMyShapeName As String
    Dim ws As Worksheet
        
    Application.ScreenUpdating = False
    
    Set ws = Sheet9
    
    For Each rngMyCell In ws.Range("I3,J3,K3")
        Select Case rngMyCell.Address
            Case Is = "$I$3"
                strMyShapeName = Evaluate("VLOOKUP(" & rngMyCell.Address & ",{""Yes"",""obYes"";""No"",""obNo"";""N/A"",""obNa""},2,False)")
            Case Is = "$J$3"
                strMyShapeName = Evaluate("VLOOKUP(" & rngMyCell.Address & ",{""Yes"",""obYes1"";""No"",""obNo1"";""N/A"",""obNa1""},2,False)")
            Case Is = "$K$3"
                strMyShapeName = Evaluate("VLOOKUP(" & rngMyCell.Address & ",{""Yes"",""obYes2"";""No"",""obNo2"";""N/A"",""obNa2""},2,False)")
        End Select
        ws.Shapes(strMyShapeName).OLEFormat.Object.Value = True 'Form Control
    Next rngMyCell
    
    Set ws = Nothing
    
    Application.ScreenUpdating = True

End Sub
 
Last edited:

Jefferson2512

Board Regular
Joined
Sep 16, 2019
Messages
53
sorry for not answering your question well :( just a beginner for vba though..
well, thanks for this .. more power to you
 
Last edited by a moderator:

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,481
No problem and you're welcome :)

Have a look here about what I meant re type of control.

Regards,

Robert
 

Jefferson2512

Board Regular
Joined
Sep 16, 2019
Messages
53
So you changed my code to work?
i didn't man .. i'm still waiting for answers ..
i'd said thanks to you not because you helped me but i said thanks to you because of the time and effort that you've spent to this thread :):):):)
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,481
OK try this (though I don't think you can have more than one radio button selected):

Code:
Option Explicit
Private Sub awp()
    
    Dim rngMyCell As Range
    Dim strMyCtrl As String
    Dim ws As Worksheet
        
    Application.ScreenUpdating = False
    
    Set ws = Sheet9
    
    For Each rngMyCell In ws.Range("I3,J3,K3")
        Select Case rngMyCell.Address
            Case Is = "$I$3"
                strMyCtrl = Evaluate("VLOOKUP(" & rngMyCell.Address & ",{""Yes"",""obYes"";""No"",""obNo"";""N/A"",""obNa""},2,False)")
            Case Is = "$J$3"
                strMyCtrl = Evaluate("VLOOKUP(" & rngMyCell.Address & ",{""Yes"",""obYes1"";""No"",""obNo1"";""N/A"",""obNa1""},2,False)")
            Case Is = "$K$3"
                strMyCtrl = Evaluate("VLOOKUP(" & rngMyCell.Address & ",{""Yes"",""obYes2"";""No"",""obNo2"";""N/A"",""obNa2""},2,False)")
        End Select
        Controls(strMyCtrl).Value = True
    Next rngMyCell
    
    Set ws = Nothing
    
    Application.ScreenUpdating = True

End Sub
Robert
 

Forum statistics

Threads
1,085,474
Messages
5,383,896
Members
401,863
Latest member
Sisma

Some videos you may like

This Week's Hot Topics

Top