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

Rich,

I had to change

Public Gutter_Sheet As New clsGutter

to Private to make it work. Where does this 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

figure in it, the class has a sheet activate event.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rich_z" wrote in
message ...

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

Hi Bob,

Thanks fort he quick reply.

The class has an event:


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

Private Sub Gutter_SheetActivate(ByVal sh As Object)
Call Gutter_Activate(sh)
End Sub

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


Which is as you point out the SheetActivate event. The sub

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

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


Is called from this event.

However, if I change the Public statement to a Private one the
variable can't be found!!

Regards

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

The Private thing was because I stored the variable in Thisworkbook, not a
standard module (didn't follow the instructions :-)).

I cannot replicate the error, but you seem to be passing the sheet object to
that module, and then trying to access one of the class properties against
that object (get me?). Perhaps you could pass the value in the call

Private Sub Gutter_SheetActivate(ByVal sh As Object)
Call Gutter_Activate(c_Sbw_Angle_R, sh)
End Sub

and

Sub Gutter_Activate(rng As String, ByVal This_Sheet As Worksheet)
If This_Sheet.Name < "Header" And This_Sheet.Name < "Customer Details"
Then
Range(rng)Activate
End If
Call Check_Data_Entry(ActiveCell)
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rich_z" wrote in
message ...

Hi Bob,

Thanks fort he quick reply.

The class has an event:


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

Private Sub Gutter_SheetActivate(ByVal sh As Object)
Call Gutter_Activate(sh)
End Sub

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


Which is as you point out the SheetActivate event. The sub

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

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


Is called from this event.

However, if I change the Public statement to a Private one the
variable can't be found!!

Regards

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

The class intercepts the various events for each and every worksheet i
the workbook and the two sheets Header and Customer Details do no
contain these properties. One other thing was that I was mistakenl
using the application rather than the worksheet.... :-(

However!

I may not have got this quite right, but now what I do is this:


Code
-------------------

Call Trap_Worksheet_Events(Worksheets(Sheet_Name))
Worksheets(Sheet_Name).Activate
On Local Error GoTo 0
Exit Sub

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



Code
-------------------

Sub Trap_Worksheet_Events(sh As Object)
Set Gutter_Sheet.shGutter = sh
End Sub

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



And access the various bits and bobs like this;


Code
-------------------

Sub Calculate_Bottom_F(ByVal Thickness As String, ByVal Top_B As Single)
Call Unlock_Sheet("")
Gutter_Sheet.Bottom_F = Top_B + Val(Thickness)
Call Lock_Sheet
If Gutter_Sheet.Bottom_F <= 0 Then
Call Error("Negative Result - Invalid Component Values")
End If
If Gutter_Sheet.Bottom_F 5000 Then
Call Error("Range Result - Check component values")
End If
End Sub

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


This smacks of global variables (which I hate)... but it Works!!!!
Yeeee hah!

Thanks for all your help Bob!

If anyone else has a better way of doing this then please let me know!

Regards

Ric

--
Rich_
-----------------------------------------------------------------------
Rich_z's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread.php?threadid=38637