VBA to assign linked cell

[Trevor Steward]

Please help if you can.

The code below is to insert a new line at a row referenced from "uc" then copy the checkbox from a previous row and assigning the correct linked cell for the checkbox.
It works upto the line where the linked cell is assigned (row inserted and checkbox created) , the line is shown below should link the cell to the checkbox.

Selection.LinkedCell = ActiveWindow.RangeSelection.Address

    ActiveCell.Offset(-1, 0).Range("A1:P1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1:P1").Select
    Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Selection.RowHeight = 15
    'Application.CutCopyMode = False

' Copy Inc? CheckBox1 on top line
    ActiveSheet.Shapes("CheckBox1").Select
    Selection.Copy
    ActiveSheet.Range("uc").Activate
    ActiveCell.Offset(-2, 5).Range("A1").Select
    ActiveSheet.Paste
    Selection.LinkedCell = ActiveWindow.RangeSelection.Address
    
' Copy Dom? CheckBox2 on top line
    ActiveSheet.Shapes("CheckBox2").Select
    Selection.Copy
    ActiveSheet.Range("uc").Activate
    ActiveCell.Offset(-2, 6).Range("A1").Select
    ActiveSheet.Paste
    Selection.LinkedCell = ActiveWindow.RangeSelection.Address

' Copy Prob Dist ComboBox1 Box on top line
    ActiveSheet.Shapes("ComboBox1").Select
    Selection.Copy
    ActiveSheet.Range("uc").Activate
    ActiveCell.Offset(-2, 10).Range("A1").Select
    ActiveSheet.Paste
    Selection.LinkedCell = ActiveWindow.RangeSelection.Address

' Done
    ActiveSheet.Range("uc").Activate
End Sub

Thanks

 

[Domenic]

Selection.LinkedCell = ActiveWindow.RangeSelection.Address

Selection in the above line refers to the target range, not the checkbox. So you'll need to make sure that the checkbox is selected prior to linking it to a cell. However, there's no need to do any selecting and activating. For example, your code for CheckBox1 can be re-written as follows...

    Dim targetRange As Range

    'Copy Inc? CheckBox1 on top line
    Set targetRange = Range("uc").Offset(-2, 5)
    With ActiveSheet
        .Shapes("CheckBox1").Copy
        .Paste
        With .Shapes(.Shapes.Count)
            .Left = targetRange.Left
            .Top = targetRange.Top
            .OLEFormat.Object.LinkedCell = targetRange.Address
        End With
    End With

Hope this helps!

 

[Trevor Steward]

Domenic,
Thank you for your reply, but this has not been successful. I have uploaded a photo to show what I am trying to do: -

I want to click a command button which will run the VBA code to insert a new row under row 29, which has checkbox25 checkbox26 and combobox13

Then add checkbox27, checkbox28 and combox14 all with the linkcell to this new row.

This to happen when ever the command button is pressed.

 

[Domenic]

In what way is it not successful?

Did you modify your code?

Or, did you adopt my code?

In any case, can you post the exact code that you're now using?

And, if you're getting an error, please specify which line is causing the error, and the exact error message that appears.

 

[Trevor Steward]

Domenic,

I removed my code and inserted your ad the tried to run it in VBA and got the error message attached. The code is below: -

Sub Add_new_line()
'
Dim targetRange As Range

    'Copy Inc? CheckBox1 on top line
    Set targetRange = Range("uc").Offset(-2, 5)
    With ActiveSheet
        .Shapes("CheckBox1").Copy
        .Paste
        With .Shapes(.Shapes.Count)
            .Left = targetRange.Left
            .Top = targetRange.Top
            .OLEFormat.Object.LinkedCell = targetRange.Address
        End With
    End With

End Sub

 

[Trevor Steward]

Domenic,

Additional information: -

attached screenshot of the spreadsheet after running your code, Also new checkbox is not in the correct place on the line.

Also the new checkbox is linked to checkbox1 and clicking either changed both. I think they are both checkbox1

 

[Domenic]

When I run that code, I don't get any errors. It seems to run successfully, whether the checkbox is a Form control or an ActiveX control. Which line causes the error?

 

[Trevor Steward]

Domenic,

I have deleted all my code and pasted your code into the Sub Add_new_line()

I have uploaded the screen image after running the code. I have commented the line which causes the problem.

For information I am running this on Excel 365, Windows 10 running on Parallels 15 on a MacBook

 

[Domenic]

Sorry, my mistake. I did not notice that you were using the Mac version of Excel. I'm not familiar with that version, but I think you can replace...

.OLEFormat.Object.LinkedCell = targetRange.Address

with

.ControlFormat.LinkedCell = targetRange.Address

Does this help?

 

[Trevor Steward]

Domenic,
I have installed Parallels virtual desktop on my MacBook. On this virtual desktop I installed MS Windows 10. Ran Windows in Vitual desktop and downloaded Office 365 (Windows Version) I know the Windows version I have is different as the spreadsheet has a button for running a Monte Carlo Simulacra which on the windows version shows an Embedded command which works and on my Mac version the button does not work. so this shows that the two versions are different and I am using the MS windows version to run the code.

The amended code seems to go further but still gives an error message. I have attached screen shot:-