help!? trail report

Hi Tom, / (anybody who could help?....)

I have a file which is used to track documents by their specific ID,
with a bit of coding I have designed it so it can all be done via
userforms (add, remove,find etc). I know I should be using access but
im restricted to excel!

My next problem is working out how to track the progress of a document,
it is stored as a row of information saying where its gone and what
date, but the same document could then be sent to another place and so
on thus creating subsequent rows the most recent being at the bottom. I
want to create a trail report on a userform which will show all the
entries for the unique ID in the order that they appear in a sort of
list but just showing certain cells from the row like date sent, where
to, and where from. so that with input of the ID and a click of a
button the form will display a trail report for me.

Is this too blue sky for a userform? I dont really know where to start
with this bit!

Hoping someone can help me

Duncan

Turn on the data recorder

select your data and do

Data=>filter=>Autofilter

in the dropdown in the ID column, select an ID with multiple rows.

Now turn off the recorder

This should give you the data you want and you can see how to program the
autofilter.

You can then use code like
Dim rng as Range, rng1 as Range
dim rng2 as Range
set rng = Activesheet.autofilter.Range.columns(1)
' now exlude the header row
set rng1 = rng.offset(1,0).Resize(rng.rows.count-1)
On error resume next
set rng2 = rng1.specialcells(xlvisible)
On error goto 0
if rng2 is nothing then
' no rows meet the criteria
else
With userform1.Listbox1
.columncount = 3
for each cell in rng2
.AddItem cell.Value
.list(.listcount-1,1) = cell.offset(0,2)
.list(.Listcount-1,2) = cell.offset(0,5)
Next
end With
End if

the alternative is to loop through your list of ID's and pick up the cells
that match your ID. then use code similar to that inside the loop to
populate your listbox.

--
Regards,
Tom Ogilvy





"Duncan" wrote:

> Hi Tom, / (anybody who could help?....)
>
> I have a file which is used to track documents by their specific ID,
> with a bit of coding I have designed it so it can all be done via
> userforms (add, remove,find etc). I know I should be using access but
> im restricted to excel!
>
> My next problem is working out how to track the progress of a document,
> it is stored as a row of information saying where its gone and what
> date, but the same document could then be sent to another place and so
> on thus creating subsequent rows the most recent being at the bottom. I
> want to create a trail report on a userform which will show all the
> entries for the unique ID in the order that they appear in a sort of
> list but just showing certain cells from the row like date sent, where
> to, and where from. so that with input of the ID and a click of a
> button the form will display a trail report for me.
>
> Is this too blue sky for a userform? I dont really know where to start
> with this bit!
>
> Hoping someone can help me
>
> Duncan
>
>

Tom,

Many thanks for your swift reply! I may take a while playing with this
and trying it out as im fairly fresh to vb, which is why i have replied
now to say thank you as it may be a while before I know if I can get it
to work or not!

Many thanks

Duncan

Give it a go and post back with specific questions if you have problems.

Also look at Debra Dalgleish's site for some topics related to this area:
http://www.contextures.com/tiptech.html

--
Regards,
Tom Ogilvy


"Duncan" wrote:

> Tom,
>
> Many thanks for your swift reply! I may take a while playing with this
> and trying it out as im fairly fresh to vb, which is why i have replied
> now to say thank you as it may be a while before I know if I can get it
> to work or not!
>
> Many thanks
>
> Duncan
>
>

Tom,

As it happens it didnt take me that long, I simply used the
selection.autofilter with the criteria as the textbox name and it shows
the results in the background. after a message box ok to say done it
removes the autofilter. (ill post the code i used)


Private Sub Report_Click()

If regTrail <= "" Then
MsgBox "must input a registration number!"
Exit Sub
End If

Range("A1").End(xlDown).Offset(1, 0).Select
activecell.Select


Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=regTrail.Value
Select Case MsgBox("finished?", vbOKOnly)
Case vbOK
Selection.AutoFilter
regTrail.Value = ""
regTrail.SetFocus
Exit Sub
End Select

End Sub

I know this is taking the easy way out but it appears to do what i
want, I just have to test it on other pc's with a smaller resolution to
ensure that the report can be viewed in the background, might have to
hide the form while its showing the results list as the message box
will have focus and therefore the form wont be able to be dragged out
of the way. I also might try adding a print option to print the results
out.

Many thanks again

Duncan

Ok,

I have reposted my code as it is now that I am close to being finished,
I have a small problem though. I am using a range selection to set what
i want to print as I only want to print the list or report when it
comes up. I am having troubles getting it to select all of the lines
though! For some reason my range which should get it all just gets the
top two lines every time.

(code is below)

Private Sub Report_Click()

If regTrail <= "" Then
MsgBox "must input a registration number!"
Exit Sub
End If

If Not IsNumeric(regTrail.Value) Then
MsgBox "Registration number must be a Numerical Value, Please retry"
Exit Sub
End If

Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.Select

Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=regTrail.Value
UserForm1.Hide

Select Case MsgBox("Print?", vbYesNo)

Case vbYes
Sheet1.Activate

Dim rng As Range
Set rng = Range(Range("A1"), Range("I1").End(xlDown).Offset(1, 0))
rng.Select
Selection.PrintOut Copies:=1, Collate:=True

Selection.AutoFilter
UserForm1.Show
regTrail.Value = ""
regTrail.SetFocus
Exit Sub

Case vbNo
Selection.AutoFilter
UserForm1.Show
regTrail.Value = ""
regTrail.SetFocus

Exit Sub
End Select


End Sub


I cant see why it isnt selecting everything, is there a better way of
doing it?


Duncan