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]

Oh I see, so you're actually using a Windows version of Excel, but within a virtual desktop. Yeah, that shouldn't be a problem. In that case, my initial code should have worked.

I looked at the error that you pointed out in your last screenshot, but it doesn't seem likely that the error occurs on that particular line, It probably errors out on .ControlFormat....

Initially, I assumed that you had an ActiveX checkbox based on its name. Do you in fact have an ActiveX checkbox? Or do you have a Form control checkbox?

 

[Trevor Steward]

Domenic,
I think it is ActiveX. I have attached the properties for one of the checkboxs on the sheet

 

[Domenic]

Yes, it's indeed an ActiveX control. So my initial code should work.

In the Visual Basic Editor (Alt+F11), select Tools, and then select References. You should have the following reference selected/checked...

Microsoft Forms 2.0 Object Library

Is it selected/checked?

If not, click on the Browse button on the right, and then find and select the following library...

FM20.DLL

On my 64-bit Windows 10, it's located in the following folder...

C:\WINDOWS\SysWOW64

Does this help?

 

[Trevor Steward]

Domenic,
when I open preferences I can see Microsoft Forms 2.0 Object Library which is checked

I looked in C:\WINDOWS\SysWOW64 but could not find FM20.DLL

I have attached screenshots of the forms

 

[Domenic]

Yeah, I see from your image that the library is indeed referenced. And, if you look towards the bottom, you'll see where the file is located.

At this point, I don't know what else to suggest. You're using the Windows version of Excel within a virtual desktop. I've done the same in the past and don't remember having any problems. And you have an ActiveX checkbox, so my initial code should work.

Out of curiosity, assuming that the sheet containing the checkbox is the active sheet and that the name of your checkbox is CheckBox1, and you run the following line of code in the Immediate Window (Ctrl+G), what does it return?

? typename(activesheet.oleobjects("CheckBox1").object)

It should return CheckBox. Does it?

 

[Trevor Steward]

Domenic,
It does return Checkbox.

Thanks for all you Effort. If I find the answer I will let you know.

Best Regards

 

[Domenic]

Okay, let's try a couple of other alternatives. The first alternative uses the OleObjects object to refer to and copy the checkbox, and then the Shapes object to refer to the new copy. And, while the second alternative also uses the OleObjects to refer to the checkbox, it uses the Duplicate method of the OleObject object to return a reference to the new copy. Does this help?

[Option 1]

    Dim targetRange As Range

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

[Option 2]

    Dim targetRange As Range
    Dim duplicateCheckbox As OLEObject

    Set targetRange = Range("uc").Offset(-2, 5)
   
    Set duplicateCheckbox = ActiveSheet.OLEObjects("CheckBox1").Duplicate
    With duplicateCheckbox
        .Left = targetRange.Left
        .Top = targetRange.Top
        .LinkedCell = targetRange.Address
    End With

 

[Trevor Steward]

Domenic,
Option1 works, Have not tried option 2
The code below adds another line then inserts two checkboxes and a combobox on this new line with the correct linked cells

Thank you for all your hard work and patience

Best Regards

Trevor

Now this works I am going to try to code vba to remove the last line, complete with checkboxes and combobox

Thanks Again

Sub Add_new_line()
'
 ActiveSheet.Range("uc").Activate
    ActiveCell.Offset(-1, 0).Range("A1:P1").Select
    Selection.Insert Shift:=xlDown
    Selection.RowHeight = 15
    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
    Set targetRange = Range("uc").Offset(-2, 5)
    With ActiveSheet
        .OLEObjects("CheckBox1").Copy
        .Paste
        With .OLEObjects(.OLEObjects.Count)
            .Left = targetRange.Left
            .Top = targetRange.Top
            .LinkedCell = targetRange.Address
        End With
    End With

 'Copy Dom? CheckBox2 on top line
    Set targetRange = Range("uc").Offset(-2, 6)
    With ActiveSheet
        .OLEObjects("CheckBox2").Copy
        .Paste
        With .OLEObjects(.OLEObjects.Count)
            .Left = targetRange.Left
            .Top = targetRange.Top
            .LinkedCell = targetRange.Address
        End With
    End With

'Copy Distibution? Combobox1 on top line
    Set targetRange = Range("uc").Offset(-2, 10)
    With ActiveSheet
        .OLEObjects("Combobox1").Copy
        .Paste
        With .OLEObjects(.OLEObjects.Count)
            .Left = targetRange.Left
            .Top = targetRange.Top
            .LinkedCell = targetRange.Address
        End With
    End With

    ActiveSheet.Range("uc").Activate

End Sub

 

[Domenic]

That's great, I'm glad I was able to help!

Cheers!