Earlier I asked what the best way to clone a spreadsheet was and was
told to use a Class module.

I've eventually come up with the following which appears to work quite
well. If this is the wrong approach could you please let me know:

Create a class module clsGutter:


Code:
--------------------

Option Explicit

Public WithEvents Gutter As Application
'*
Const c_Sbw_Angle_R = "$C$18"
...
...
Private Sub Gutter_SheetActivate(ByVal sh As Object)
Call Gutter_Activate(sh)
End Sub
Private Sub Gutter_SheetChange(ByVal sh As Object, ByVal Target As Range)
Call Gutter_Change(sh, Target)
End Sub

Private Sub Gutter_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)
Call Gutter_SelectionChange(sh, Target)
End Sub
...
...
...
Public Property Get Angle_R() As Double
Angle_R = Range(Angle_R_Address()).Value
End Property
Public Property Let Angle_R(New_Value As Double)
Range(Angle_R_Address()).Value = New_Value
End Property
Public Property Get Insulation_Type_Address() As String
Insulation_Type_Address = c_sbw_Insulation_Type
End Property

--------------------


In a standard module I have a public object:


Code:
--------------------

Public Gutter_Sheet As New clsGutter

--------------------


And during the Workbook_Open event I call the following procedu


Code:
--------------------

Sub Trap_Application_Events()
Set Gutter_Sheet.Gutter = Application
End Sub

--------------------


This all works fine with the application events being trapped by the
new class, so as each new worksheet is added to the work book they
behave in an identical way with all the events in place and being
called.

However, even though I have declared the properties of the class as
public, the macros complain that they do not exist:


Code:
--------------------

Sub Gutter_Activate(ByVal This_Sheet As Object)
If This_Sheet.Name < "Header" And This_Sheet.Name < "Customer Details" Then
Range(This_Sheet.Insulation_Type_Address()).Activa te
End If
Call Check_Data_Entry(ActiveCell)
End Sub

--------------------


This code causes a run time error 438 - "object doesn't support this
property or method" even though the Gutter class is active and the
property get has been declared as public.

Where am I going wrong ??

Cheers,

Rich


--
Rich_z
------------------------------------------------------------------------
Rich_z's Profile: http://www.excelforum.com/member.php...o&userid=24737
View this thread: http://www.excelforum.com/showthread...hreadid=386379