Group Option Based on Contents of Another Cell - Copied Worksheet

shellp

Board Regular
Joined
Jul 7, 2010
Messages
207
Office Version
  1. 365
  2. 2021
  3. 2010
  4. 2007
Platform
  1. Windows
Hello

I have an Excel macro that takes data from one worksheet that is formatted so one row is one abstract and makes a single worksheet for each abstract. The abstract is based on a template that is used as the "master" for all abstracts. All is working very well as is. But now I've added an option group of 3 buttons for age group: <45, 46 to 65 and 65+. I need the option box that is copied to the new worksheet to be based on another field, age, that is already in the abstract.

I'm not sure how to do this and just as importantly I don't know where to add the piece of code to do this in the current program. Below is the program I have now that is working minus setting the option box values:

Code:
Sub AbstractData()
Dim r As Range, wsAdd As Worksheet, t As Range, rSEQ_NO As Range, s As Range, myPassword As String, ws As Worksheet, hims

Application.EnableEvents = False

With Sheets("Raw")
Set rSEQ_NO = .Rows(1).Find("MRN")

If Not rSEQ_NO Is Nothing Then
For Each r In .Range(.[A2], .[A2].End(xlDown))

Sheets("Template").Copy After:=Sheets(Sheets.Count)
Set wsAdd = ActiveSheet
Set targcell = wsAdd.Cells(33, 6)

wsAdd.Name = .Cells(r.Row, rSEQ_NO.Column).Value

For Each t In [From]
.Range(.Cells(r.Row, t.Value), .Cells(r.Row, t.Offset(0, 1).Value)).Copy
wsAdd.Range(t.Offset(0, 2).Value).PasteSpecial _
Paste:=xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
       
Next t
wsAdd.Range("C7:C20,C22,C24:C28:C30:C32").HorizontalAlignment = xlLeft
wsAdd.Range("C7:C20,C22,C24:C28:C30:C32").VerticalAlignment = xlTop
wsAdd.Range("C21,C23,C29").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .EntireRow.AutoFit
        
    End With
       
Next

End If
End With

Application.EnableEvents = True

End Sub

Any and all assistance greatly appreciated.
 
I figured it out.

My problem was not knowing what properties of the option box I wanted. I ended up creating a select statement for the age cell and the format to check the appropriate option box was:
Code:
wsadd.shapes("Option Button 10").ControlFormat.Value=xlon
 
Upvote 0

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