A B C
Day Month Year
21 June 2005

assume the 21 is in A5 on Sheet1

the subscript out of range error would be caused if you didn't have a
worksheet named Sheet2 or you didn't have a worksheet named sheet2 or if you
did have a worksheet named sheet1, it didn't contain a listbox from the
control toolbox toolbar with a name of Listbox1. The code worked fine for
me with those names.

If you you objects don't have those names, correct the code to reflect the
names you have.


Sub ABC()
Dim dt As Date
Dim sh As Worksheet
Dim tot As Double
Dim rng As Range, rng1 As Range
Dim cell As Range
Dim lb As MSForms.ListBox
Dim cnt As Long
set sh = Worksheets("Sheet1")
dt = DateValue(sh1.Cells(5,"B") & " " & sh1.Cells(5,"A") & ", " & _
sh1.Cells(5,"C"))

With Worksheets("Sheet2")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With

Set lb = sh.OLEObjects("Listbox1").Object
Set rng1 = sh.OLEObjects("Listbox1").BottomRightCell
' assume dates on sheet2 are in column 4
tot = 0
lb.Clear
For Each cell In rng
If cell.Offset(0, 3) < dt Then
lb.AddItem cell
cnt = lb.ListCount - 1
lb.List(cnt, 1) = cell.Offset(0, 1)
lb.List(cnt, 2) = cell.Offset(0, 2)
lb.List(cnt, 3) = cell.Offset(0, 3).Text
lb.List(cnt, 4) = cell.Offset(0, 4)
tot = tot + cell.Offset(0, 4).Value
End If
Next
rng1.Offset(1, 0).Value = tot
End Sub

--
Regards,
Tom Ogilvy


"A.G.M ash" wrote in message
...
hi

thanks for your reply your amazing

and im thick

I have tried it and I keep getting runtime error '9': subscript out of
range.
also the date is not in one column there is individual columns for dd mm
as
text (i.e august) and yyyy


like this

A B C
Day Month Year
21 June 2005

can you tell me how to change the program to accomadate this format
instead
of using dt as Date


and how do I use drop dwon boxes to choose the filter. I'm thinkin use a
linked cell for a dropdown box to put in the value and then refer dt say
to
sheetX.Cells("1","a")

"Tom Ogilvy" wrote:

there was a typo:

Set rng1.Offset(1, 0).Value = tot


should be

rng1.Offset(1, 0).Value = tot

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Sub ABC()
Dim dt As Date
Dim sh As Worksheet
Dim tot As Double
Dim rng As Range, rng1 As Range
Dim cell As Range
Dim lb As MSForms.ListBox
Dim cnt As Long
dt = DateValue("1/5/2007")

With Worksheets("Sheet2")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With

Set sh = Worksheets("Sheet1")
Set lb = sh.OLEObjects("Listbox1").Object
Set rng1 = sh.OLEObjects("Listbox1").BottomRightCell
' assume dates on sheet2 are in column 4
tot = 0
lb.Clear
For Each cell In rng
If cell.Offset(0, 3) < dt Then
lb.AddItem cell
cnt = lb.ListCount - 1
lb.List(cnt, 1) = cell.Offset(0, 1)
lb.List(cnt, 2) = cell.Offset(0, 2)
lb.List(cnt, 3) = cell.Offset(0, 3).Text
lb.List(cnt, 4) = cell.Offset(0, 4)
tot = tot + cell.Offset(0, 4).Value
End If
Next
Set rng1.Offset(1, 0).Value = tot
End Sub


You can't change the appearance of individual elements/cells within the
listbox. All must have the same format. To the best of my knowledge,
borders are not supported.

--
Regards,
Tom Ogilvy


"A.G.M ash" wrote:

Hi all you genius programmers

I have a question as you probably guest.

I am making a workbook in excel for my income and outgoings. I am
trying to
make the data for my outgoings which is located on sheet2 and spans
across
several columns appear in sheet1 but according to a selected month or
dd/mm
or dd/mm/yy or mm/yy or yy similar to a normal filter but it will be
selected
by drop down boxes. I have tried to simplify the calling of the above
by
using seperate columns for dd mm and yy of each transaction. I havnt
made any
dropdowns or anything like that yet as I want the data to apear in
the centre
of sheet1 and to only take up x amount of rows and be scrollable in
order to
see the other info. I have tried using a list box so far instead of
the cells
to dispaly the data wich works fine only it obviously displays the
range that
i manually put in its properties. so Is there a way to use the
dropdown boxes
in order to tell the list box what filtered data to view. also can
you format
the data in a list box to have borders and cells some headings bold
and some
cells with a bg colour. what would be ace was if i could just apply
the
format to sheet2 and the list box just shows it exactly as it is
formatated
on sheet2. or is there another way of doing all this. and one other
thing i
would like to is add all the values in the filtered range to give the
total
balance and display that in the bootom line of the list box.

It doesnt have to be a list box though it can be anything that allows
me to
achieve this result.

many many many many thanks in advance

Ash Mokhberi