How do I increment a cell by 1 using a form button?

[Guest]

Hi,

I wish to use a form button to run a macro that will increment one of three
cells by one. I want to keep a running total of how many units (budget,
standard or deluxe)have been ordered. e.g. if A1="Deluxe" then increment A2
by 1 or if A1="Budget" then increment B2 by 1.

Can anyone advise please?

 

[Dave Peterson]

I think that this will work ok:

Option Explicit
Sub testme01()

Dim myCellToInspect As Range
Dim myCelltoAdjust As Range

With ActiveSheet
Set myCellToInspect = .Range("a1")
Select Case LCase(myCellToInspect.Value)
Case Is = "deluxe"
Set myCelltoAdjust = .Range("A2")
Case Is = "budget"
Set myCelltoAdjust = .Range("b2")
Case Else
Set myCelltoAdjust = Nothing
End Select

If myCelltoAdjust Is Nothing Then
MsgBox "Not a valid value in: " & myCellToInspect.Address(0, 0)
Else
If IsNumeric(myCelltoAdjust.Value) Then
myCelltoAdjust.Value = myCelltoAdjust + 1
Else
MsgBox "non-numeric data in: " & myCelltoAdjust.Address(0, 0)
End If
End If
End With

End Sub

But be careful. If you click too many times, your counts will be off and you
won't have a record of what was there.

I think I'd just enter the data going down a column and count from there. In
fact, you could put other information on that row, too.

 

[Debra Dalgleish]

On a separate worksheet, you could keep a list of the types ordered.
Then, in cell A2, use the COUNTIF function to calculate the total. For
example, with "Deluxe" in cell A3 on the Data Entry sheet, enter the
following formula in cell A2:
=COUNTIF(Orders!$B:$B,A3)
Copy the formula across to column C.

The following macro will record the orders on a sheet named Orders (add
headings "Date" and "Type" in row 1:

'========================
Sub RecordOrder()
Dim wsEntry As Worksheet
Dim wsOrders As Worksheet
Dim r As Long
Set wsEntry = Worksheets("Data Entry")
Set wsOrders = Worksheets("Orders")

r = wsOrders.Cells.SpecialCells(xlLastCell).Row + 1
With wsOrders
.Cells(r, 1).Value = Date
.Cells(r, 2).Value = wsEntry.Cells(1, 1).Value
End With

End Sub
'======================

 

[Guest]

Thanks Dave & Debra

I have tried both and yes, they both work as stated. I have gone for Debra's
solution in the end as I have developed the idea of having a whole sheet
devoted to recording transactions. Also there was the possibility, as you
pointed out Dave, that unintentional increments could occur.

This is for a project that I am developing for students and will be most
helpful.

Thanks again to both of you.