Quick ? - Worksheet_BeforeDoubleClick
 

Hi

I'm trying to use the above in two different sheets, but referencing the
same cell on these different sheets. The macro that is called is different
but it's coming back with a "Run-time error '13': Type Mismatch"

So at the moment, if the Target.Address is "$E$6" on both then it doesnt
work. If i instead change it to an unused cell on the second sheet,
"$E$100", then it works.

Is there anyway around this so that i can leave the target addresses the
same or do i need move it all down to cells that haven't already been used
and on sheet1 and hide everything in between?

cheers

Adam

Quick ? - Worksheet_BeforeDoubleClick
 

Try the below workbook event instead of the worksheet event...Double click
'This Workbook' from VBE left treeview and check out the below event. Sh
returns the sheet name and Target returns the target range.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
If Sh.Name = "Sheet1" Or Sh.Name = "Sheet2" Then
' do something
End If
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Adam" wrote:

Hi

I'm trying to use the above in two different sheets, but referencing the
same cell on these different sheets. The macro that is called is different
but it's coming back with a "Run-time error '13': Type Mismatch"

So at the moment, if the Target.Address is "$E$6" on both then it doesnt
work. If i instead change it to an unused cell on the second sheet,
"$E$100", then it works.

Is there anyway around this so that i can leave the target addresses the
same or do i need move it all down to cells that haven't already been used
and on sheet1 and hide everything in between?

cheers

Adam

Quick ? - Worksheet_BeforeDoubleClick
 

Hi Jacob

I've just relised that it's because I'm trying to get the double click to
work on a merged cell. If I unmerge, it works fine.

apologies for wasting your time

Adam

"Jacob Skaria" wrote:

Try the below workbook event instead of the worksheet event...Double click
'This Workbook' from VBE left treeview and check out the below event. Sh
returns the sheet name and Target returns the target range.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
If Sh.Name = "Sheet1" Or Sh.Name = "Sheet2" Then
' do something
End If
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Adam" wrote:

Hi

I'm trying to use the above in two different sheets, but referencing the
same cell on these different sheets. The macro that is called is different
but it's coming back with a "Run-time error '13': Type Mismatch"

So at the moment, if the Target.Address is "$E$6" on both then it doesnt
work. If i instead change it to an unused cell on the second sheet,
"$E$100", then it works.

Is there anyway around this so that i can leave the target addresses the
same or do i need move it all down to cells that haven't already been used
and on sheet1 and hide everything in between?

cheers

Adam