Turn a fixed range in VBA code to a dynamic range in VBA code

[sashapixie]

I have the code below to input check boxes and link them to the cells they are located in, I want to change to the range to a dynamic range for e.g

If cell B3 <> "" input check boxes in to columns J3:R3 and also I want to align the check boxes to be in the centre of the cells.

Option Explicit
Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range
With ActiveSheet
.CheckBoxes.Delete 'nice for testing
For Each myCell In ActiveSheet.Range("J1:R1").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = "" 'or whatever you want
'.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With
Next myCell
End With
End Sub

I am quite new to VBA, and have been struggling for a while, I found the code online which works, i am just struggling on where I would make the amendments and how to write them.

Any help would be appreciated.

Thanks

 

[MARK858]

I'm not sure exatly what you want as a dynamic range, I am assuming that you mean column B as you mention B3.
If my assumption is correct then try the code below

Sub CellCheckbox()
    Dim myCell As Range, rngC As Range
    Dim myRng As Range
    Dim CBX As CheckBox

    Application.ScreenUpdating = False

    With ActiveSheet
        .CheckBoxes.Delete

        For Each rngC In .Range("B3:B" & .Range("B" & .Rows.Count).End(xlUp).Row)
            If rngC <> "" Then
                Set myRng = .Range(.Cells(rngC.Row, "J"), .Cells(rngC.Row, "R"))

                For Each myCell In myRng.Cells
                    With myCell
                        Set CBX = .Parent.CheckBoxes.Add( _
                                  Top:=.Top, _
                                  Left:=.Left, _
                                  Width:=1, _
                                  Height:=1)
                        'CBX.Name = "CBX_" & .Address(0, 0)
                        CBX.Caption = ""
                        CBX.Left = .Left + ((.Width - CBX.Width) / 2)
                        CBX.Top = .Top + ((.Height - CBX.Height) / 2)
                        CBX.LinkedCell = .Offset(0, 0).Address(external:=True)
                        CBX.Value = xlOff
                        .Offset(0, 1).NumberFormat = ";;;"
                    End With

                Next myCell
            End If
        Next
    End With

    Application.ScreenUpdating = True

End Sub

 

[sashapixie]

I'm not sure exatly what you want as a dynamic range, I am assuming that you mean column B as you mention B3.

Hi Mark

I have data to input into cell B3 when I enter the info I want the macro to input the checkboxes into the columns J3:R3, however I will be adding to the list in column B and therefore when I enter the data into B4 I want the checkboxes to appear in the columns J4:R4 without me having to manually enter the range every time I add to the list. hope this helps, your efforts are appreciated

Thanks

 

[sashapixie]

Hi Mark

The macro is working when I run it, however I want it to run automatically when the cell in column B is not blank.

Thanks, again for the amendment

 

[MARK858]

however I want it to run automatically when the cell in column B is not blank

It would have been better to state this in post #1 rather than

I want to change to the range to a dynamic range

where you don't actually need any range to be dynamic.

 

[MARK858]

Try the code below. You need to put it in the worksheet module of the sheet (right click the sheet tab, click view code and paste the code in the window that appears).

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myCell As Range, myRng As Range, CBX As CheckBox

    If Target.Count > 1 Then Exit Sub

    Application.ScreenUpdating = False

    If Not Intersect(Target, Columns("B:B")) Is Nothing Then
        If Not IsEmpty(Target) And Target.Row > 1 Then

            With ActiveSheet

                Set myRng = .Range(.Cells(Target.Row, "J"), .Cells(Target.Row, "R"))

                For Each myCell In myRng.Cells
                    With myCell
                        Set CBX = .Parent.CheckBoxes.Add( _
                                  Top:=.Top, _
                                  Left:=.Left, _
                                  Width:=1, _
                                  Height:=1)
                        CBX.Name = "CBX_" & .Address(0, 0)
                        CBX.Caption = ""
                        CBX.Left = .Left + ((.Width - CBX.Width) / 2)
                        CBX.Top = .Top + ((.Height - CBX.Height) / 2)
                        CBX.LinkedCell = .Offset(0, 0).Address(external:=True)

                        CBX.Value = xlOff
                        .Offset(0, 1).NumberFormat = ";;;"
                    End With
                Next
            End With
        End If
    End If

    Application.ScreenUpdating = True

End Sub

 

[sashapixie]

This is perfect! Thank you Mark for your help and guidance, it is greatly appreciated!