0

Very new to VBA, usually use Matlab and Python. I've got the following section of code

Sub Example
    Dim num As Integer

    num = 62
    ActiveSheet.CheckBoxes.Add(23.25, 595.5, 101.25, 18.75).Select

    With Selection
        .Name = "NewCheckBox"
        Sheets("IPT data").Select
        .Caption = Cells(num, "C")
    End With

    Sheets("IPT Chart").Select
    ActiveSheet.CheckBoxes("NewCheckBox").Select

    With Selection
        .Value = xlOff
        .LinkedCell = "'IPT data'!$A$num"
        .Display3DShading = False
    End With
End Sub

I want the linked cell to refer to Anum. Is there any way to do this? Later num will be used in a loop so I can't just use it standalone.

Like I say, very new and this is probably basic stuff, so I apoligse. I had a search around and have tried to use both Cells and Range to no avail.

Thanks

David Rushton
  • 4,915
  • 1
  • 17
  • 31
user6649163
  • 1
  • 2
    This has nothing to do with Excel object model. Surely if you use Python you know about string concatenation? `= "'IPT data'!$A$" & num`. Also please see [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/q/10714251/11683). – GSerg Jul 28 '16 at 10:40

1 Answers1

0

use

.LinkedCell = "'IPT data'!$A$" & num

but you can avoid all that selecting and write:

Option Explicit
Sub Example2()
    Dim num As Integer

    num = 62
    With Worksheets("IPT Chart")
        With .CheckBoxes.Add(23.25, 595.5, 101.25, 18.75)
            .Name = "NewCheckBox"
            .Caption = Worksheets("IPT data").Cells(num, "C")
            .Value = xlOff
            .LinkedCell = "'IPT data'!$A$" & num
            .Display3DShading = False
        End With
    End With
End Sub
user3598756
  • 28,893
  • 4
  • 18
  • 28