How to get the 'current row' from VBA

  • Thread starter Thread starter Peter van de Kerkhof
  • Start date Start date
P

Peter van de Kerkhof

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
 
Hi Peter

Check out the TopLeftCell/BottomRightCell property.

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.
 
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
 
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).TopLeftCell
end sub

regards Peter
 
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.
 
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
 
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.
 
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

--
 
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
 
Back
Top