Cell value change to trigger macro (worksheet change event?)

Hi! I have a cell named "Question_Number" which can take any integer value
from 1 to 20.

There are several ways to change the value of this cell:
1) Typing in a new number manually
2) Using the scroll bar to increase or decrease the value
3) As a result of various macros from pressing buttons within the sheet.

I'd like to be able to trigger an event if this value alters. E.g. if it was
previously 3, and it is then increased or decreased. If the macro puts a 3
back in, or the user types 3 into the cell manually, I don't want anything to
happen.

I have already written the code for the changes I want to take place
afterwards, but don't know enough about events and coding to get the event
triggered in teh first place.

Any chance somebody could show me the code I need to put in?

Does it start as follows?...


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "Question_Number" Then
Application.EnableEvents = False
If Target.Value ........
etc...
Else
etc... (do I put a "Nothing" or something like that here?)
End If
Application.EnableEvents = True
End If


Or am I completely wrong with this?


Also, how would I adapt it if I wanted the event to be triggered even if the
same value is put back in e.g. it was previously a 3, the macro gives another
3 and the event still triggers?

I'd be very grateful for some help!
Thanks, Neil

If Target.Address = "Question_Number" Then
should be

If Target.Address = Range("Question_Number").Address Then

Unless 3 is the only number to consider, you would have to store and
maintain the starting value. You would probably do that in a static
variable, but it is unclear when the "start" has occured.

for general information on events:

http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy


"Neil Goldwasser" <[email protected]> wrote in
message news:[email protected]...
> Hi! I have a cell named "Question_Number" which can take any integer value
> from 1 to 20.
>
> There are several ways to change the value of this cell:
> 1) Typing in a new number manually
> 2) Using the scroll bar to increase or decrease the value
> 3) As a result of various macros from pressing buttons within the sheet.
>
> I'd like to be able to trigger an event if this value alters. E.g. if it
was
> previously 3, and it is then increased or decreased. If the macro puts a 3
> back in, or the user types 3 into the cell manually, I don't want anything
to
> happen.
>
> I have already written the code for the changes I want to take place
> afterwards, but don't know enough about events and coding to get the event
> triggered in teh first place.
>
> Any chance somebody could show me the code I need to put in?
>
> Does it start as follows?...
>
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>
> If Target.Address = "Question_Number" Then
> Application.EnableEvents = False
> If Target.Value ........
> etc...
> Else
> etc... (do I put a "Nothing" or something like that here?)
> End If
> Application.EnableEvents = True
> End If
>
>
> Or am I completely wrong with this?
>
>
> Also, how would I adapt it if I wanted the event to be triggered even if
the
> same value is put back in e.g. it was previously a 3, the macro gives
another
> 3 and the event still triggers?
>
> I'd be very grateful for some help!
> Thanks, Neil
>

Many thanks for your help Tom! I wasn't sure about the code I suggested -
that was just an idea, but I am very much a novice, and may well have picked
a completely wrong way of trying to do it. I thought I'd see what the
professionals suggest instead!

Unfortunately you are correct in saying that 3 is not the only number I am
concerned with. I would need the macro to trigger if the value in the cell
named "Question_Number" changes from any number x to any different number y
(i.e. regardless of the initial number x, the macro needs to be triggered if
somehow the value of that cell alters to y, where y is not equal to x).

Do you know of any code that I could use to compare the value of that cell
before and after any worksheet change, and start the macro if there has been
an "actual" change of value?

I did have a look at Chip's site, and though interesting, I could not
understand all of it (I have never learned any VBA formally, just by reading
through posts and through the generous help of friends on this forum - I'm
getting there, but having had no formal education in this area I probably
have a few gaps in my knowledge that might not be there had I attended a
college course or something. Even small but very important parts of code may
unfortunately be lost on me - still, at least I'm enthusiastic enough to keep
trying!)

Many thanks in advance, Neil




"Tom Ogilvy" wrote:

