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" wrote:


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.A ddress)

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


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


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