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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
What control type have you used for your "ob" controls - i.e. Form Controls or ActiveX Controls?
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
No problem and you're welcome :)

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

Regards,

Robert
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top