How to get the 'current row' from VBA
 

Hi,

I want to execute a macro and let it read/store some values in de current
row.

My intention was to place a graphical object in a row, attach the macro to
it
and let the macro 'dectect the "current row'' and read/store values row
related.
And do this for several rows, that all execute the same macro.

How to solve this or are there smarter solutions!
(I want to execute this for any single row)


Regards Peter

How to get the 'current row' from VBA
 

Hi Peter

Check out the TopLeftCell/BottomRightCell property.

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

"Peter van de Kerkhof" skrev i en meddelelse
.. .
Hi,

I want to execute a macro and let it read/store some values in de current
row.

My intention was to place a graphical object in a row, attach the macro to
it
and let the macro 'dectect the "current row'' and read/store values row
related.
And do this for several rows, that all execute the same macro.

How to solve this or are there smarter solutions!
(I want to execute this for any single row)


Regards Peter

How to get the 'current row' from VBA
 

In addition, to determine which graphical object was called, use the
application.Caller property in attached macro. It will return a string with
the name of the graphical object that fired the macro.

Regards,
Tom Ogilvy


Peter van de Kerkhof wrote in message
.. .
Hi,

I want to execute a macro and let it read/store some values in de current
row.

My intention was to place a graphical object in a row, attach the macro to
it
and let the macro 'dectect the "current row'' and read/store values row
related.
And do this for several rows, that all execute the same macro.

How to solve this or are there smarter solutions!
(I want to execute this for any single row)


Regards Peter

How to get the 'current row' from VBA
 

I.'m sorry Leo but that didn't help me much.

Yes I've found the TopLeftCell/BottomRightCell property but don't know how
to apply it.


Can you give a sample like:

sub Updateline()
summarybook = Excel.ThisWorkbook.Name
MsgBox Workbooks(summarybook).Worksheets(DevList).TopLeft Cell
end sub

regards Peter

"Leo Heuser" schreef in bericht
...
Hi Peter

Check out the TopLeftCell/BottomRightCell property.

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

"Peter van de Kerkhof" skrev i en meddelelse
.. .
Hi,

I want to execute a macro and let it read/store some values in de
current
row.

My intention was to place a graphical object in a row, attach the macro
to
it
and let the macro 'dectect the "current row'' and read/store values
row
related.
And do this for several rows, that all execute the same macro.

How to solve this or are there smarter solutions!
(I want to execute this for any single row)


Regards Peter

How to get the 'current row' from VBA
 

Works like a charm

Thanks Peter

"Dave Peterson" schreef in bericht
...
Something like this within the common macro:

Option Explicit
Sub myCommonMacro()

Dim myShape As Shape

Set myShape = ActiveSheet.Shapes(Application.Caller)

MsgBox myShape.TopLeftCell.Address & vbLf & _
myShape.BottomRightCell.Address

End Sub


myshape.topleftcell.row might be what you want.


Peter van de Kerkhof wrote:

I.'m sorry Leo but that didn't help me much.

Yes I've found the TopLeftCell/BottomRightCell property but don't know
how
to apply it.

Can you give a sample like:

sub Updateline()
summarybook = Excel.ThisWorkbook.Name
MsgBox Workbooks(summarybook).Worksheets(DevList).TopLeft Cell
end sub

regards Peter

"Leo Heuser" schreef in bericht
...
Hi Peter

Check out the TopLeftCell/BottomRightCell property.

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

"Peter van de Kerkhof" skrev i en meddelelse
.. .
Hi,

I want to execute a macro and let it read/store some values in de
current
row.

My intention was to place a graphical object in a row, attach the
macro
to
it
and let the macro 'dectect the "current row'' and read/store
values
row
related.
And do this for several rows, that all execute the same macro.

How to solve this or are there smarter solutions!
(I want to execute this for any single row)


Regards Peter





--

Dave Peterson

How to get the 'current row' from VBA
 

I have an answer already in another thread.

