for each loop

[Jefferson2512]

how to do it in for each loop??

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]

What control type have you used for your "ob" controls - i.e. Form Controls or ActiveX Controls?

 

[Jefferson2512]

What control type have you used for your "ob" controls - i.e. Form Controls or ActiveX Controls?

im using radio button for my '"ob"

 

[Trebor76]

im using radio button for my '"ob"

That doesn't answer my question

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:

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

 

[Jefferson2512]

sorry for not answering your question well just a beginner for vba though..
well, thanks for this .. more power to you

 

[Trebor76]

No problem and you're welcome

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

Regards,

Robert

 

[Jefferson2512]

No problem and you're welcome

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

Regards,

Robert

thanks for the knowledge..
btw i am using a userform not in sheet

 

[Trebor76]

btw i am using a userform not in sheet

So you changed my code to work?

 

[Jefferson2512]

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]

OK try this (though I don't think you can have more than one radio button selected):

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