how to transfer datas in sheet to my radio button userform ??

[Jefferson2512]

    Dim wks As Worksheet    Set wks = Sheet9


    If wks.Range("BO3").Offset(0, 0).Text = "Yes" Then
    apeyes.Value = True
    ElseIf wks.Range("BO3").Offset(0, 0).Text = "No" Then
    apeno.Value = True
    End If
    If wks.Range("BP3").Offset(0, 0).Text = "Yes" Then
    apeyes1.Value = True
    ElseIf wks.Range("BP3").Offset(0, 0).Text = "No" Then
    apeno1.Value = True
    End If

 

[My Aswer Is This]

Not sure I see a question here.
You do not nee to use:

[LEFT][COLOR=#333333][FONT=monospace].Offset(0, 0)[/FONT][/COLOR][/LEFT]

Your offsetting by nothing.

And only one Option Button on a UserForm can be set to true.
Unless you put the option buttons into different Frames.

 

[Jefferson2512]

ahh okay .. i put my option buttons in 1 frame only ..
but how to retrieve my data to my option button if i will set only one option button ? can you give me some example on how to retrieve my data to my option button ?

 

[My Aswer Is This]

You would need to put Apeyes and Apeno in a Frame
And Put the other two in a separate Frame

So you would have two different Frames
Your script could look like this:

Private Sub CommandButton1_Click()
Dim wks As Worksheet
'Modified  9/18/2019  1:36:25 AM  EDT
Set wks = Sheet9
    If wks.Range("BO3").Text = "Yes" Then apeyes.Value = True
    If wks.Range("BO3").Text = "No" Then apeno.Value = True
    If wks.Range("BP3").Text = "Yes" Then apeyes1.Value = True
    If wks.Range("BP3").Text = "No" Then apeno1.Value = True
End Sub

 

[Jefferson2512]

i'd try the code but still won't work.. i think because of my buttons who controls my multi page ..

here is my system can you help me about the errors ?
https://www.dropbox.com/s/7fug39z9wg7q6b9/Audit Team Performance Scorecard.xlsm?dl=0

 

[My Aswer Is This]

Maybe someone else on the forum will be willing to open your file and help you. I never open files posted here.

 

[Jefferson2512]

okay I understand..well, thanks for the help man ..

more power!!!

 

[dmt32]

HI,
you have created a very complex project which clearly you have spent a lot of time on. Fortunately, it looks like you have used naming conventions with indexes for your controls which allows the use of looping that will avoid all the "IF" lines of code you have for each control.

I do not have time to spend working through your project but you can try this update to your Score code & see if it does what you want

Private Sub score()
    Dim wks As Worksheet
    Dim i As Integer, Opt As Integer
    Dim Cell As Range, Rng As Range, Area As Range
    Dim YesOption As String, NoOption As String


    Set wks = Sheet9
    
    Set Rng = wks.Range("BO3:BX3,BY3:CH3,CI3:CR3")
    
    For Each Area In Rng.Areas
        Opt = Opt + 1
        i = 0
        YesOption = Choose(Opt, "apeyes", "ccyes", "aryes")
        NoOption = Choose(Opt, "apeno", "ccno", "arno")
        For Each Cell In Rng.Areas(Opt).Cells
            Me.Controls(YesOption & IIf(i = 0, "", i)).Value = CBool(Cell.Value = "Yes")
            Me.Controls(NoOption & IIf(i = 0, "", i)).Value = CBool(Cell.Value = "No")
            i = i + 1
        Next Cell
    Next Area
    
    TextBox2.Text = wks.Range("CS3").Offset(0, 0)
End Sub

Another example of how you can use looping through your controls try this update to obcombo_Change

Private Sub obcombo_Change()
    Dim Ctrl As Control
    
    awpEnter.Enabled = Me.obcombo.ListIndex <> -1
    oblbl.Caption = IIf(Me.obcombo.Text = "Regular", 23, 24)
    For Each Ctrl In Me.MultiPage3.Pages(1).Controls
        If TypeName(Ctrl) = "OptionButton" Then Ctrl.Enabled = awpEnter.Enabled
    Next Ctrl


    Call awp
             
End Sub

Both codes are untested in your project but have been created to hopefully, show a method in producing more compact code.

Dave

 

[Jefferson2512]

HI,
you have created a very complex project which clearly you have spent a lot of time on. Fortunately, it looks like you have used naming conventions with indexes for your controls which allows the use of looping that will avoid all the "IF" lines of code you have for each control.

I do not have time to spend working through your project but you can try this update to your Score code & see if it does what you want

Private Sub score()
    Dim wks As Worksheet
    Dim i As Integer, Opt As Integer
    Dim Cell As Range, Rng As Range, Area As Range
    Dim YesOption As String, NoOption As String


    Set wks = Sheet9
    
    Set Rng = wks.Range("BO3:BX3,BY3:CH3,CI3:CR3")
    
    For Each Area In Rng.Areas
        Opt = Opt + 1
        i = 0
        YesOption = Choose(Opt, "apeyes", "ccyes", "aryes")
        NoOption = Choose(Opt, "apeno", "ccno", "arno")
        For Each Cell In Rng.Areas(Opt).Cells
            Me.Controls(YesOption & IIf(i = 0, "", i)).Value = CBool(Cell.Value = "Yes")
            Me.Controls(NoOption & IIf(i = 0, "", i)).Value = CBool(Cell.Value = "No")
            i = i + 1
        Next Cell
    Next Area
    
    TextBox2.Text = wks.Range("CS3").Offset(0, 0)
End Sub

Another example of how you can use looping through your controls try this update to obcombo_Change

Private Sub obcombo_Change()
    Dim Ctrl As Control
    
    awpEnter.Enabled = Me.obcombo.ListIndex <> -1
    oblbl.Caption = IIf(Me.obcombo.Text = "Regular", 23, 24)
    For Each Ctrl In Me.MultiPage3.Pages(1).Controls
        If TypeName(Ctrl) = "OptionButton" Then Ctrl.Enabled = awpEnter.Enabled
    Next Ctrl


    Call awp
             
End Sub

Both codes are untested in your project but have been created to hopefully, show a method in producing more compact code.

Dave

Thanks for the help man .. can you help me more ? can you loop this one ??

    Dim wks As Worksheet
    Dim addnew As Range
    Dim addup(20) As Double
    Set wks = Sheet9
    Set addnew = wks.Range("CI3").Offset(0, 0)
            If aryes.Value = True Then
                addnew.Offset(0, 0).Value = aryes.Caption
                ElseIf arno.Value = True Then
                addnew.Offset(0, 0).Value = arno.Caption
            End If
            If aryes1.Value = True Then
                addnew.Offset(0, 1).Value = aryes1.Caption
                ElseIf arno1.Value = True Then
                addnew.Offset(0, 1).Value = arno1.Caption
            End If
            If aryes2.Value = True Then
                addnew.Offset(0, 2).Value = aryes2.Caption
                ElseIf arno2.Value = True Then
                addnew.Offset(0, 2).Value = arno2.Caption
            End If
            If aryes3.Value = True Then
                addnew.Offset(0, 3).Value = aryes3.Caption
                ElseIf arno3.Value = True Then
                addnew.Offset(0, 3).Value = arno3.Caption
            End If
            If aryes4.Value = True Then
                addnew.Offset(0, 4).Value = aryes4.Caption
                ElseIf arno4.Value = True Then
                addnew.Offset(0, 4).Value = arno4.Caption
            End If
            If aryes5.Value = True Then
                addnew.Offset(0, 5).Value = aryes5.Caption
                ElseIf arno5.Value = True Then
                addnew.Offset(0, 5).Value = arno5.Caption
            End If
            If aryes6.Value = True Then
                addnew.Offset(0, 6).Value = aryes6.Caption
                ElseIf arno6.Value = True Then
                addnew.Offset(0, 6).Value = arno6.Caption
            End If
            If aryes7.Value = True Then
                addnew.Offset(0, 7).Value = aryes7.Caption
                ElseIf arno7.Value = True Then
                addnew.Offset(0, 7).Value = arno7.Caption
            End If
            If aryes8.Value = True Then
                addnew.Offset(0, 8).Value = aryes8.Caption
                ElseIf arno8.Value = True Then
                addnew.Offset(0, 8).Value = arno8.Caption
            End If
            If aryes9.Value = True Then
                addnew.Offset(0, 9).Value = aryes9.Caption
                ElseIf arno9.Value = True Then
                addnew.Offset(0, 9).Value = arno9.Caption
            End If


            If aryes.Value = True Then
                addup(0) = 100
                ElseIf arno.Value = True Then
                addup(0) = 0
            End If
            If aryes1.Value = True Then
                addup(1) = 100
                ElseIf arno1.Value = True Then
                addup(1) = 0
            End If
            If aryes2.Value = True Then
                addup(2) = 100
                ElseIf arno2.Value = True Then
                addup(2) = 0
            End If
            If aryes3.Value = True Then
                addup(3) = 100
                ElseIf arno3.Value = True Then
                addup(3) = 0
            End If
            If aryes4.Value = True Then
                addup(4) = 100
                ElseIf arno4.Value = True Then
                addup(4) = 0
            End If
            If aryes5.Value = True Then
                addup(5) = 100
                ElseIf arno5.Value = True Then
                addup(5) = 0
            End If
            If aryes6.Value = True Then
                addup(6) = 100
                ElseIf arno6.Value = True Then
                addup(6) = 0
            End If
            If aryes7.Value = True Then
                addup(7) = 100
                ElseIf arno7.Value = True Then
                addup(7) = 0
            End If
            If aryes8.Value = True Then
                addup(8) = 100
                ElseIf arno8.Value = True Then
                addup(8) = 0
            End If
            If aryes9.Value = True Then
                addup(9) = 100
                ElseIf arno9.Value = True Then
                addup(9) = 0
            End If

 

[dmt32]

Thanks for the help man .. can you help me more ? can you loop this one ??

Hi,
Yours is a very complex project & think my suggestions went much further to answer your original question.
If I get a quiet moment will have a look but idea was for you take what I have done & see if you can apply it to other codes in your project - if get, stuck, can always post your attempts here for further guidance.

Dave