Option Button w variable cell link

SpencerRichman

New Member
Joined
Feb 15, 2013
Messages
18
Hi all. I am creating a scorecard for a rock climbing competition. When the team turns in their scorecard, I want the staff to be able to enter the team # and then click radio buttons to indicate how each climber did on each route. It would look like this:
2020-01-22_123756.jpg

I would like the radio buttons in row 7 (and when complete, I'd have radio buttons in all rows) to link to cells for Mary Kay's row on another sheet. BUT, when the staff member changes the team # in cell B2, a different competitor's name will appear in cell D4, and then I'd like the radio buttons in row 7 link to DIFFERENT cells on another sheet.

Does anyone know how to accomplish this?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
You are describing the radio buttons linking to cells for the person on another sheet ... so is this to be an output sheet, showing what the status is on the other sheet, or is this to be an input sheet of some kind? If input, what is supposed to be done with the choices, exactly?
 

SpencerRichman

New Member
Joined
Feb 15, 2013
Messages
18
Glenn, the example I showed is the INPUT sheet. The staff would mark the radio button corresponding to how many attempts it took the climber to complete the climb (anywhere from Flash - which means first attempt - to 5 tries). I would have a group of radio buttons on each row (one set for each rope station #). But as you know, creating all these radio buttons and grouping them takes a lot of effort, so I don't want to re-create all these sets of radio buttons for all 40 or so climbers. Instead, I was hoping that by changing the team # at the top, I could have the radio buttons link to different cells for the output.
For example, I have team #3 chosen and it shows Mary Kay as the climbers name. If we hit the radio button under the number 2 as the image shows, then I'd like the output to be a linked cell on another sheet corresponding to a row & cell with Mary Kay's name.
But if I change the team # to 4, then it would show the climber's name as John Doe. And when we select the radio button under the number 3 (if it took him 3 attempts to finish the climb), then I'd like the output to be a linked cell on another sheet corresponding to a row & cell with John Doe's name.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Thanks for the extra info. Design-wise, I would use a "ü", formatted with Wingdings font (looks like a tick), toggled with blank, in cells, instead of radio buttons ... with the toggle done by sheet event BeforeDoubleClick; right-click the tab and choose View Code, with this code:

VBA Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Set isect = Intersect(Target, Range("D7:I99"))
    If Not isect Is Nothing Then
        If Target.Value = "" Then
            Range("D" & Target.Row).Resize(1, 6).ClearContents
            Target.Value = "ü"
        Else
            Target.Value = ""
        End If
    End If
End Sub

Once you have this working, then I would consider having a button to click to save to the sheet and row for the person concerned. Let me know what you think of this, so far.
 

SpencerRichman

New Member
Joined
Feb 15, 2013
Messages
18

ADVERTISEMENT

Thanks Glenn. I like this solution. In order to complete it, can I have two different BeforeDoubleClick Codes? One would be for columns D through H (I changed the Range in the code to "D7:H99"). The other would be for columns I and J.

Columns D-H record how many attempts it took the climber to climb the route. Columns I-J record whether the climber climbed on Top Rote or Lead.

If that will work, then the last piece would be a code to change the viewable display based on what team # is entered in cell B2.

Thanks again for your help!
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Hi Spencer, you can test for 2 ranges in the BeforeDoubleClick code. Like this:


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set isect = Intersect(Target, Range("D7:H99")) If Not isect Is Nothing Then If Target.Value = "" Then Range("D" & Target.Row).Resize(1, 5).ClearContents Target.Value = "ü" Else Target.Value = "" End If End If Set isect = Intersect(Target, Range("I7:J99")) If Not isect Is Nothing Then If Target.Value = "" Then Range("I" & Target.Row).Resize(1, 2).ClearContents Target.Value = "ü" Else Target.Value = "" End If End If End Sub

As for the last piece ... does "change the viewable display" mean storing the data to the data sheet? You're going to need a button (a graphic object, like a circle, or oval, or rounded-square, with text and colour to make it easy to identify ... right-click to assign macro) ... and associated code to translate the choices into however you are going to store data. Let me know what help you need.
 

SpencerRichman

New Member
Joined
Feb 15, 2013
Messages
18
Glenn, it may not be as elegant, but I got 2 ranges to work with the following:

Private Sub GoBeforeDoubleClick1(ByVal Target As Range, Cancel As Boolean)
Set isect = Intersect(Target, Range("D7:H999"))
If Not isect Is Nothing Then
If Target.Value = "" Then
Range("D" & Target.Row).Resize(1, 5).ClearContents
Target.Value = "X"
Else
Target.Value = ""
End If
End If
End Sub


Private Sub GoBeforeDoubleClick2(ByVal Target As Range, Cancel As Boolean)
Set isect = Intersect(Target, Range("I7:J999"))
If Not isect Is Nothing Then
If Target.Value = "" Then
Range("I" & Target.Row).Resize(1, 2).ClearContents
Target.Value = "X"
Else
Target.Value = ""
End If
End If
End Sub


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
GoBeforeDoubleClick1 Target, Cancel
GoBeforeDoubleClick2 Target, Cancel
Application.EnableEvents = True
End Sub


...and then simply used columns hidden = true and false and rows hidden = true and false to make the areas of the worksheet visible depending on the team number...so I think it's all solved! Thank you so much for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,493
Messages
5,636,641
Members
416,932
Latest member
mm07

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
Top