But your solution (as far as I can tel) has a focus problem.
Meaning: when I want to start a macro I don't care which cell has the focus.
I want to start a macro whith that specific row.

Again the solution presented in the other thread works fine.

Thanks.

"Kevin" schreef in bericht
...
ActiveCell.Row will return the row of the active cell.
Use offset(0,X) or Sheets("sheet1").Cells(RowNum, X)to
read/store values in cells of the same row.

Is that what you're looking for?

kpd


-----Original Message-----
Hi,

I want to execute a macro and let it read/store some
values in de current
row.

My intention was to place a graphical object in a row,
attach the macro to
it
and let the macro 'dectect the "current row'' and
read/store values row
related.
And do this for several rows, that all execute the same
macro.

How to solve this or are there smarter solutions!
(I want to execute this for any single row)


Regards Peter


.

How to get the 'current row' from VBA
 

Hi Peter,
Or carrying Kevin's suggestion into an actual example without
any graphic, initiated on a double-click in column A.
Double-click on any other column would be ignored.

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
'to install -- right-click on the sheettab of the sheet to
' be used in and choose 'view code'. Paste this Worksheet
' event macro into the module.
If Target.Column < 1 Then Exit Sub
MsgBox "You could run a macro here " & Chr(10) & _
"you poked cell " & Target.Address(0, 0) & Chr(10) & _
"The cell two columns to the right displays: " & _
Target.Offset(0, 2).Text
End Sub .

Worksheet Events and Workbook Events
http://www.mvps.org/dmcritchie/excel/event.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Kevin" wrote in message ...
ActiveCell.Row will return the row of the active cell.
Use offset(0,X) or Sheets("sheet1").Cells(RowNum, X)to
read/store values in cells of the same row.

"Peter van de Kerkhof" wrote in
I want to execute a macro and let it read/store some
values in de current
row.

My intention was to place a graphical object in a row,
attach the macro to
it
and let the macro 'dectect the "current row'' and
read/store values row
related.
And do this for several rows, that all execute the same
macro.

How to solve this or are there smarter solutions!
(I want to execute this for any single row)


Regards Peter


.

How to get the 'current row' from VBA
 

Why jump on Kevin because he didn't give you a dick and jane sample like
Dave did?

Between Leo and My post you already had all the information Dave gave you -
apparently you couldn't put it together - so no reason to criticize Kevin
for offering some ideas - even if they didn't meet your needs.

--
Regards,
Tom Ogilvy


Peter van de Kerkhof wrote in message
.. .
I have an answer already in another thread.

But your solution (as far as I can tel) has a focus problem.
Meaning: when I want to start a macro I don't care which cell has the
focus.
I want to start a macro whith that specific row.

Again the solution presented in the other thread works fine.

Thanks.

"Kevin" schreef in bericht
...
ActiveCell.Row will return the row of the active cell.
Use offset(0,X) or Sheets("sheet1").Cells(RowNum, X)to
read/store values in cells of the same row.

Is that what you're looking for?

kpd


-----Original Message-----
Hi,

I want to execute a macro and let it read/store some
values in de current
row.

My intention was to place a graphical object in a row,
attach the macro to
it
and let the macro 'dectect the "current row'' and
read/store values row
related.
And do this for several rows, that all execute the same
macro.

How to solve this or are there smarter solutions!
(I want to execute this for any single row)


Regards Peter


.

How to get the 'current row' from VBA
 

Hey,

It was never my intention to disrespect Kevin's answer or his intention and
knowledge
I am very pleased with every sugegstion I get/got!

But with the first replies I had all my problems solved!
I just found it polite to answer that responce and give some possible (im
my opion)
drawbacks why this particular answer probably ( as far as I know)
wouldn't hellp me to find a better solution.

The cell focus problem is solved if I can start the macro from that specific
cell.
(Not acidentally when another cell is in focus)
Thats why I posed another question: "How to use a cell text to call a macro
?"
The 'double' click answer seems a solution but I haven't tested it yet.


Regards Peter

