Checkbox - clearing a cell

[Bill D.]

I have a list of items and prices which can be selected with a checkbox.
When the checkbox is selected the unit price is added to the sub total.
When checkbox is deselected the unit price is subtracted from the sub
total.

There will be cases where the unit price cell is empty. When the
checkbox is selected the user will enter a price via an input box . In
this instance when the checkbox is deselected the unit price must be
subtracted from the sub total AND the unit price cell cleared.

Example:

Item 1 $100
Item 2 $125
Item 3 blank cell
Item 4 $25

I have the following code that works EXCEPT it will not clear the price
cell when the checkbox is deselected (OFF)

MyRoutineCheckBox:
With ActiveSheet.CheckBoxes(buttonclicked)
If ActiveSheet.CheckBoxes(buttonclicked).Value = _
xlOn Then
If Cells(.TopLeftCell.Row, vColumn) = "" Then _
Cells(.TopLeftCell.Row, vColumn) = _
Application.InputBox(Prompt:="Enter Amount")
End If
Cells(vSubTotalRow, vColumn) = Cells(vSubTotalRow, _
vColumn) + Cells(.TopLeftCell.Row, vColumn)
Else
Cells(vSubTotalRow, vColumn) = Cells(vSubTotalRow, _
vColumn) - Cells(.TopLeftCell.Row, vColumn)
End If
End With
Return

When the checkbox is selected I test for an empty cell and open the
input box. I need a way to set a flag for that cell so when the checkbox
is deselected I can test to see if the contents of the cell should be
deleted.

Bill

There are 10 types of people; those that understand binary and those
that don't.

 

[Tom Ogilvy]

when you create the price using an inputbox, put it in as a formula. Then
you can check the cell and if it has a formula, clear it, otherwise leave it
alone

ans = InputBox
cell.Formula = "=" & ans


if cell.Hasformula then
cell.ClearContents
End if

 

[Bob Phillips]

Not tested, but you could set some other attribute, like

MyRoutineCheckBox:
With ActiveSheet.CheckBoxes(buttonclicked)
If ActiveSheet.CheckBoxes(buttonclicked).Value = xlOn Then
If Cells(.TopLeftCell.Row, vColumn) = "" Then _
Cells(.TopLeftCell.Row, vColumn) = _
Application.InputBox(Prompt:="Enter Amount")
Cells(.TopLeftCell.Row, vColumn).Font.Underline = _
xlUnderlineStyleSingle
End If
Cells(vSubTotalRow, vColumn) = Cells(vSubTotalRow, _
vColumn) + Cells(.TopLeftCell.Row, vColumn)
Else
Cells(vSubTotalRow, vColumn) = Cells(vSubTotalRow, _
vColumn) - Cells(.TopLeftCell.Row, vColumn)
If Cells(.TopLeftCell.Row, vColumn).Font.Underline = _
xlUnderlineStyleSingle Then _
Cells(.TopLeftCell.Row, vColumn).Font.Underline = _
xlUnderlineStyleNone
End If
End With
Return


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bill D.]

Thanks Tom and Bob for your help.

Tom, your approach never occured to me. Need to add that to my ever
growing file of information.

Bob, I modified your code a bit to use italics. Hadn't thought of that
before but it occured to me that having the user input values in italics
would call attention to the fact that the price was not a standard
price. Later when reviewing a printed file copy it would stand out.

Thanks all.

Bill

There are 10 types of people; those that understand binary and those
that don't.