Class module to access range events

[Mark]

I was wondering if anyone out there could help me to find a way to
create a class that essentially would be composed of 3 cells. I dont
know if this is possible as I have been at it for awhile. I know how
to create classes, and have some familiarity with the "With Events"
keyword, but am at a loss for how to get my class to respond to a
change that takes place within the specific cells within the class.
The reason I am looking into this is that I want to create a Bowling
Frame:
one cell will be the first ball
another cell will be the second ball (it's data validation will be
set to allow for only the pins remaining)
the last cell will contain the score.

I want to be able to update the score and the second ball cell
whenever an entry has been made.

I hope this is clear enough. Any help would be really
appreciated as I am tired of running into the same wall. If you need
any more information in order to help, please post and I will give it
to you ASAP.

Thanks,
Mark

 

[Jim Cone]

Mark,
Each Excel sheet has its own class module.
Right click the sheet tab and choose View Code.
Included in that module are several events including one that
occurs with every change in a cell.
There are two dropdowns at the top of the module.
Select "worksheet" in the left dropdown and "change" in the right dropdown.
(not selection change)
Have a go at it. "Target" is a range object referencing the cell or cells changed.
--
Jim Cone
Portland, Oregon USA



"Mark" <[email protected]>
wrote in message
I was wondering if anyone out there could help me to find a way to
create a class that essentially would be composed of 3 cells. I dont
know if this is possible as I have been at it for awhile. I know how
to create classes, and have some familiarity with the "With Events"
keyword, but am at a loss for how to get my class to respond to a
change that takes place within the specific cells within the class.
The reason I am looking into this is that I want to create a Bowling
Frame:
one cell will be the first ball
another cell will be the second ball (it's data validation will be
set to allow for only the pins remaining)
the last cell will contain the score.

I want to be able to update the score and the second ball cell
whenever an entry has been made.

I hope this is clear enough. Any help would be really
appreciated as I am tired of running into the same wall. If you need
any more information in order to help, please post and I will give it
to you ASAP.

Thanks,
Mark

 

[Mark]

Mark,
Each Excel sheet has its own class module.
Right click the sheet tab and choose View Code.
Included in that module are several events including one that
occurs with every change in a cell.
There are two dropdowns at the top of the module.
Select "worksheet" in the left dropdown and "change" in the right dropdown.
(not selection change)
Have a go at it.  "Target" is a range object referencing the cell or cells changed.
--

I am familiar with the "Worksheet Change Event" and the passed Target
Object. That is EXACTLY the event that I want to be able to tie my
class into, but I don't know how. Are you telling me to do something
like this in my Class...?

Public WithEvents WkSht as Worksheet

I think you are, and that does seem to give me the options I'm looking
for, I just thought that perhaps I could access the "Range" Class more
directly.

Thank for your time, I REALLY appreciate it.
Mark