Creating command buttons beside every row to execute each row macro

[sirriff]

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

 

[zantor]

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

 

[Iain King]

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

 

[John]

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