> If Target.Address = "Question_Number" Then
> should be
>
> If Target.Address = Range("Question_Number").Address Then
>
> Unless 3 is the only number to consider, you would have to store and
> maintain the starting value. You would probably do that in a static
> variable, but it is unclear when the "start" has occured.
>
> for general information on events:
>
> http://www.cpearson.com/excel/events.htm
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Neil Goldwasser" <[email protected]> wrote in
> message news:[email protected]...
> > Hi! I have a cell named "Question_Number" which can take any integer value
> > from 1 to 20.
> >
> > There are several ways to change the value of this cell:
> > 1) Typing in a new number manually
> > 2) Using the scroll bar to increase or decrease the value
> > 3) As a result of various macros from pressing buttons within the sheet.
> >
> > I'd like to be able to trigger an event if this value alters. E.g. if it
> was
> > previously 3, and it is then increased or decreased. If the macro puts a 3
> > back in, or the user types 3 into the cell manually, I don't want anything
> to
> > happen.
> >
> > I have already written the code for the changes I want to take place
> > afterwards, but don't know enough about events and coding to get the event
> > triggered in teh first place.
> >
> > Any chance somebody could show me the code I need to put in?
> >
> > Does it start as follows?...
> >
> >
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> >
> > If Target.Address = "Question_Number" Then
> > Application.EnableEvents = False
> > If Target.Value ........
> > etc...
> > Else
> > etc... (do I put a "Nothing" or something like that here?)
> > End If
> > Application.EnableEvents = True
> > End If
> >
> >
> > Or am I completely wrong with this?
> >
> >
> > Also, how would I adapt it if I wanted the event to be triggered even if
> the
> > same value is put back in e.g. it was previously a 3, the macro gives
> another
> > 3 and the event still triggers?
> >
> > I'd be very grateful for some help!
> > Thanks, Neil
> >
>
>
>

Hi Neil,

Given that the question number may be changed in a variety of ways, the
Worksheet_Change event is not appropriate to your needs.

Perhaps you could consider using a helper cell, monitor the value of the
helper cell with the Worksheet_Calculate event and compare this to the value
of a defined name.

For example:

(1) In a selected helper cell (say) D1, enter the formula:
=A1
where A1 represents the question number cell.

The helper cell could be hidden or behind (say) the spinner.

(2) In a standard module, paste the following code

'=============>>
Sub RunOnce()
Dim NME As Name

On Error Resume Next
Set NME = ThisWorkbook.Names("Question_Number")
If Err.Number <> 0 Then
ThisWorkbook.Names.Add Name:="Question_Number", _
RefersTo:=" "
End If
End Sub
'<<=============

The above code only needs to be run once to initialise the defined name.
Obviously, you could equally define the name manually.

'=============>>
Sub QuestionNumber()
Dim rng As Range
Dim NME As Name

Set rng = Range("D1")

Set NME = ThisWorkbook.Names("Question_Number")

If rng.Value <> Evaluate(NME.RefersTo) Then
NME.RefersTo = rng.Value
MsgBox "Neil's code runs here"
End If

End Sub
'<<=============

Replace the message box with your pre-written processing code.

In the worksheet's code module paste the following code:

'=============>>
Public Sub Worksheet_Calculate()
Call QuestionNumber
End Sub
'<<=============


---
Regards,
Norman



