Hi all bit of a VB novice here and could use some help.
I've been putting together a budget spreadsheet that allows a user to select what payment method was used for each item, this is done with ActiveX option buttons, these all work no problem.
The issue arises when a user attempts to put in a new row. I've borrowed a piece of VB code that will insert a new row along with all formulas from above and a new set of option buttons (this solved the first problem I had)
The trouble is, the new set of option buttons are still considered a part of the same group so do not operate independently of the other groups, they also do not have a linked cell. Is there a way to do this via macro?
E.g. The first row's option buttons are in cell F12, they are linked with cells J,K and L 12 and are in a group called 'Group1', the next row's option buttons (which I created manually) are in F13 and are similarly linked to J,K and L 13, the group name is 'Group2' If I run the macro below, the new row is created but the new option buttons are all in 'Group2' still and have no linked cell where it would need to be J,K and L 14.
Basically, is there a macro to set properties of ActiveX option buttons as they are inserted with a new row?
Here is the code I've borrowed from another site to insert rows:
Sub InsertRowsAndFillFormulas()
Dim x As Long
ActiveCell.EntireRow.Select
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1)
If vRows = False Then Exit Sub
End If
Dim sht As Worksheet, shts() As String, i As Long
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name
x = Sheets(sht.Name).UsedRange.Rows.Count
Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown
Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault
On Error Resume Next
Next
End Sub
I've been putting together a budget spreadsheet that allows a user to select what payment method was used for each item, this is done with ActiveX option buttons, these all work no problem.
The issue arises when a user attempts to put in a new row. I've borrowed a piece of VB code that will insert a new row along with all formulas from above and a new set of option buttons (this solved the first problem I had)
The trouble is, the new set of option buttons are still considered a part of the same group so do not operate independently of the other groups, they also do not have a linked cell. Is there a way to do this via macro?
E.g. The first row's option buttons are in cell F12, they are linked with cells J,K and L 12 and are in a group called 'Group1', the next row's option buttons (which I created manually) are in F13 and are similarly linked to J,K and L 13, the group name is 'Group2' If I run the macro below, the new row is created but the new option buttons are all in 'Group2' still and have no linked cell where it would need to be J,K and L 14.
Basically, is there a macro to set properties of ActiveX option buttons as they are inserted with a new row?
Here is the code I've borrowed from another site to insert rows:
Sub InsertRowsAndFillFormulas()
Dim x As Long
ActiveCell.EntireRow.Select
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1)
If vRows = False Then Exit Sub
End If
Dim sht As Worksheet, shts() As String, i As Long
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name
x = Sheets(sht.Name).UsedRange.Rows.Count
Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown
Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault
On Error Resume Next
Next
End Sub
