ExcelBanter - Lookups

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Lookups (https://www.excelbanter.com/excel-discussion-misc-queries/261230-lookups.html)

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

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

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

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