Excel 10 Userform to Count Cash

J

jhedges

Board Regular
Joined
May 27, 2009
Messages
208
Hello,

I'm working on creating a cash counter allowing users to count loose coin, rolled coin and loose currency at their site via a VBA userform where the data will then populate either a protected or hidden worksheet, setup as a listobject, stored on a shared drive for the only the office manager (and myself) to maintain. I have the userform partially working and need some help finalizing the project.

Help needed:

  1. Autonumber the ID field txtID, which is a textbox on the userform and located in column A of the listobject(table1) on the CSI Cash Register Cash Count worksheet.
  2. I have the coin (loose and rolled) textboxes calculating correctly with the values being placed in labels and this code is below:
Code: 
Private Sub CommandButton4_Click()
    Label35.Caption = Val(txtPennies.Text) * 0.01 + Val(txtRollPennies) * 0.5
    Cells(1, 3).Value = Label35.Caption
    Label35.Caption = VBA.Format(Label35, "Currency")
    
    Label36.Caption = Val(txtNickles.Text) * 0.05 + Val(txtRollNickles) * 2
    Cells(1, 3).Value = Label36.Caption
    Label36.Caption = VBA.Format(Label36, "Currency")
    
    Label37.Caption = Val(txtDimes.Text) * 0.1 + Val(txtRollDimes) * 5
    Cells(1, 3).Value = Label37.Caption
    Label37.Caption = VBA.Format(Label37, "Currency")
    
    Label38.Caption = Val(txtQuarters.Text) * 0.25 + Val(txtRollQuarters) * 10
    Cells(1, 3).Value = Label38.Caption
    Label38.Caption = VBA.Format(Label38, "Currency")
    
    Label39.Caption = Val(txtFiftyCent.Text) * 0.5 + Val(txtRollFiftyCent) * 10
    Cells(1, 3).Value = Label39.Caption
    Label39.Caption = VBA.Format(Label39, "Currency")
    
    Label40.Caption = Val(txtDollarCoin.Text) * 1 + Val(txtRollDollarCoin) * 25
    Cells(1, 3).Value = Label40.Caption
    Label40.Caption = VBA.Format(Label40, "Currency")

    Label42.Caption = Val(txtOnes.Text) * 1
    Cells(1, 3).Value = Label42.Caption
    Label42.Caption = VBA.Format(Label42, "Currency")

    Label43.Caption = Val(txtFives.Text) * 5
    Cells(1, 3).Value = Label43.Caption
    Label43.Caption = VBA.Format(Label43, "Currency")
    
    Label44.Caption = Val(txtTens.Text) * 10
    Cells(1, 3).Value = Label44.Caption
    Label44.Caption = VBA.Format(Label44, "Currency")

    Label45.Caption = Val(txtTwenty.Text) * 20
    Cells(1, 3).Value = Label45.Caption
    Label45.Caption = VBA.Format(Label45, "Currency")

    Label46.Caption = Val(txtFifty.Text) * 50
    Cells(1, 3).Value = Label46.Caption
    Label46.Caption = VBA.Format(Label46, "Currency")
    
    Label47.Caption = Val(txtHundred.Text) * 100
    Cells(1, 3).Value = Label47.Caption
    Label47.Caption = VBA.Format(Label47, "Currency")

End Sub

4. I cannot figure out how to get the total coin labels for each denomination to sum and display in Label48 (Total Coin) and the total currency labels for each denomination to sum the total currency and display in Label49 (Total Currency). I would then like these two labels to sum their totals and display in Label50 (Total Cash). Also, possibly there is a more efficient way to accomplish what I have so far...

5. I have four commanbuttons on the userform - Commandbutton4 (Calculate) is partially working indicated in #4. Commandbutton1 (Add) is not working and is the button I would like to utilize to validate the userform data and add the userform data into the listobject (table1) on my worksheet. I have tried a few things to no avail. Commandbutton2 (clear) is partially working and is the button I would like to utilize to clear the form. It currently clears the textboxes; however, the label values remain. I would like for the entire userform to reset allowing another user to enter data. I have the commandbutton code below in module1 and I call them from the userform frmCashCount code.

