Im currently trying to implement an insert new row value and a automatic checkbox inserter.
I currently have the following code spread over different buttons and therefore different Subs. I have boldened the key information that i will need to increment by 1 cell. This will occur after clicking the "InsertNewBill" button.:
Private Sub InsertNewBill_Click()
'I AM USING i TO STORE THE CELL INCREMENT, IT CURRENTLY DOES NOTHING**
Dim i As Integer
'**range("A30:AC30").Select**
'**range("AC30").Activate**
Selection.Copy
Selection.Insert Shift:=xlDown
End Sub
Private Sub DeleteTickBoxes_Click()
'Variables
Dim c As CheckBox
Dim CellRange As Range
Dim cel As Range
Set CellRange = ActiveSheet.Range("E7:**F30**")
'Delete Checkboxes within the specified range above on the ActiveSheet Only
For Each c In ActiveSheet.CheckBoxes
If Not Intersect(c.TopLeftCell, CellRange) Is Nothing Then
c.Delete
End If
Next
'Insert New Checkboxes and Assign to a specified link cell using the offset
For Each cel In CellRange
'you can adjust left, top, height, width to your needs
Set c = ActiveSheet.CheckBoxes.Add(cel.Left, cel.Top, 30, 6)
With c 'Clears the textbox so it has no text
.Caption = ""
'Offset works by offsetting (Row offset, Column Offset) and accepts
'positive for down/right and negative for left/up,
'keep in not that the linked cells will automatically populate with true/false
.LinkedCell = cel.Offset(0, -4).Address
End With
Next
Call CentreCheckbox_Click
End Sub
I need all boldened values to increase by one. I.e from F30 to F31 and A30:AC30 to A31:AC31. This value also needs to be carried across from the InsertNewBill_Click sub to the DeleteTickBoxes_Click sub.
I assume i will need to remove the Private sub and possibly have a public integer variable? Im just not sure how to implement increasing only the number by 1 after each button click.
All your help is appreciated
MyCell.Value = MyCell.Value + 1
?range("A30:AC30").Select
. I apologise, im relatively new to this