Clicking on Cell on one sheet and seeing the details on another sheet

[howard]

I have set up management accounts on sheet "Man_ACCTS". I have an account called general expenses in Col A and the total value in Col B.

I have the breakdown of all the general expenses on sheet TB and have named these "Gen_Expenses"

I would like the user to be able to double click on General expenses on sheet "Man_ACCNTS" and to be able to see what makes up this account on sheet TB

Your assistance in guiding me in the right direction is most appreciated.

 

[Momentman]

Maybe something along these lines, just an idea

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("Man_ACCNTS")) Is Nothing Then
        Range("Gen_Expenses").Select
    End If
End Sub

 

[howard]

Thanks for the help. I get a run time error 1004 "Method range Object_Worksheet failed which I double click on General Expenses on sheet "Man_ACCNTS"

The general expenses on sheet TB has been named and refers to Col A =TB!$C$95:$C$101

It would be appreciated if you would assist in rectifying this error

 

[Momentman]

Does this work?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("Man_ACCNTS")) Is Nothing Then
        TB.Activate
        TB.Range("Gen_Expenses").Select
    End If
End Sub

 

[howard]

Thanks for the reply.

For some unknown reason this is not working. It still get a run time error

I have changed my sheet name to Income_Statement

On sheet "Income_Statement" I have general expenses in Col A

Excel 2012
	A	B
23	General Expenses	1565
Income_Statement

On sheet "TB" I have named General Expenses GEN_EXPENSES"

Excel 2012
	C
96	General Expenses - Renovations
97	General Expenses - Replacement
98	General Expenses - Flat
99	General Expenses - Groceries/Photo's etc
100	General Expenses - Hygiene/Pest Control
101	General Expense - Relocation Exp
TB

 

[Momentman]

So cell A23 is the range(Cell) named "Man_ACCTS"? I noticed i called the range ACCNTS rather than ACCTS.

On the Income_Statement sheet, right click>View code and paste the modified code in there

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("Man_ACCTS")) Is Nothing Then
        TB.Activate
        TB.Range("Gen_Expenses").Select
    End If
End Sub

 

[howard]

Thanks for the reply and your amended code

I have right clicked on the On the Income_Statement sheet and pasted the modified code in there

After double clicking on cell A23, I now get runtime error 424 Object required and the following code is highlighted

 TB.Activate

 

[Momentman]

That last code assumes that the sheet that has the range("gen_Expenses") is a sheet called TB. Isn't that the case? If it isn't, change the name of the sheet to the appropriate name

I have run this exact code and it works fine for me

Victor

 

[howard]

Thanks for the reply. The ranged named "Gen_Expense" is on sheet TB

For some unknown reason when I click on sheet1 general Expenses" it comes runtime error 424 Object required and TB.activate is highlighted

 

[Momentman]

Thanks for the reply. The ranged named "Gen_Expense" is on sheet TB

For some unknown reason when I click on sheet1 general Expenses" it comes runtime error 424 Object required and TB.activate is highlighted

Click or double click?

The cell you click on i named "Man_ACCTS"? Is that right?

This should work

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


    If Not Intersect(Target, Range("Man_ACCTS")) Is Nothing Then
        Sheets("TB").Activate
        Sheets("TB").Range("Gen_Expenses").Select
    End If
End Sub

Maybe i had been assuming too much