Any help would be appreciated...

Code: 
Sub ClearForm()
Dim z As Control


For Each z In frmCashCount.Controls


If TypeName(z) = "TextBox" Then
z.Value = ""


Else
    If TypeName(z) = "ComboBox" Then
    z.Value = ""


    End If


End If
Next z
frmCashCount.Repaint
End Sub

Code: 
Sub Add()
Dim i As Integer
Dim nextrow As Integer


On Error Resume Next
'Validate Comboxes
If frmCashCount.Date1.Value = "" Then
    frmCashCount.Date1.SetFocus
    MsgBox "'Date' is a mandatory field...", vbOKOnly, "Required Field"
Exit Sub
ElseIf frmCashCount.ComboBox1.Value = "" Then
    frmCashCount.ComboBox1.SetFocus
    MsgBox "'Site' is a mandatory field...", vbOKOnly, "Required Field"
Exit Sub
ElseIf frmCashCount.ComboBox2.Value = "" Then
    frmCashCount.ComboBox2.SetFocus
    MsgBox "'Staff' is a mandatory field...", vbOKOnly, "Required Field"
Exit Sub
ElseIf frmCashCount.ComboBox3.Value = "" Then
    frmCashCount.ComboBox3.SetFocus
    MsgBox "'Shift' is a mandatory field...", vbOKOnly, "Required Field"
End If

I also have a notes textbox txtNotes that I would like the add button to place the data into my listobject as a comment indicator.

Here is my Listobject:
CSI Cash Register Cash Count

*ABCDEFGHIJKLMNOPQRSTUVW
4*****Total CoinTotal CurrencyReconcilationComments
5IDDateSiteStaffShiftPenniesNicklesDimesQuartersF iftyDollarTotal CoinOnesFivesTensTwentiesFiftiesHundredsTotal CurrencyTotal CashCSI Cash ReconciliationTotal DepositNotes
61######MBCRachaelOpener******************

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Here is my remaining code:

Code: 
Option Explicit


Private Sub combobox3_change()
    Select Case ComboBox3.ListIndex
        Case 0
             Me.txtCSIRecon.Visible = False
             Me.Label51.Visible = False
             Me.Label52.Visible = False
             Me.Label31.Visible = False
        Case 1
             Me.txtCSIRecon.Visible = True
             Me.Label51.Visible = True
             Me.Label52.Visible = True
             Me.Label31.Visible = True
    End Select
End Sub

Private Sub Userform_Initialize()
    
Me.Label53.Caption = Date


ComboBox1.SetFocus


With ComboBox1
    .AddItem "FHCIC"
    .AddItem "GO"
    .AddItem "MBC"
    .AddItem "WHBC"
End With


With ComboBox3
    .AddItem "Opener"
    .AddItem "Closer"
End With


End Sub


Private Sub combobox1_change()


Dim index As Integer
index = ComboBox1.ListIndex


ComboBox2.Clear


Select Case index
    Case Is = 0
        With ComboBox2
            .AddItem "Lyndsay"
            .AddItem "Nic"
            .AddItem "Rob"
            .AddItem "Celine"
            .AddItem "Dawn"
            .AddItem "Doug"
            .AddItem "Jason"
            .AddItem "Jen"
            .AddItem "Kim"
            .AddItem "Meggan"
            .AddItem "Nancy"
            .AddItem "Rachael"
            .AddItem "Sarah"
        End With
    Case Is = 1
        With ComboBox2
            .AddItem "Doug"
            .AddItem "Kim"
            .AddItem "Nancy"
            .AddItem "Rob"
            .AddItem "Sarah"
            .AddItem "Celine"
            .AddItem "Dawn"
            .AddItem "Doug"
            .AddItem "Jason"
            .AddItem "Jen"
            .AddItem "Kim"
            .AddItem "Meggan"
            .AddItem "Nic"
            .AddItem "Rachael"
        End With
    Case Is = 2
        With ComboBox2
            .AddItem "Celine"
            .AddItem "Jason"
            .AddItem "Rachael"
            .AddItem "Dawn"
            .AddItem "Doug"
            .AddItem "Jen"
            .AddItem "Kim"
            .AddItem "Lyndsay"
            .AddItem "Meggan"
            .AddItem "Nancy"
            .AddItem "Nic"
            .AddItem "Rob"
            .AddItem "Sarah"
          
        End With
        Case Is = 3
        With ComboBox2
            .AddItem "Dawn"
            .AddItem "Jen"
            .AddItem "Meggan"
            .AddItem "Celine"
            .AddItem "Doug"
            .AddItem "Jason"
            .AddItem "Kim"
            .AddItem "Lyndsay"
            .AddItem "Nancy"
            .AddItem "Nic"
            .AddItem "Rob"
            .AddItem "Sarah"
            .AddItem "Rachael"
        End With