"Neil Goldwasser" <[email protected]> wrote in
message news:[email protected]...
> Many thanks for your help Tom! I wasn't sure about the code I suggested -
> that was just an idea, but I am very much a novice, and may well have
> picked
> a completely wrong way of trying to do it. I thought I'd see what the
> professionals suggest instead!
>
> Unfortunately you are correct in saying that 3 is not the only number I am
> concerned with. I would need the macro to trigger if the value in the cell
> named "Question_Number" changes from any number x to any different number
> y
> (i.e. regardless of the initial number x, the macro needs to be triggered
> if
> somehow the value of that cell alters to y, where y is not equal to x).
>
> Do you know of any code that I could use to compare the value of that cell
> before and after any worksheet change, and start the macro if there has
> been
> an "actual" change of value?
>
> I did have a look at Chip's site, and though interesting, I could not
> understand all of it (I have never learned any VBA formally, just by
> reading
> through posts and through the generous help of friends on this forum - I'm
> getting there, but having had no formal education in this area I probably
> have a few gaps in my knowledge that might not be there had I attended a
> college course or something. Even small but very important parts of code
> may
> unfortunately be lost on me - still, at least I'm enthusiastic enough to
> keep
> trying!)
>
> Many thanks in advance, Neil
>
>
>
>
> "Tom Ogilvy" wrote:
>
>> If Target.Address = "Question_Number" Then
>> should be
>>
>> If Target.Address = Range("Question_Number").Address Then
>>
>> Unless 3 is the only number to consider, you would have to store and
>> maintain the starting value. You would probably do that in a static
>> variable, but it is unclear when the "start" has occured.
>>
>> for general information on events:
>>
>> http://www.cpearson.com/excel/events.htm
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "Neil Goldwasser" <[email protected]> wrote in
>> message news:[email protected]...
>> > Hi! I have a cell named "Question_Number" which can take any integer
>> > value
>> > from 1 to 20.
>> >
>> > There are several ways to change the value of this cell:
>> > 1) Typing in a new number manually
>> > 2) Using the scroll bar to increase or decrease the value
>> > 3) As a result of various macros from pressing buttons within the
>> > sheet.
>> >
>> > I'd like to be able to trigger an event if this value alters. E.g. if
>> > it
>> was
>> > previously 3, and it is then increased or decreased. If the macro puts
>> > a 3
>> > back in, or the user types 3 into the cell manually, I don't want
>> > anything
>> to
>> > happen.
>> >
>> > I have already written the code for the changes I want to take place
>> > afterwards, but don't know enough about events and coding to get the
>> > event
>> > triggered in teh first place.
>> >
>> > Any chance somebody could show me the code I need to put in?
>> >
>> > Does it start as follows?...
>> >
>> >
>> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>> >
>> > If Target.Address = "Question_Number" Then
>> > Application.EnableEvents = False
>> > If Target.Value ........
>> > etc...
>> > Else
>> > etc... (do I put a "Nothing" or something like that here?)
>> > End If
>> > Application.EnableEvents = True
>> > End If
>> >
>> >
>> > Or am I completely wrong with this?
>> >
>> >
>> > Also, how would I adapt it if I wanted the event to be triggered even
>> > if
>> the
>> > same value is put back in e.g. it was previously a 3, the macro gives
>> another
>> > 3 and the event still triggers?
>> >
>> > I'd be very grateful for some help!
>> > Thanks, Neil
>> >
>>
>>
>>

This works an absolute treat - fantastic!

I decided to hide the helper cell, and also call the RunOnce macro in the
Workbook_Open event, and it has solved a big headache for me!

Thanks ever so much Norman, it is very much appreciated!
Cheers, Neil



"Norman Jones" wrote:

