Excel VBA - Add Sheet Change Event at Run Time

[cidol]

My project adds a worksheet to the active workbook and then adds a shee
change event procedure at run time. After the procedure is ran, th
vbe window automatically opens to the new sheet change event procedure
Is there a way to prevent this from happening? I have tried turnin
off screen updating and adding the line VBE.MainWindow.Close to the en
of the code, but I get the error "Programmatic Access to Visual Basi
Project is not Trusted." I have signed my project and selected "Trus
access to Visual Basic Project" in the macro security settings and
still get the error. I am using Excel 2002

 

[Jim Rech]

I don't get an error when I do this:

Application.VBE.MainWindow.Visible = False

I do get an ugly flicker though even with screen updating off.

 

[Guest]

Hi,
If each _Change event for each newly added sheet runs similar code, you could use a Class module instead. That is, you don't need to access the vbide programatically, and you don't need to duplicate similar code several times.

1. Create a class named clsSheetEvent:
'----------------------------------------------------------------
Public WithEvents Worksheet As Worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Parent.Name & " -- " & Target.Address
'... your event code here
'
End Sub

Private Sub Class_Terminate()
Set Worksheet = Nothing
End Sub
'----------------------------------------------------------------

2. Add a clsSheetEvent global variable. In a module:
Public wshEvent As clsSheetEvent

3. In the sub that adds a sheet:
Public Sub AddSheet()
Dim wsh As Worksheet

Set wsh = ActiveWorkbook.Worksheets.Add
Set wshEvent = New clsSheetEvent 'Create the clsSheetEvent
Set wshEvent.Worksheet = wsh 'Sets its Worksheet member to newly added one
End Sub

Now, after running AddSheet, change a cell in the newly created sheet, it will executed the _Change from the class.
Here, i declared only one clsSheetEvent variable (wshEvent), but you could, for example, store them in a collection instead to have as many as you want.
The interesting thing is that you don't need to add the code in each sheet.

I hope this helps,

Regards,
Sebastien

 

[Tom Ogilvy]

Another approach would be to instantiate and use Application level events:

See Chip Pearson's site on this topic (or use Excel VBA Help)
http://www.cpearson.com/excel/appevent.htm

--
Regards,
Tom Ogilvy

Hi,
If each _Change event for each newly added sheet runs similar code, you

could use a Class module instead. That is, you don't need to access the
vbide programatically, and you don't need to duplicate similar code several
times.

1. Create a class named clsSheetEvent:
'----------------------------------------------------------------
Public WithEvents Worksheet As Worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Parent.Name & " -- " & Target.Address
'... your event code here
'
End Sub

Private Sub Class_Terminate()
Set Worksheet = Nothing
End Sub
'----------------------------------------------------------------

2. Add a clsSheetEvent global variable. In a module:
Public wshEvent As clsSheetEvent

3. In the sub that adds a sheet:
Public Sub AddSheet()
Dim wsh As Worksheet

Set wsh = ActiveWorkbook.Worksheets.Add
Set wshEvent = New clsSheetEvent 'Create the clsSheetEvent
Set wshEvent.Worksheet = wsh 'Sets its Worksheet member to newly added one
End Sub

Now, after running AddSheet, change a cell in the newly created sheet, it

will executed the _Change from the class.

Here, i declared only one clsSheetEvent variable (wshEvent), but you

could, for example, store them in a collection instead to have as many as
you want.

 

[cidol]

Hi, thanks for all the quick responses.

My project basically writes SQL code and then attaches a query objec
to a new worksheet. I have added an option that allows the user to ad
"drill down" functionality to the results.

To do this, I need to know when the query results have been returned s
that the code can add hyperlinks to the approprate fields (which shoul
only happen once) and handle the hyperlink click event (which update
the SQL code from the query object based on the value of the cell tha
is clicked and then adds another query object to another new sheet).

So, I didn't want to use a class module or an application level even
because the event procedures are pretty specific to certain kinds o
sheets.

I thought of using QueryTable AfterRefresh event, but the user can hav
multiple queries running at the same time and I couldn't find an eas
way for the event to know if the user had selected the drill dow
object for a specific query.

I know how to use ADO to return query results in the code and then cop
the record set to a worksheet which would remove the need to know whe
the query results are returned, but some of the queries can take up t
an hour to run and it's not feasible for my users to not have access t
excel for the period the query(ies) are running. Also, using a quer
object makes it easy for the user to cancel the query by deleting th
cell with the query object before the results are returned. An
suggestions?

Again thanks for all your help. I've been trying to teach mysefl V
for the last few months and these forums have been an invaluabl
resource

 

[cidol]

Hi, thanks for all the quick responses.

My project basically writes SQL code and then attaches a query objec
to a new worksheet. I have added an option that allows the user to ad
"drill down" functionality to the results.

To do this, I need to know when the query results have been returned s
that the code can add hyperlinks to the approprate fields (which shoul
only happen once) and handle the hyperlink click event (which update
the SQL code from the query object based on the value of the cell tha
is clicked and then adds another query object to another new sheet).

So, I didn't want to use a class module or an application level even
because the event procedures are pretty specific to certain kinds o
sheets.

I thought of using QueryTable AfterRefresh event, but the user can hav
multiple queries running at the same time and I couldn't find an eas
way for the event to know if the user had selected the drill dow
object for a specific query.

I know how to use ADO to return query results in the code and then cop
the record set to a worksheet which would remove the need to know whe
the query results are returned, but some of the queries can take up t
an hour to run and it's not feasible for my users to not have access t
excel for the period the query(ies) are running. Also, using a quer
object makes it easy for the user to cancel the query by deleting th
cell with the query object before the results are returned. An
suggestions?

Again thanks for all your help. I've been trying to teach mysefl V
for the last few months and these forums have been an invaluabl
resource

 

[Jamie Collins]

I know how to use ADO to return query results
in the code and then copy the record set to a
worksheet which would remove the need to
know when the query results are returned, but
some of the queries can take up to an hour to
run and it's not feasible for my users to not have
access to excel for the period the query(ies) are running.

Have you considered:

- using ADO asynchronously? Requires a class module to sink the
connection/recordset events and get a 'callback' when the query has
finished executing;

- executing the queries on the DB server, creating a temp table and
the Excel clients only query the temp table?

- reviewing your queries for efficiency (an hour to run?! <g>) e.g.
are you using a cursor/loop rather than pure SQL?

Jamie.

--