Excel VBA Class Objects - Parent & Successor Objects

[walker.oliver]

Good Afternoon,

I would greatly appreciate any assistance relative to the following
Class object related objective:

1. I would like to define a Class of Objects based on the presence of
various Data Validation Lists in certain cells, and be able to fire
code based upon the worksheet calculate event whenever a member of the
class is changed by the user.

I think I understand the basics re Class objects, in that they enable
you to define a custom set of procedures, properties and methods for an
object or a collection of objects. However, I am struggling witht the
syntax.

If one of you kind folks could provide some code that achieved the
following AIM, it would greatly assist me with understanding classes.

1. I have a worksheet with numerous data validation list cells. I want
the user to be able to select from the following text strings, and in
response to the user's choice, have the formatting for the cell
directly to the right of the data validation list cell update according
to user selection.

Data Validation List Component ("G27"): Amount Input Cell ("H27"):
"$ Per Land SqFt:" If G27="$ Per
Land SqFt:", number format H27 = $#.00
"$ Amount:" If G27="$
Amount:" number format H27 = $#,###0
"% Land Cost:" If G27="% Land
Cost:" number format H27 = #.##0%


Rather than loop through all the cell testing for the presence of these
strings, and offsetting(0,1) to apply the format, it seems like a Class
of Worksheet Cells would be more efficient and enable me to do more.

Please advise! Thanks! - Walker

 

[Tom Ogilvy]

I don't see any reason to add complexity when selection in the list triggers
the Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 then exit sub
If Target.Column = 7 then
Select Case Target.Value
Case "Something1"
Target.Offset(0,1).NumberFormat = "#,##0.00"
Case "Something2"
Target.Offset(0,1).NumberFormat = "$ #,##0.00"
end Select
End if
End Sub