Change LinkedCell in Toggle Button

[pagrender]

Hello all,

I have a toggle button (active x control) in cell B1. In the properties menu, I linked the toggle button to cell A1. The toggle button is entirely in the cell.

I need to make a toggle button in each cell in the range of B1:B30. The toggle button in cell B2 should link to A2, the toggle button in cell B3 should link to A3, etc. What is the best way to do this? My guess would have been Copy & Paste.

If I use Copy & Paste, the LinkedCell reference does not update and I have to make all the changes manually. Is there a way to have the LinkedCell reference update with each copy & paste?

Thanks for your help,
Pete

 

[Domenic]

Try the following code, which will automatically copy your ToggleButton and set the LinkedCell property for each one. Each control will be positioned according to the position of your first ToggleButton in relation to the cell it occupies. And, if you wish, you can also re-position your first ToggleButton anywhere along the first row and the code will copy it down the same column (Rows 2 through 30), along with setting the LinkedCell to the cell left of it.

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] TB [color=darkblue]As[/color] MSForms.ToggleButton
    
    [color=darkblue]Dim[/color] OleObj [color=darkblue]As[/color] Excel.OLEObject
    
    [color=darkblue]Dim[/color] StartCell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Col [color=darkblue]As[/color] [color=darkblue]Integer[/color]
    [color=darkblue]Dim[/color] LeftPos [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] TopMargin [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    [color=darkblue]Set[/color] TB = Me.ToggleButton1 [color=green]'change the name accordingly[/color]
    
    [color=darkblue]Set[/color] StartCell = TB.TopLeftCell
    
    Col = StartCell.Column
    
    LeftPos = TB.Left
    
    TopMargin = TB.[color=darkblue]To[/color]p - StartCell.Top
    
    [color=darkblue]For[/color] i = 2 To 30
        Cells(i, Col).RowHeight = StartCell.RowHeight [color=green]'ensure each row is the same height[/color]
        [color=darkblue]Set[/color] OleObj = Me.OLEObjects.Add( _
            ClassType:="Forms.ToggleButton.1", _
            Left:=LeftPos, _
            Top:=Cells(i, Col).Top + TopMargin, _
            Width:=TB.Width, _
            Height:=TB.Height)
        [color=darkblue]With[/color] OleObj
            .LinkedCell = Cells(i, Col).Offset(, -1).Address
            .Object.Value = [color=darkblue]False[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Next[/color] i
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[pagrender]

Thanks Domenic!

I'll give this a try when I get into work tomorrow.

Thanks again!