Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Custom Properties
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Spreadsheet Properties | Excel Worksheet Functions | |||
Custom File Properties | Excel Discussion (Misc queries) | |||
i am having problem displaying the custom properties | Excel Discussion (Misc queries) | |||
Custom File Properties | Excel Discussion (Misc queries) | |||
Custom Properties | Excel Programming |