Yourself93
New Member
- Joined
- Jun 1, 2016
- Messages
- 41
Hi everyone, looking for some help with a problem I am trying to solve. Before I get to it, I will preface this by saying that before yesterday I had never once touched VBA (to be honest didn't even know you could do anything close to this in excel) so if you go beyond even the basic of basics for explanations I probably won't understand. I have also historically hated coding and anything like that so yeah.
Anyway, I have been assigned a task at work to take a template that consists of 14 different possible rows where we want to copy and paste that info from a template worksheet into a designated location in a separate worksheet for that file. The thing that is causing problems for myself is that there are option buttons within what we are copying from the one worksheet to the other (I believe they are form control option buttons not active x). I have been able to copy over most things, but in one cell there will be 3 option buttons and for some reason I lose how they are grouped when they get copied over. The top option button works fine but the next two do not show properly (they appear to ungroup from the first option button). I am looking for help on how to get these option buttons to all copy over properly. After that I also need to be able to properly link the option buttons to a cell. Currently they are linked in the template file. Ex. if cell E3 has 3 option buttons these are linked to bell G3. If cell G3 is 1, then option button 1 in E3 is checkmarked, 2 for option button 2 etc. When I copy over the option buttons these buttons remain linked to cell G3 in the template file. I will need them to link to cell G3 of the new sheet I copy them to.
This is a lot of text and I am not sure if I worded it all correctly so please feel free to ask questions.
Code I currently have below is likely a mess (remember I have almost no knowledge of this), also it takes the above row as a reference point and copies what is in cell A and cell B from the above row to the new row created.
Sub Test()
'
Dim y As Long, btn As OLEObject, x As Long
y = ActiveCell.Row
' Test Macro
'
'
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Rows("1:1").EntireRow.Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
For Each btn In ActiveSheet.OLEObjects
If TypeName(btn.Object) = "CheckBox" And Not Intersect(btn.TopLeftCell, Sheets("Temp").Range("F2:BA2")) Is Nothing Then
x = btn.TopLeftCell.Column
btn.Copy
Cells(y, x).Select
ActiveSheet.Paste
End If
Next btn
Sheets("Temp").Range("F2:BA2").Copy Destination:=Sheets("year1").Cells(y, 6)
Application.ScreenUpdating = True
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=R[-1]C"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[-1]C"
ActiveCell.Offset(0, 4).Range("A1:X1").Select
End Sub
I was told this was an easy task, so not feeling great that I cannot understand this.
Anyway, I have been assigned a task at work to take a template that consists of 14 different possible rows where we want to copy and paste that info from a template worksheet into a designated location in a separate worksheet for that file. The thing that is causing problems for myself is that there are option buttons within what we are copying from the one worksheet to the other (I believe they are form control option buttons not active x). I have been able to copy over most things, but in one cell there will be 3 option buttons and for some reason I lose how they are grouped when they get copied over. The top option button works fine but the next two do not show properly (they appear to ungroup from the first option button). I am looking for help on how to get these option buttons to all copy over properly. After that I also need to be able to properly link the option buttons to a cell. Currently they are linked in the template file. Ex. if cell E3 has 3 option buttons these are linked to bell G3. If cell G3 is 1, then option button 1 in E3 is checkmarked, 2 for option button 2 etc. When I copy over the option buttons these buttons remain linked to cell G3 in the template file. I will need them to link to cell G3 of the new sheet I copy them to.
This is a lot of text and I am not sure if I worded it all correctly so please feel free to ask questions.
Code I currently have below is likely a mess (remember I have almost no knowledge of this), also it takes the above row as a reference point and copies what is in cell A and cell B from the above row to the new row created.
Sub Test()
'
Dim y As Long, btn As OLEObject, x As Long
y = ActiveCell.Row
' Test Macro
'
'
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Rows("1:1").EntireRow.Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
For Each btn In ActiveSheet.OLEObjects
If TypeName(btn.Object) = "CheckBox" And Not Intersect(btn.TopLeftCell, Sheets("Temp").Range("F2:BA2")) Is Nothing Then
x = btn.TopLeftCell.Column
btn.Copy
Cells(y, x).Select
ActiveSheet.Paste
End If
Next btn
Sheets("Temp").Range("F2:BA2").Copy Destination:=Sheets("year1").Cells(y, 6)
Application.ScreenUpdating = True
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=R[-1]C"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[-1]C"
ActiveCell.Offset(0, 4).Range("A1:X1").Select
End Sub
I was told this was an easy task, so not feeling great that I cannot understand this.