Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another 'Copy To The Next Available Row' Question | Excel Discussion (Misc queries) | |||
'select table row' | Excel Discussion (Misc queries) | |||
Pivot Table - filtering 'row' and then hide it | Charts and Charting in Excel | |||
Another 'IF cell contains THEN color row' Question | Excel Discussion (Misc queries) | |||
change row' height | New Users to Excel |