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" wrote in
message ...
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" wrote in
message ...
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" wrote in
message ...
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" wrote in
message ...
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" wrote in
message ...
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" wrote in
message ...
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