VBA to assign linked cell

Trevor Steward

New Member
Joined
Jul 11, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

VBA Code:
    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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,465
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?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Trevor Steward

New Member
Joined
Jul 11, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Domenic,
I think it is ActiveX. I have attached the properties for one of the checkboxs on the sheet
 

Attachments

  • Screenshot 2020-07-13 at 21.24.56.png
    Screenshot 2020-07-13 at 21.24.56.png
    203.6 KB · Views: 2

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,465
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...

VBA Code:
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...

VBA Code:
FM20.DLL

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

VBA Code:
C:\WINDOWS\SysWOW64

Does this help?
 

Trevor Steward

New Member
Joined
Jul 11, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Attachments

  • Screenshot 2020-07-13 at 23.09.43.png
    Screenshot 2020-07-13 at 23.09.43.png
    85.6 KB · Views: 3

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,465

ADVERTISEMENT

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?

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

It should return CheckBox. Does it?
 

Trevor Steward

New Member
Joined
Jul 11, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Domenic,
It does return Checkbox.

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

Best Regards
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,465

ADVERTISEMENT

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]

VBA Code:
    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]

VBA Code:
    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

New Member
Joined
Jul 11, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,101
Members
416,161
Latest member
David1966Lewis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top