How to use Executeexcel4macro to fill a formula in a textbox

[dineshtendulkar]

Hi

When am using like this, It is working fine

Sheets("BBS Sheet").Shapes("TextBox " & pictureRow & "1").SelectExecuteExcel4Macro "FORMULA(""=R4C9"")"

But When am using like this, it is not working what would be the problem

Sheets("BBS Sheet").Shapes("TextBox " & pictureRow & "1").Select
ExecuteExcel4Macro "Formula(""=Cells(pictureRow, "I")"")"


Please give me solution

 

[Jeffrey Lopez]

It looks like the quotes are giving the issue, I could be wrong... but give this a go:

Replace:

"Formula(""=Cells(pictureRow, "I")"")"

With:

"FORMULA(""=Cells(pictureRow,""I"")"")"

Since no other information was given, it is sorta of hard to test your code...

 

[dineshtendulkar]

Thanks for your Quick Response Mr. Jeffrey Lopez...

I will explain you my condition, I am having a textbox in a sheet. I want to type a formula inside the text box linking a particular cell. When am giving R1C1 style of formula it is working. But my code chooses different ranges of cell when move on from cell to cell. So I want to link the cell like giving external reference.

Sub First_Case()
Sheets("BBS Sheet").Shapes("TextBox 1").Select
ExecuteExcel4Macro "FORMULA(""=R4C9"")"
End Sub

This piece of code is working very fine writing a formula in the text box i.e =$I$4

Instead of Giving =R4C9 i want to give that reference as =cells(4,9) or =cells(a,b)

If i give like this it is not working. Will you check and give me the solution please

It looks like the quotes are giving the issue, I could be wrong... but give this a go:

Replace:

"Formula(""=Cells(pictureRow, "I")"")"

With:

"FORMULA(""=Cells(pictureRow,""I"")"")"

Since no other information was given, it is sorta of hard to test your code...

 

[Andrew Poulsom]

The Formula property expects a formula not some VBA code. Try:

ExecuteExcel4Macro "FORMULA(""=R" & pictureRow & "C9"")"

Or you can use the Address of the Range returned by the Cells property in R1C1 reference style:

ExecuteExcel4Macro "FORMULA(""=" & Cells(pictureRow, "I").Address(ReferenceStyle:=xlR1C1) & """)"

 

[dineshtendulkar]

Thank You Sir. You made my Day. Worked like Charm.

 

[Jeffrey Lopez]

Looks like Andrew Poulsom beat me to it, CELLS is not a valid formula in Excel, which the ExecuteExcel4macros is looking for... give his suggestions a go!

 

[dineshtendulkar]

Sir,

One more Question. I have made 4 shapes (3 text boxes and 1 freeform) as a group. This group was put under a Cell in Sheet 1. In sheet 2 depending on the shape name it will display the particular shape from sheet 1 in Next Cell.

The Groups and all the shape names in the pasted sheet 2 were all same.

Is there a Way to Select Shapes in a particular rows. For example, All shapes and groups present in the Row 1:1

Waiting for your reply

 

[dineshtendulkar]

Mr. Jeffrey Lopez / Andrew Poulsom,

Is it possible to select all shapes in a particular row alone. Row 1 has 5 shapes, Row 2 has 5 shapes, etc... When my formula updates in Row 1 then all row 1 shapes should be selected. Is it possible.

Thanks in advance