Microsoft Excel VBA Worksheet Activate and Change Events

This video provides a demonstration on how to use macros to trigger the worksheet activate and change events.

---

Closed Caption:

hello everyone today in Microsoft Excel
we're going to be discussing a few
macros specifically worksheet activate
and worksheet change in order to set
this up first got to make sure your
macros are unable so please make sure
those are all set up and will explain a
few scenarios as to why you may want to
do this with your workbook or your
worksheet so let's go let's get right
into it i'm gonna go on it's like the
visual basic environment and first
you're going to select the sheet that
you want to work with in this case is
due sheet1 and by default when you go
and there's not going to be any code but
what you'll do is go to your worksheet
on the left and on the right let's do
the one that says change going to show
you what change does is to do a
messagebox target . address so this is
going to do any time you make a change
in this worksheet let's say we type in
anything here in this but the number-one
hit enter
Microsoft Excel is going to notify you
of what cell was just changed so let's
just say that you didn't want anybody to
make a change to d5 now notice dollar
dollar five this is what we can do if
target address equals dollar D dollar
five then and then we gotta make sure we
close our if statement all we're going
to do is reset that sell to our own
value with these sheets sheet1 that
range and will do d 5 is equal to do not
modify this cell
no print the seasons closed required for
that
alright so let's just go on and try to
modify this same cell again testing we
actually debug.error now if you notice
we gotta debug.error and the reason
behind that is because we're actually
making a change to this cell what you're
going to want to do is gonna hit stopped
temporarily disable the events in Excel
and this will ignore these kind of
events that occur applications that
enable events equals false change it up
change the value of the sheet of the
cell then application that enables
events equals true
this should correct your error testing
one and there you have it
Microsoft Excel prevented you from
getting into an infinite loop since
every time of that that sells changed
that code to be executed but this will
save that process from happening
temporarily enabling the events and then
re-enabling them the other macro we're
going to discuss is just the activate
activate will simply you can put in your
own code as to what you want to do when
the worksheet is activated if you want
to do anything at all to give an example
message box you have just selected
sheet1 nothing will happen since we're
already in sheet1 but let's just say
that what you're using to navigate to a
different sheets sheet2 III is going to
sheet1 we're going to message by saying
you have just selected sheet1 because in
the visual basic environment we had
sheet1 selected and we're looking at
worksheet the activate feature
well this is it for this video I hope
you enjoyed watching thank you