About Us

Bruce D.

Thanks a million JL!!!!
--
Bruce

"JLatham" wrote:

I believe the code below would do as I described previously. It needs to be
in the Monthly sheet's code module: open the workbook and choose that sheet
and right-click on the monthly sheet's name tab and choose [View Code]. Then
copy the code and paste it into the module and change the Const values in
each routine as needed.

Private Sub Worksheet_Activate()
'this will take place when you select
'this sheet after some other sheet has
'been selected
'this is the name of the history sheet
'change as required
Const historyName = "Sheet2"

ThisWorkbook.Worksheets(historyName).Visible = _
xlSheetHidden
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
'change these Const values as required
'this should refer to the
'column that the account numbers are in
Const accountColumn = "A"
'this is the first row that an
'account # is entered into
Const firstAcctRow = 2
'this is the name of the history sheet
Const historyName = "Sheet2"

Dim historySheet As Worksheet

If Target.Cells.Count = 1 _
And Not IsEmpty(Target) _
And Target.Row = firstAcctRow Then
Set historySheet = _
ThisWorkbook.Worksheets(historyName)
historySheet.Range(accountColumn & 1). _
AutoFilter field:=1, Criteria1:=Target.Value
historySheet.Visible = xlSheetVisible
historySheet.Activate
Set historySheet = Nothing
End If
End Sub

"JLatham" wrote:

Rather than thinking about dropdowns/combo boxes and such you might think
about this:
when a user double-clicks on an account number on the monthly sheet, then
you'd apply data filtering to the History sheet and bring it into view and
activate it. Then when they go and click on the monthly sheet again, you
simply hide the history sheet. Most of what you need to know could be found
out by recording macros while doing it, with some modification to the
recorded code.

You'd probably want to work with the Monthly sheet's Before_DoubleClick()
event and it's Activate() event.

"Bruce D." wrote:

I am using excel 2007 and I am working with a workbook that has 2 sheets. One
is called Monthly and the other is called History. Both contain the same
fields and columns which includes Account Numbers. The Monthly has a small
amount of records for the month and the History has all records for the year
2010. What I would like to do is hide the History tab. Which is no problem.
And when the user is looking at the monthly records they can also select a
account number on that sheet and a dropdown, combo box, or lookup will pop up
and they would be able to then view all the history records for that account
number. I am not sure how to approach or attempt this.

Thanks to all who respond!!
--
Bruce

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Lookups	Warren Easton	Excel Discussion (Misc queries)	2	July 9th 08 03:38 PM
Lookups	RkB2008	Excel Discussion (Misc queries)	2	June 23rd 08 07:24 PM
Lookups...	ajayb	Excel Worksheet Functions	3	June 17th 08 02:18 AM
lookups	EC	Excel Discussion (Misc queries)	5	December 12th 06 08:36 PM
LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied.	Mr Wiffy	Excel Worksheet Functions	2	May 16th 05 04:29 AM

Menu

About Us