Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_BeforeDoubleClick() | Excel Programming | |||
Worksheet_BeforeDoubleClick Stops Working | Excel Programming | |||
Worksheet_beforedoubleclick | Excel Programming | |||
Sub Worksheet_BeforeDoubleClick from elsewhere? | Excel Discussion (Misc queries) | |||
Quick question - quick answer about assigning shortcut keys | Excel Programming |