Grossery Budget spred sheet.

[Simphiwe Zondi]

Hi,
I want to create a grossery budget spread sheet, but I don't know
where to start. My idea is to create a grossery data base which I have
done by capturing all my monthly grossery slips on to a spread sheet,
I have a column for the Items and one for the prices, but now I'm
stuck. The plan was to be able to tick each Item required that month
and this will calculate the total amount and print out the list of
items required that particular month. please help I'm really
stuck....do I use Excel VB, Macros or just formulars?

 

[Otto Moehrbach]

I made up a little file that will do what you want. If you wish, send me an
email and I will send you the file along with instructions. My email is
(e-mail address removed). Remove the "extra" from this address.
Reference the name Zondi in your email. HTH Otto

 

[Jeff]

Create a Userforn:
Open VBE
Insert A UserForm
Add 2 Label Controls
Add Listbox Control

Change List Box Properties
Column Count = 2
MutiSelect = 1-fmMultiselectMulti
ListStyle = 1-frmlistStyleOption

This can also be done @ runtime like this
Private Sub UserForm_Initialize()
With Me.ListBox1
.ColumnCount = 2
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectMulti
End With
End Sub

‘Here is what I came up with
‘****************************
Option Explicit

Private Sub ListBox1_Change()
Me.Label2.Caption = "Subtotal: " & Format(GetSubtotal, "$ #,##.00")
Me.Label3.Caption = "Total: " & Format(AddSalesTax(GetSubtotal), "$
#,##.00")
End Sub

Private Sub UserForm_Initialize()
Dim LastRow As Long
Dim ws As Worksheet

Set ws = ActiveWorkbook.Worksheets(1)
LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

With Me.ListBox1
.ColumnCount = 2
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectMulti
.RowSource = "A2:B" & LastRow
End With
End Sub


Private Function GetSubtotal() As Double
Dim i As Long
Dim x As Long
Dim ArrSubtotal() As Double
Dim SubTotal As Double

ReDim ArrSubtotal(ListBox1.ListCount)
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
ArrSubtotal(i) = ListBox1.List(i, 1)
End If
Next

For x = 0 To UBound(ArrSubtotal)
SubTotal = SubTotal + ArrSubtotal(x)
Next
GetSubtotal = SubTotal
End Function

Private Function AddSalesTax(ByVal SaleAmount As Double, _
Optional ByVal Tax As Double = 0.0825) As Double
AddSalesTax = SaleAmount * (1 + Tax)
End Function