Worksheet Custom Properties

[Rich_z]

Hi Guys,

I am having a few problems with some custom properties in an excel work
book.

You can set up custom properties for a work sheet like so:


Code:
--------------------
Public Property Get Insulation_Type() As String
Insulation_Type = Range(Insulation_Type_Address()).Value
End Property
Public Property Let Insulation_Type(New_Value As String)
Range(Insulation_Type_Address()).Value = New_Value
End Property
--------------------


This then enables you to do things like:


Code:
--------------------
Worksheets("Gutter1").Insulation_Type = "XXXX"
--------------------


and


Code:
--------------------
Ins_Type = Worksheets("Gutter1").Insulation_Type
--------------------


This works fine, however, when you then do something like this:


Code:
--------------------
Function Data_Appears_Valid(Check_Sheet As Worksheet) As Boolean
Dim Error_List(1, 200) As String
Dim Error_Count As Integer
Dim Error_Display As Integer
Dim Out_Pos_Row As Integer
Dim Out_Pos_Cell As String
Error_Count = 0
'*
'* Insulation type
'*
If Check_Sheet.Insulation_Type = "" Then
Error_List(0, Error_Count) = "'" & Check_Sheet.Name & "'!" & Check_Sheet.Insulation_Type_Address
Error_List(1, Error_Count) = Check_Sheet.Name & ": Insulation Type cannot be blank"
Error_Count = Error_Count + 1
End If
--------------------


The code complains that Check_Sheet does not have the property
Insulation_Type.

How can I pass a worksheet as a parameter and get the code to compile
?

Secondly, I originally had the properties set up like so:


Code:
--------------------
Type Property_String
Address As String
Value As String
End Type

Public Property Get Insulation_Type() As Property_String
Insulation_Type.Value = Range(Insulation_Type_Address()).Value
Insulation_Type.Address = Insulation_Type_Address()
End Property
--------------------


And accessed these as follows:

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

Ins_Type = Worksheets("Gutter1").Insulation_Type.Value

Call Reset_Cell(Worksheets("Gutter1").Insulation_Type.Address)

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


This worked fine for one pass through the code, second and subsequent
passes caused the error: "the object invoked has disconnected from its
clients"

I spent many hours Monday and Tuesday searching for an answer to this
on the web. Apparently there are many causes, none of which fitted my
scenario. The actual cause is the fact that I have a user defined type
as the parameters. Return this to an intrinsic type and the error goes
away.

So my next question is: How can I implement this type of functionality
? Ie have a property such as Insulation_Type.Address and
Insulation_Type.Value ?

Regards and thanks.

Rich

 

[K Dales]

Where is your code for Insulation_Type? It must be in a class module for it
to work. The class module name is the name of the new object you created to
be a worksheet with your custom properties but your Function call specifies
"Function Data_Appears_Valid(Check_Sheet As Worksheet)..." If Check_Sheet is
a Worksheet it does not have your custom properties. Make if Function
Data_Appears_Valid(Check_Sheet As ClassName) (where ClassName is the name of
the Class Module)

 

[Rich_z]

Hi K(ev???)

Thanks for the quick reply. You beat me to the post by about 15
minutes. I have changed the parameter declaration to 'AS OBJECT', and
it compiles (and more importantly runs...) without error.

Any thoughts on the using a UDT in a property ?

 

[DM Unseen]

Rick

this should be

Function Data_Appears_Valid(Check_Sheet As Sheet1) As Boolean

where sheet1 is the *codename* for the sheet with the property get/set

This is correct because it actually prevents you from passing an
illegal sheet(i.e a sheet without the property) to this function

DM Unseen

 

[Rich_z]

Hi DM,

Unfortunately this will be called from several worksheets which
obviously will have different names and slightly different layouts,
but all with the same set of properties.

It's actually working now with the declaration 'AS OBJECT' and as there
will not be any dynamic calls to the function, i can be certain that
the sheets using the function will always have the correct properties.

Thanks for your input!

regards

Rich

 

[DM Unseen]

OK,

I did not know this.

As an alternative

Function Data_Appears_Valid(Check_Sheet As Worksheet) As Boolean
Dim mypropsheet as object
....
....
set mypropsheet = Check_Sheet

if mypropsheet .Insulation_Type = "" Then
.......


should also work

DM Unseen

 

[K Dales]

Can't really comment since I haven't had any experience trying that in a
class module and haven't encountered that problem