Cell reference in VBA

[andrewb90]

Hello all,

I feel like this is a really dumb thing to ask, but I am having some trouble putting a cell reference in my menu caption in VBA:

        With ContextMenu.Controls.Add(Type:=msoControlButton, before:=7)            .OnAction = "'" & ThisWorkbook.Name & "'!" & "RTO_MATERNITY"
            .FaceId = 484
            .Caption = "Custom Request"
        End With

I would like to have:

Sheets("Print").Range("Y3") & " " & Sheets("Print").Range("Y4")

Is this possible? The cell reference keeps appearing in my menu instead of the value of the cell.

 

[RickXL]

Hi,

Try:

.Caption = Sheets("Print").Range("Y3") & " " & Sheets("Print").Range("Y4")

 

[andrewb90]

Brilliant! thank you!

 

[RickXL]

You're welcome.

Regards,