How to fire an event of another sheet

[arno]

Hello,

when I doubleclick in sheet2 I would like to fire the worksheet_change event
of sheet1. How do I do this? So, the before_doublecleck event of sheet2
should fire the change event of sheet1.

If this is not possible to do with the double click event, what normal sub
can I use to fire the change event on sheet1?

regards

arno

 

[Don Guillett]

If your double click on 2 changes a1 on sht 1 then the change event will
fire on sht 1
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Sheets("sheet1").Range("a1") = Target
End Sub

 

[Jim Rech]

You can call the sheet1 change event code directly from the sheet 2 before
double click event if:

-you remove "Private" from the Sheet1 code, and
-you pass it a range:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True
Sheet1.Worksheet_Change Range("A1")
End Sub

--
Jim
| Hello,
|
| when I doubleclick in sheet2 I would like to fire the worksheet_change
event
| of sheet1. How do I do this? So, the before_doublecleck event of sheet2
| should fire the change event of sheet1.
|
| If this is not possible to do with the double click event, what normal sub
| can I use to fire the change event on sheet1?
|
| regards
|
| arno
|
|
|

 

[Dave Peterson]

I think this line:
Sheet1.Worksheet_Change Range("A1")
should probably be:
Sheet1.Worksheet_Change Sheet1.Range("A1")

Otherwise, that Range("A1") will be the A1 on the sheet that gets
doubleclicked--not A1 of the sheet that's changing.

 

[Jim Rech]

You're right about the cell that's passed and I would have done it the way
your suggest if I'd thought about it. OTOH I passed a range because I had
too, not because I thought he wanted to use cell A1 or any particular cell.

In fact, now that I'm conjuring up my unconscious reasoning<g>, passing a
cell from another sheet may be a way to distinguish a true sheet event on
Sheet1 from this artificial one.

--
Jim
|I think this line:
| Sheet1.Worksheet_Change Range("A1")
| should probably be:
| Sheet1.Worksheet_Change Sheet1.Range("A1")
|
| Otherwise, that Range("A1") will be the A1 on the sheet that gets
| doubleclicked--not A1 of the sheet that's changing.
|
|
| Jim Rech wrote:
| >
| > You can call the sheet1 change event code directly from the sheet 2
before
| > double click event if:
| >
| > -you remove "Private" from the Sheet1 code, and
| > -you pass it a range:
| >
| > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
| > Boolean)
| > Cancel = True
| > Sheet1.Worksheet_Change Range("A1")
| > End Sub
| >
| > --
| > Jim
| > | > | Hello,
| > |
| > | when I doubleclick in sheet2 I would like to fire the worksheet_change
| > event
| > | of sheet1. How do I do this? So, the before_doublecleck event of
sheet2
| > | should fire the change event of sheet1.
| > |
| > | If this is not possible to do with the double click event, what normal
sub
| > | can I use to fire the change event on sheet1?
| > |
| > | regards
| > |
| > | arno
| > |
| > |
| > |
|
| --
|
| Dave Peterson

 

[arno]

Thank you all for your help!

This works in "normal" Excel:

In sheet1 I have the following code:
Sub Worksheet_Change(ByVal Target As Range)
MsgBox "CHANGE on Sheet 1 fired!"
End Sub
Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox "DOUBLECLICK on Sheet 1 fired!"
End Sub


In Sheet2 I have the following code (tabelle1 = sheet1):
Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Sheets("tabelle1").Range("a6") = Target
End Sub
Sub Worksheet_Change(ByVal Target As Range)
Sheets("tabelle1").Range("a6") = Target
End Sub

Whatever I do, on sheet1 the change event is fired. This event also works
and fires the change event:
Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Tabelle1.Worksheet_Change Tabelle1.Range("A6")
End Sub


The whole story is that I have an Add-in that fires events on doubleclicks
but I do not know which one as it is protected/hidden. So, the second
BeforeDoubleClick event cannot be used as there is no worksheet_change event
in sheet1. However, the first Worksheet_beforeDoubleClick event does
_something_ (but not the right thing or not everything that should happen).
The events deletes cell A6 in sheet1 but it should fill it again. Maybe
another event on that sheet/workbook should be fired.

It is very likely that the Addin is listening to the
Application_SheetChange.

Would it be possible to show how I can fire an Application_sheetchange event
(and a Workbook_sheetchange event) on sheet1.range("a6") when doubleclicking
sheet2.range("a6")? I have troubles to pass the right arguments for
workbook_change and I know nothing about how to fire an
application_sheetchange event.

regards

arno

 

[Jim Rech]

Arno-

Let me say first off I have no idea what you are doing and why. And I don't
think I should ask.<g> It seems to be 'highly idiosyncratic'.

|In Sheet2 I have the following code (tabelle1 = sheet1):
| Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
| Cancel = True
| Sheets("tabelle1").Range("a6") = Target
| End Sub

This says: When I double click a cell on sheet2 I want the value in that
cell to appear in cell A6 on sheet1. If the double clicked cell is blank I
want A6 to be blank.

