Tech Support Guy banner
Status
Not open for further replies.

Excel VBA: Subtract from a corresponding cell based on button placement

1K views 7 replies 3 participants last post by  rotvik 
#1 ·
Hi guys,
I need to write a macro that is connected to many buttons, one for each row in spreadsheet, ask for a value to subtract, error check and (if ok) subtract from a corresponding value in column P.

When the button on a left hand side is clicked:

  • a dialog button should pop-up prompting a user to input the number of pieces to subtract from the corresponding cell (row based on the row of the button, column P)
  • error check the input number (show error if the value is smaller than 0 or larger than the corresponding value) ... I can write the error check by myself, so no need to do this step :up:
  • subtract the number from the corresponding cell (i.e. user clicked a button located in cell A9, input 9,24 pieces in a dialog box and there are 15,97 pieces in P9 => the pieces in P9 should change to 6,73)
Thank you for helping me out! ;)
Hope, the description is not very confusing.

Michal
 
See less See more
#8 ·
After a really looong time, I've managed to write the following code:

Sub SubtractPieces()

Dim Hcell As Range
Dim PiecesInput As Variant

With ActiveSheet.Shapes(Application.Caller) 'with pressed button
Set Hcell = Range(.TopLeftCell, .TopLeftCell) 'record its location
End With

' Cells(Hcell.Row, Hcell.Column + 9).Select

PiecesInput = InputBox("Kolik kusů " & Cells(Hcell.Row, Hcell.Column + 7) & " (" & Cells(Hcell.Row, Hcell.Column + 8) & ") chce klient prodat?", "", Cells(Hcell.Row, Hcell.Column + 9)) 'third value is default value, should be the actual number of pieces

If PiecesInput >= 0 And PiecesInput <= Cells(Hcell.Row, Hcell.Column + 9).Value Then 'OK
Cells(Hcell.Row, Hcell.Column + 9).Value = Cells(Hcell.Row, Hcell.Column + 9).Value - PiecesInput

Else
MsgBox "K prodeji je dostupných " & Cells(Hcell.Row, Hcell.Column + 9) & " ks"
End If

End Sub
But I still have a problem with the "If PiecesInput >= 0 And PiecesInput <= Cells(Hcell.Row, Hcell.Column + 9).Value Then" part. Cells(Hcell.Row, Hcell.Column + 9).Value is i.e. 3000 but the macro doesn't work unless I write directly"If PiecesInput >= 0 And PiecesInput <= 3000 Then" (which would be incorrect for different rows).
Any ideas on how to remedy this?
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top