> Hi Neil,
>
> Given that the question number may be changed in a variety of ways, the
> Worksheet_Change event is not appropriate to your needs.
>
> Perhaps you could consider using a helper cell, monitor the value of the
> helper cell with the Worksheet_Calculate event and compare this to the value
> of a defined name.
>
> For example:
>
> (1) In a selected helper cell (say) D1, enter the formula:
> =A1
> where A1 represents the question number cell.
>
> The helper cell could be hidden or behind (say) the spinner.
>
> (2) In a standard module, paste the following code
>
> '=============>>
> Sub RunOnce()
> Dim NME As Name
>
> On Error Resume Next
> Set NME = ThisWorkbook.Names("Question_Number")
> If Err.Number <> 0 Then
> ThisWorkbook.Names.Add Name:="Question_Number", _
> RefersTo:=" "
> End If
> End Sub
> '<<=============
>
> The above code only needs to be run once to initialise the defined name.
> Obviously, you could equally define the name manually.
>
> '=============>>
> Sub QuestionNumber()
> Dim rng As Range
> Dim NME As Name
>
> Set rng = Range("D1")
>
> Set NME = ThisWorkbook.Names("Question_Number")
>
> If rng.Value <> Evaluate(NME.RefersTo) Then
> NME.RefersTo = rng.Value
> MsgBox "Neil's code runs here"
> End If
>
> End Sub
> '<<=============
>
> Replace the message box with your pre-written processing code.
>
> In the worksheet's code module paste the following code:
>
> '=============>>
> Public Sub Worksheet_Calculate()
> Call QuestionNumber
> End Sub
> '<<=============
>
>
> ---
> Regards,
> Norman
>
>
>
> "Neil Goldwasser" <[email protected]> wrote in
> message news:[email protected]...
> > Many thanks for your help Tom! I wasn't sure about the code I suggested -
> > that was just an idea, but I am very much a novice, and may well have
> > picked
> > a completely wrong way of trying to do it. I thought I'd see what the
> > professionals suggest instead!
> >
> > Unfortunately you are correct in saying that 3 is not the only number I am
> > concerned with. I would need the macro to trigger if the value in the cell
> > named "Question_Number" changes from any number x to any different number
> > y
> > (i.e. regardless of the initial number x, the macro needs to be triggered
> > if
> > somehow the value of that cell alters to y, where y is not equal to x).
> >
> > Do you know of any code that I could use to compare the value of that cell
> > before and after any worksheet change, and start the macro if there has
> > been
> > an "actual" change of value?
> >
> > I did have a look at Chip's site, and though interesting, I could not
> > understand all of it (I have never learned any VBA formally, just by
> > reading
> > through posts and through the generous help of friends on this forum - I'm
> > getting there, but having had no formal education in this area I probably
> > have a few gaps in my knowledge that might not be there had I attended a
> > college course or something. Even small but very important parts of code
> > may
> > unfortunately be lost on me - still, at least I'm enthusiastic enough to
> > keep
> > trying!)
> >
> > Many thanks in advance, Neil
> >
> >
> >
> >
> > "Tom Ogilvy" wrote:
> >
> >> If Target.Address = "Question_Number" Then
> >> should be
> >>
> >> If Target.Address = Range("Question_Number").Address Then
> >>
> >> Unless 3 is the only number to consider, you would have to store and
> >> maintain the starting value. You would probably do that in a static
> >> variable, but it is unclear when the "start" has occured.
> >>
> >> for general information on events:
> >>
> >> http://www.cpearson.com/excel/events.htm
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >>
> >> "Neil Goldwasser" <[email protected]> wrote in
> >> message news:[email protected]...
> >> > Hi! I have a cell named "Question_Number" which can take any integer
> >> > value
> >> > from 1 to 20.
> >> >
> >> > There are several ways to change the value of this cell:
> >> > 1) Typing in a new number manually
> >> > 2) Using the scroll bar to increase or decrease the value
> >> > 3) As a result of various macros from pressing buttons within the
> >> > sheet.
> >> >
> >> > I'd like to be able to trigger an event if this value alters. E.g. if
> >> > it
> >> was
> >> > previously 3, and it is then increased or decreased. If the macro puts
> >> > a 3
> >> > back in, or the user types 3 into the cell manually, I don't want
> >> > anything
> >> to
> >> > happen.
> >> >
> >> > I have already written the code for the changes I want to take place
> >> > afterwards, but don't know enough about events and coding to get the
> >> > event
> >> > triggered in teh first place.
> >> >
> >> > Any chance somebody could show me the code I need to put in?
> >> >
> >> > Does it start as follows?...
> >> >
> >> >
> >> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> >> >
> >> > If Target.Address = "Question_Number" Then
> >> > Application.EnableEvents = False
> >> > If Target.Value ........
> >> > etc...
> >> > Else
> >> > etc... (do I put a "Nothing" or something like that here?)
> >> > End If
> >> > Application.EnableEvents = True
> >> > End If
> >> >
> >> >
> >> > Or am I completely wrong with this?
> >> >
> >> >
> >> > Also, how would I adapt it if I wanted the event to be triggered even
> >> > if
> >> the
> >> > same value is put back in e.g. it was previously a 3, the macro gives
> >> another
> >> > 3 and the event still triggers?
> >> >
> >> > I'd be very grateful for some help!
> >> > Thanks, Neil
> >> >
> >>
> >>
> >>
>
>
>