Is that truly what you want? I have never run into such a "feature".<g>

I'm sorry I cannot be more helpful but, as I said, I'm lost.

--
Jim
| Thank you all for your help!
|
| This works in "normal" Excel:
|
| In sheet1 I have the following code:
| Sub Worksheet_Change(ByVal Target As Range)
| MsgBox "CHANGE on Sheet 1 fired!"
| End Sub
| Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
| MsgBox "DOUBLECLICK on Sheet 1 fired!"
| End Sub
|
|
| In Sheet2 I have the following code (tabelle1 = sheet1):
| Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
| Cancel = True
| Sheets("tabelle1").Range("a6") = Target
| End Sub
| Sub Worksheet_Change(ByVal Target As Range)
| Sheets("tabelle1").Range("a6") = Target
| End Sub
|
| Whatever I do, on sheet1 the change event is fired. This event also works
| and fires the change event:
| Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
| Cancel = True
| Tabelle1.Worksheet_Change Tabelle1.Range("A6")
| End Sub
|
|
| The whole story is that I have an Add-in that fires events on doubleclicks
| but I do not know which one as it is protected/hidden. So, the second
| BeforeDoubleClick event cannot be used as there is no worksheet_change
event
| in sheet1. However, the first Worksheet_beforeDoubleClick event does
| _something_ (but not the right thing or not everything that should
happen).
| The events deletes cell A6 in sheet1 but it should fill it again. Maybe
| another event on that sheet/workbook should be fired.
|
| It is very likely that the Addin is listening to the
| Application_SheetChange.
|
| Would it be possible to show how I can fire an Application_sheetchange
event
| (and a Workbook_sheetchange event) on sheet1.range("a6") when
doubleclicking
| sheet2.range("a6")? I have troubles to pass the right arguments for
| workbook_change and I know nothing about how to fire an
| application_sheetchange event.
|
| regards
|
| arno
|
|
|

 

[arno]

Hi Jim,

sorry for confusion you. It is very simple.

I have a software that fires events when I doubleclick on the report in
sheet1. I make simple formulas to sheet1 in sheet2. The user works in sheet2
and doubleclicks eg. on A6. Excel should now fire an event on sheet1!a6 and
not sheet2!a6. I would like to pass Sheet1!a6 to an application.sheetchange
event which requires an "object" and a "target" (I guess). How can I pass
"sheet1" and "a6" to that event? It should be basically the same as
workbook_sheetchange.

The only thing I can make work is this event

| Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
| Cancel = True
| Sheets("tabelle1").Range("a6") = Target
| End Sub

Here, I pass the whole reference as Target. I click on Sheet2!a6 but it acts
as I would have clicked on sheet1. But Worksheet/application_sheetchange
expect "object" and "target" as arguments. For the beginning I only need to
know how I can pass these two arguments to any sheetchange event.

arno

 

[Dave Peterson]

So you want Sheet1's worksheet_Change event to use the same address as the cell
that got double clicked on in sheet2?

Maybe:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Cancel = True
Sheet1.Worksheet_Change sheet1.Range(target.address)
End Sub

 

[arno]

Hi Dave,

So you want Sheet1's worksheet_Change event to use the same address as the
cell
that got double clicked on in sheet2?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Cancel = True
Sheet1.Worksheet_Change sheet1.Range(target.address)
End Sub

this works. But I'd like to know how this works for application or workbook
events - how can I pass the required arguments? I wrote all errors I get as
comments.

1)Workbook event
How can this be done using the Workbook_SheetChange event? I have this code
for the workbook:

Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Sh.Target.Interior.ColorIndex = 5 'does not work:Error 438, Object does
not support this method or attribute
End Sub

In Sheet2 I have:
Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Workbook_SheetChange Sheets(1), Sheet1.Range(Target.Address)
'does not work: sub or function not defined
End Sub

2) Application event
The I tried the class module with Application_sheetchange:
Class Module:
Dim WithEvents app As Application
Sub app_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox "hi"
End Sub

normal Module:
Dim WithEvents app As Application 'error: user defined type not defined
Private Sub app_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox "hi"
End Sub

Code for sheet2:
Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
app_SheetChange Sheets(1), sheet1.Range(Target.Address)
'error: sub or function not defined
End Sub

How can I pass the object and the target to either application events or
workbook events?

regards

arno

 

[Dave Peterson]

I used the workbook_sheetchange event.

Under the ThisWorkbook module:

Option Explicit
Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox Sh.Name & vbLf & Target.Address(external:=True)
End Sub

Notice how this procedure expects an object (a worksheet most times) and a range
(usually on that same worksheet. That means you have to pass it both those
parms when you call it.

Behind a worksheet:
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Cancel = True
ThisWorkbook.Workbook_SheetChange Sheet1, Sheet1.Range(Target.Address)
End Sub

As for this line:
Sh.Target.Interior.ColorIndex = 5

Target is already a range. That means it already knows what its parent is. You
don't specify it again.

Try:
Target.Interior.ColorIndex = 5