Getting cells referenced while creating formula

[jariquelme]

Hi.

I need to get the range selected when I create a formula for use in Interop services.

For example, if i am creating this formula =SUM(A1:A10), I select the range by mouse.

There is any Excel interop property that provides me the A1:A10 range?



TIA

[p45cal]

msgbox Selection.address(0,0)
?

[jariquelme]

Hi p45cal.

I need to know the range selected while creating the formula. Selection.Address(0,0) is only set when I accept the edition.

TIA

[p45cal]

Originally Posted by jariquelme

Hi p45cal.

I need to know the range selected while creating the formula. Selection.Address(0,0) is only set when I accept the edition.

TIA

I haven't the foggiest what you mean but:

range("H5").Formula = "=SUM(" & Selection.address(0,0) & ")"

Perhaps if you described th background behind what you're trying to do…

[jariquelme]

Hi p45cal.

The background of the process is as following:

1.- Click in one cell.
2.- Put "=SUM(" to create a SUM.
3.- Select a range to sum with mouse.
4.- Getting the selected range in an Interop property to send to another application.
5.- Hit ENTER to save the data

In other forum, Hans Vogelaar tell me that object model can not expose data in edit mode. This could be the problem.

TIA.

[p45cal]

It is, but do you have to do it before the formula is committed to the sheet?
You could examine the formula directly after committing it to the sheet using a Worksheet_Change event. This in the sheet concerned's code-module:

Private Sub Worksheet_Change(ByVal Target As Range)
myString = Target.Formula
MsgBox myString
If UCase(Left(myString, 5)) = "=SUM(" Then
  strRng = Split(Split(myString, "(")(1), ")")(0)
  MsgBox strRng
End If
End Sub

Not very robust but with a few more IFs perhaps could be made to do what you want.

[macropod]

Cross-posted at:
https://www.excelguru.ca/forums/show...eating-formula
https://social.msdn.microsoft.com/Fo...forum=exceldev

For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184