Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating command buttons beside every row to execute each row macro
Greetings,
thanks to anybody who reads this I got sales data exported as excel. each row contains an item and al its data, cost, tax, end price, shipping cost, and so on. basically i got a macro that records all the varous costs and tells m my end profit per item. but i keep adding more items every week so i was thinking about makin a row specific button that would execute the macro for that row only. that way i could calculate each new row only, or recalculate older row if i need to alter teh data. this would require a command button for each row, preferaby consistin of the size/shape of the last cell of each row. is this even possible? to make an entire colume of command buttons fro cells? the only other alternative would be to use an isempty thing to star from the top each time and skip over all the ones with answers alread filled, untill it gets to the new ones, and only calculate those. i am not sure exactly how to code either, but of course i would onl want to tackle the one that is simplist. any suggestions? i think from my limited vba sklls that the isempty route might b easiest. i am not sure how to make it check cells down a column, skip the row i it is already filled, or execute my existing sub if its empty. any help is appreciated, i spend 15 hrs making my simple profi calculator and this would just make it perfect. sirrif -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating command buttons beside every row to execute each row macro
Hi sirriff
Try the following: In the VB editor, add the code below to the sheet where you do you calcs. (Do not add it in a module). The code uses column 6 as your calculation column, you can change it to whatever your column is. Change the part that says Target.Value = ... to what you want to calculate. Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Target.Column = 6 Then If IsEmpty(Target.Value) Then Target.Value = Cells(Target.Row, 4) + Cells(Target.Row, 5) <== <Change this to your specific calculation) End If End If End Sub Hope it helps.. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating command buttons beside every row to execute each row macro
is this even possible? to make an entire colume of command buttons from
cells? the only other alternative would be to use an isempty thing to start from the top each time and skip over all the ones with answers already filled, untill it gets to the new ones, and only calculate those. Another option is to have one button which executes the macro for whatever row the active cell is in. Or, more complicated, loop through all the rows which are currently selected. ActiveCell.Row will return the row the activecell is on. To loop through all the selected rows: For Each singlearea In Selection.Areas For r = singlearea.row To singlearea.row + singlearea.rows.count - 1 doCalculation( r) 'or whetever you wnt to do to row r Next Next Iain King |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating command buttons beside every row to execute each rowmacro
Had same thing but created a macro that worked on the rows selected with
mouse then a button on the toolbar. Select, click, pretty simple John sirriff < wrote: Greetings, thanks to anybody who reads this I got sales data exported as excel. each row contains an item and all its data, cost, tax, end price, shipping cost, and so on. basically i got a macro that records all the varous costs and tells me my end profit per item. but i keep adding more items every week so i was thinking about making a row specific button that would execute the macro for that row only. that way i could calculate each new row only, or recalculate older rows if i need to alter teh data. this would require a command button for each row, preferaby consisting of the size/shape of the last cell of each row. is this even possible? to make an entire colume of command buttons from cells? the only other alternative would be to use an isempty thing to start from the top each time and skip over all the ones with answers already filled, untill it gets to the new ones, and only calculate those. i am not sure exactly how to code either, but of course i would only want to tackle the one that is simplist. any suggestions? i think from my limited vba sklls that the isempty route might be easiest. i am not sure how to make it check cells down a column, skip the row if it is already filled, or execute my existing sub if its empty. any help is appreciated, i spend 15 hrs making my simple profit calculator and this would just make it perfect. sirriff --- Message posted from http://www.ExcelForum.com/ -- R-e-t-u-r-n A-d-d-r-e-s-s I-n-s-t-r-u-c-t-i-o-n-s Change LID to |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Macro Buttons and formatting | Excel Discussion (Misc queries) | |||
How do I execute buttons in Excel using a voice command? | Excel Discussion (Misc queries) | |||
How do I execute command from button or hyperlink? | Excel Discussion (Misc queries) | |||
Execute a menu command with VBA? | Excel Programming | |||
creating buttons with a macro | Excel Programming |