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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,466
Trevor Steward said:
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...

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

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

Attachments

  • Screenshot 2020-07-11 at 23.43.46.png
    Screenshot 2020-07-11 at 23.43.46.png
    166 KB · Views: 3

Domenic

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

New Member
Joined
Jul 11, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

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

Attachments

  • Screenshot 2020-07-12 at 11.23.41.png
    Screenshot 2020-07-12 at 11.23.41.png
    28.2 KB · Views: 5

Trevor Steward

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

Attachments

  • Screenshot 2020-07-12 at 11.34.59.png
    Screenshot 2020-07-12 at 11.34.59.png
    95.2 KB · Views: 2

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,466

ADVERTISEMENT

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

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

Attachments

  • Screenshot 2020-07-12 at 21.02.40.png
    Screenshot 2020-07-12 at 21.02.40.png
    167.1 KB · Views: 3

Domenic

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

VBA Code:
.OLEFormat.Object.LinkedCell = targetRange.Address

with

Code:
.ControlFormat.LinkedCell = targetRange.Address

Does this help?
 

Trevor Steward

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

Attachments

  • Screenshot 2020-07-13 at 11.02.20.png
    Screenshot 2020-07-13 at 11.02.20.png
    157.5 KB · Views: 2

Watch MrExcel Video

Forum statistics

Threads
1,127,843
Messages
5,627,213
Members
416,230
Latest member
jdaitchman

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