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

J

Jefferson2512

Board Regular
Joined
Sep 16, 2019
Messages
53
Code: 
    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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Not sure I see a question here.
You do not nee to use:
Code: 
[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.
 
Upvote 0
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 ?
 
Last edited by a moderator:
Upvote 0
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:
Code: 
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
 
Upvote 0
Maybe someone else on the forum will be willing to open your file and help you. I never open files posted here.
 
Upvote 0
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

Code: 
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
Code: 
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
 
Upvote 0
dmt32 said:
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

Code: 
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
Code: 
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
Click to expand...


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

Code: 
    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
 
Upvote 0
Jefferson2512 said:
Thanks for the help man .. can you help me more ? can you loop this one ??
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
202
ipbr21054
I
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
191
krunal123
K
J
  • Question Question
Excel VBA Print Name Tag from Userform
Replies
2
Views
343
Rhodie72
Rhodie72
I
  • Question Question
Receive Automation error when running code & values added to sheet twice
2
Replies
13
Views
430
NoSparks
N
C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
182
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

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