End Select


End Sub


'Private Sub TextBox1_Change()


'GetData


'End Sub


Private Sub CommandButton1_Click()


Add


End Sub


Private Sub CommandButton2_Click()


ClearForm


End Sub




Private Sub CommandButton3_Click()


Unload Me


End Sub
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Update

I have the following Sub add() code working; however, it will only insert two rows into my Excel 10 listobject, then begins overwriting the second row. Does anyone have any ideas on how to get it to continue inserting new rows into my listobject to capture each data entry from the userform?


Code: 
Sub Add()
Dim tblCash As Integer
tblCash = WorksheetFunction.CountA(Sheets("CSI Cash Register Cash Count").Range("B6")) + 6
'ActiveSheet.Unprotect Password:="xxxxx"
'On Error Resume Next
'Validate Comboxes
If frmCashCount.ComboBox1.Value = "" Then
MsgBox "'Site' is a mandatory field...", vbOKOnly, "Required Field"
frmCashCount.ComboBox1.SetFocus
Exit Sub
ElseIf frmCashCount.ComboBox2.Value = "" Then
MsgBox "'Staff' is a mandatory field...", vbOKOnly, "Required Field"
frmCashCount.ComboBox2.SetFocus
Exit Sub
ElseIf frmCashCount.ComboBox3.Value = "" Then
MsgBox "'Shift' is a mandatory field...", vbOKOnly, "Required Field"
frmCashCount.ComboBox3.SetFocus
Exit Sub
End If

Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 2).Value = frmCashCount.Label53.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 3).Value = frmCashCount.ComboBox1.Value
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 4).Value = frmCashCount.ComboBox2.Value
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 5).Value = frmCashCount.ComboBox3.Value
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 6).Value = frmCashCount.Label35.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 7).Value = frmCashCount.Label36.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 8).Value = frmCashCount.Label37.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 9).Value = frmCashCount.Label38.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 10).Value = frmCashCount.Label39.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 11).Value = frmCashCount.Label40.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 13).Value = frmCashCount.Label42.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 14).Value = frmCashCount.Label43.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 15).Value = frmCashCount.Label44.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 16).Value = frmCashCount.Label45.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 17).Value = frmCashCount.Label46.Caption
Worksheets("CSI Cash Register Cash Count").Cells(tblCash, 18).Value = frmCashCount.Label47.Caption
'ActiveSheet.Protect Password:="xxxxx", AllowFiltering:=True
Unload frmCashCount
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Add msgbox if value not found
Replies
5
Views
201
nemmi69
nemmi69
T
  • Question
Loop through labels on mulitpage control?
Replies
1
Views
520
JLGWhiz
JLGWhiz
A
  • Question
Use combobox value & textbox to fill a variable cell
Replies
1
Views
928
Adrienk
A
H
  • Question
Shorten Code
Replies
4
Views
634
Norie
Norie
G
  • Question
vb working in excel 2007 not in 2003
Replies
1
Views
493
GerardAcision
G

Forum statistics

Threads
1,214,839
Messages
6,121,891
Members
449,058
Latest member
Guy Boot

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top