add eventhandler to sheet in code

[King Albert II]

Hi,


I'd like to attach the eventhandler below, to any sheet that I add in
code like so : Set wsUpload = ThisWorkbook.Worksheets.Add




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


Call bereikenmaken
If target.Column = Range("womschrijving").Column Then
Load Ingave
Call tonen(target)
Ingave.Show
End If


End Sub





How can I do that ?

thx

Ward

 

[Peter T]

Not sure what you are asking, and what the relevance of the DoubleClick
event is. Maybe you simply want something like this

Private Sub Workbook_NewSheet(ByVal Sh As Object)
If TypeName(Sh) = "Worksheet" Then
Set gwsUpload = Sh
MsgBox Sh.Name & " assigned to gwsUpLoad"
' code ?
End If
End Sub

I am assuming gwsUpload is a global worksheet variable declared in a normal
module

If(?) you have a Form Ingave showing it might be better to update a Property
in the Form and call some routine in the Form.

Regards,
Peter T

 

[King Albert II]

Peter,

When the user doubleclicks the evented worksheet, the handler checks if a
particular column is doubleclicked, and if yes proceeds to load a form in
memory and populates that with row info.
The evented sheet has too many columns to fit on a 21" screen. In the row
of the clicked cell, there are some cells that need to be visualized
closely together, so I use a form with some controls.



What I wanted to do was create a sheet from scratch using
.....Worksheets.Add, and then attach an event to it. Similar to adding
clickable DOM objects to a html page.

After posting here I discovered a page by Chip Pearson explaining how to,
but it is too involved for this project. Since I only need to add one
worksheet in this particular subroutine, I'll go easy and unhide an
existing sheet, with a hardcoded eventhandler. For the enduser it's the
same magic.



thx for your help


Ward




See "Creating An Event Procedure" here :

http://www.cpearson.com/excel/vbe.aspx ,

 

[Peter T]

What I wanted to do was create a sheet from scratch using

....Worksheets.Add, and then attach an event to it. Similar to adding
clickable DOM objects to a html page.

You can use ThisWorkbook level events but you'll need to check the current
sheet is your UpLoad sheet

' code in a normal module
Public gwsUpload As Worksheet

Sub test()

Set gwsUpload = ActiveWorkbook.Worksheets.Add

End Sub

Function IsUploadSheet() As Boolean

On Error GoTo errExit
If Not gwsUpload Is Nothing Then
IsUploadSheet = (ActiveSheet Is gwsUpload)
End If

errExit:

End Function

' ''''' code in Thisworkbook module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If IsUploadSheet = True Then
MsgBox "UploadSheet sheet activated"
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
If IsUploadSheet = True Then
MsgBox Target.Address & " on UploadSheet sheet"
End If

End Sub

Regards,
Peter T

 

[Dave Peterson]

There is a user security setting that will stop your code from running if they
don't allow this type of access.

I think it's a much better idea to use that hidden sheet within the workbook --
or even add a worksheet (that already has the code) from a template workbook.

But there may be another option.

Can you tie into the workbook event: Workbook_SheetBeforeDoubleClick

You might need to check something to know when to continue (a key header in a
key cell or a hidden sheet level name or ???).

If the code is pretty much the same for all your sheets that need this, it may
be easier to maintain.

 

[John_John]

Hi Albert!

You can use the same eventhandler (SheetBeforeDoubleClick) of Workbook object.



Ο χÏήστης "King Albert II" έγγÏαψε:

 

[King Albert II]

Yes, that would prolly work.
I didn't know you could add sheethandlers to the Thisworkbook object.
If one needed different eventhandlers for each type of sheet one added,
one could use the technique you demonstrated here, to route behaviour.

But for this particular project I'll stick to Ockham's razor

thx again

Ward

 

[King Albert II]

Thread-Topic: add eventhandler to sheet in code
thread-index: AcsDEdZ2QIf5RnwjQ6299EO7Jxhqvw==
X-WBNR-Posting-Host: 85.75.175.18
From: =?Utf-8?B?Sm9obl9Kb2hu?= <[email protected]>
References: <[email protected]>
Subject: RE: add eventhandler to sheet in code
Date: Thu, 3 Jun 2010 04:42:25 -0700
Lines: 42
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 8bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4325
Newsgroups: microsoft.public.excel.programming
NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
Path:
s01-b043!cyclone03.ams2.highwinds-media.com!news.highwinds-media.com!no
vso.com!ecngs!feeder2.ecngs.de!194.25.134.126.MISMATCH!newsfeed01.sul.t
-online.de!newsfeed00.sul.t-online.de!t-online.de!TK2MSFTFEEDS02.phx.gb
l!TK2MSFTNGP01.phx.gbl!TK2MSFTNGHUB02.phx.gbl Xref: Hurricane-Charley
microsoft.public.excel.programming:544985 X-Received-Date: Thu, 03 Jun
2010 11:47:06 UTC (s01-b043)

Hi Albert!

You can use the same eventhandler (SheetBeforeDoubleClick) of Workbook
object.



Ο χÏήστης "King Albert II" έγγÏαψε:

yes, I realize that now !

thx

Ward

 

[King Albert II]

There is a user security setting that will stop your code from running
if they don't allow this type of access.

I think it's a much better idea to use that hidden sheet within the
workbook -- or even add a worksheet (that already has the code) from a
template workbook.

But there may be another option.

Can you tie into the workbook event: Workbook_SheetBeforeDoubleClick

You might need to check something to know when to continue (a key
header in a key cell or a hidden sheet level name or ???).

If the code is pretty much the same for all your sheets that need
this, it may be easier to maintain.

didn't know about the securitysetting, would've hit it for sure. It's all
GPO in this shop.

ThisWorkbook has sheethandlers : now I know

easier maintenance: I see it - and would be the way to go


thx


Ward