Custom Class and Properties

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:

Please Login or Register  to view this content.

In a standard module I have a public object:

Please Login or Register  to view this content.

And during the Workbook_Open event I call the following procedure:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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,

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()).Activate
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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 procedure:
>
>
> 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()).Activate
> 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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Is called from this event.

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

Regards

Rich

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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 in the workbook and the two sheets Header and Customer Details do not contain these properties. One other thing was that I was mistakenly using the application rather than the worksheet.... :-(

However!

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

Please Login or Register  to view this content.

Please Login or Register  to view this content.

And access the various bits and bobs like this;

Please Login or Register  to view this content.

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

Rich