Can you have variations to Worksheet_Change event?

R

rob nobel

Sorry to repost....www problems and can't find a reply!
Rob

Is it possible to have separate or variations to the Worksheet_Change
events for
different procedures on the same sheet as I'm trying to do quite a number of
things on the same sheet and it all becomes rather confusing doing it all
under the one Worksheet_Change event. ( I am also using a
Worksheet_SelectionChange event on this sheet.)
 
F

Frank Kabel

Hi
no you can't. You have to put all your code in one worksheet_change
event. You may consider using a Select Case statement (if you haven't
implemented it this way)
 
A

Arvi Laanemets

Hi

No!
There is only one Change event for worksheet, which is trigered whenever
anything is changed on this sheet.
I'm not sure, is it possible to write your own events in Excel VBA -
whenever I feel I'll need something above workbooks Open event, I'll know
it's time to look for some more serious tool (usually Access does it).
 
O

onedaywhen

Well, you can, sort of. But do you really want to? A Select Case may
be better. But if you must, here's some code:

'<In class module named Class1>--------
Option Explicit

Private WithEvents m_oWorksheet As Excel.Worksheet
Private m_oRange As Excel.Range

Public Event SelectionChange()

Private Sub m_oWorksheet_SelectionChange(ByVal Target As Range)
If Not Excel.Application.Intersect(m_oRange, Target) Is Nothing Then
RaiseEvent SelectionChange
End If
End Sub

Public Function Init( _
ByVal ExcelWorksheet As Excel.Worksheet, _
ByVal ExcelRange As Excel.Range _
) As Boolean

Set m_oWorksheet = ExcelWorksheet
Set m_oRange = ExcelRange

End Function
'</In class module named Class1>--------

'<In a sheet code module>--------
Option Explicit

Private WithEvents oC1 As Class1
Private WithEvents oC2 As Class1
Private WithEvents oC3 As Class1

Public Function Init() As Boolean
Set oC1 = New Class1
oC1.Init Me, Me.Range("A1")
Set oC2 = New Class1
oC2.Init Me, Me.Range("A2")
Set oC3 = New Class1
oC3.Init Me, Me.Range("A3")
End Function

Private Sub oC1_SelectionChange()
MsgBox "Event handler 1"
End Sub

Private Sub oC2_SelectionChange()
MsgBox "Event handler 2"
End Sub

Private Sub oC3_SelectionChange()
MsgBox "Event handler 3"
End Sub

Public Function Destroy() As Boolean
Set oC1 = Nothing
Set oC2 = Nothing
Set oC3 = Nothing
End Function
'</In a sheet code module>--------

'<In ThisWorkbook code module>--------
Option Explicit

Private Sub Workbook_Open()
Sheet1.Init
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheet1.Destroy
End Sub
'</In ThisWorkbook code module>--------

--
 
A

Arvi Laanemets

Hi


onedaywhen said:
Well, you can, sort of. But do you really want to? A Select Case may
be better. But if you must, here's some code:
Click to expand...

No, I don't - for me a need for Change event is reason enough to switch to
Access. But I'm not so sure about OP's :)
 
R

rob nobel

Wow! and I'm not even gonna ask how all this works. Before I try it
though, the line Sheet1. Destroy, could that be fatal, particularly if I
already have a sheet 1??
Rob
 
R

rob nobel

Thanks Frank.
I do like the Select Case option and will certainly give this a go.
Rob
 
O

onedaywhen

Rob, You are correct, Sheet1.Destroy qualifies for the worst method
name of the week! It doesn't actually destroy Sheet1, although the
name suggests it does. My mistake.

I should have named it Sheet1.CleanUp because that's what it does i.e.
cleans up my custom objects by destroying them.

I should also have said, try this code in a new workbook. Run the
Workbook_Open sub, then when you individually select one of cells
A1,A2 or A3 on Sheet1 a messagebox will show. It's a demo of how you
*could* have more than one Worksheet_Change event handler but again I
wonder if in practise there is any real benefit.

--
 
O

onedaywhen

Arvi, I was addressing the OP rather than you. I attached to your
thread to counter your 'no' with my 'yes'.

BTW don't switch to Access in haste. Excel's userforms would be the
next thing to consider.

--
 
R

rob nobel

Thanks again. I think you're right, there probably isn't a huge benefit.
The trouble with having a lot of procedures that use the worksheet _change
event, separating them isn't going to help much as they are all interlinked
anyway.
But I will try it out and see what benefit it might have.
Rob
 
Top