--
"Tom Ogilvy" schreef in bericht
...
Why jump on Kevin because he didn't give you a dick and jane sample like
Dave did?

Between Leo and My post you already had all the information Dave gave
you -
apparently you couldn't put it together - so no reason to criticize Kevin
for offering some ideas - even if they didn't meet your needs.

--
Regards,
Tom Ogilvy


Peter van de Kerkhof wrote in message
.. .
I have an answer already in another thread.

But your solution (as far as I can tel) has a focus problem.
Meaning: when I want to start a macro I don't care which cell has the
focus.
I want to start a macro whith that specific row.

Again the solution presented in the other thread works fine.

Thanks.

"Kevin" schreef in bericht
...
ActiveCell.Row will return the row of the active cell.
Use offset(0,X) or Sheets("sheet1").Cells(RowNum, X)to
read/store values in cells of the same row.

Is that what you're looking for?

kpd


-----Original Message-----
Hi,

I want to execute a macro and let it read/store some
values in de current
row.

My intention was to place a graphical object in a row,
attach the macro to
it
and let the macro 'dectect the "current row'' and
read/store values row
related.
And do this for several rows, that all execute the same
macro.

How to solve this or are there smarter solutions!
(I want to execute this for any single row)


Regards Peter


.

How to get the 'current row' from VBA
 

BeforeDoubleclick should work fine if you want to use the double click as a
trigger.

If you wanted to use the right click then you would use BeforeRightClick and
so forth.


Although, Dave's code, as written, assumes the name of the macro to run is
contained in the cell.

if you are calling a single macro and it needs to know what cell triggered
the action, you would need to pass in the target

On Error Resume Next
Application.Run Target.Value
If Err.Number < 0 Then
MsgBox "didn't work"
Err.Clear
End If
On Error GoTo 0


would be replace with

UpDateData target

and the macro modified to take an argument


Sub UpdateData(rng as Range)

for each cell in Range(cells(rng.row,1),cells(rng.row,9))

Next

End Sub

--
Regards,
Tom Ogilvy



Peter van de Kerkhof wrote in message
.. .
Hey,

It was never my intention to disrespect Kevin's answer or his intention
and
knowledge
I am very pleased with every sugegstion I get/got!

But with the first replies I had all my problems solved!
I just found it polite to answer that responce and give some possible (im
my opion)
drawbacks why this particular answer probably ( as far as I know)
wouldn't hellp me to find a better solution.

The cell focus problem is solved if I can start the macro from that
specific
cell.
(Not acidentally when another cell is in focus)
Thats why I posed another question: "How to use a cell text to call a
macro
?"
The 'double' click answer seems a solution but I haven't tested it yet.


Regards Peter

--
"Tom Ogilvy" schreef in bericht
...
Why jump on Kevin because he didn't give you a dick and jane sample like
Dave did?

Between Leo and My post you already had all the information Dave gave
you -
apparently you couldn't put it together - so no reason to criticize
Kevin
for offering some ideas - even if they didn't meet your needs.

--
Regards,
Tom Ogilvy


Peter van de Kerkhof wrote in message
.. .
I have an answer already in another thread.

But your solution (as far as I can tel) has a focus problem.
Meaning: when I want to start a macro I don't care which cell has the
focus.
I want to start a macro whith that specific row.

Again the solution presented in the other thread works fine.

Thanks.

"Kevin" schreef in bericht
...
ActiveCell.Row will return the row of the active cell.
Use offset(0,X) or Sheets("sheet1").Cells(RowNum, X)to
read/store values in cells of the same row.

Is that what you're looking for?

kpd


-----Original Message-----
Hi,

I want to execute a macro and let it read/store some
values in de current
row.

My intention was to place a graphical object in a row,
attach the macro to
it
and let the macro 'dectect the "current row'' and
read/store values row
related.
And do this for several rows, that all execute the same
macro.

How to solve this or are there smarter solutions!
(I want to execute this for any single row)


Regards Peter


.