I have a survey form where users can rate corporate performance. It is made
up of radio buttons to rank efficiency. i designed it to prevent closing and
printing if the answers are too skewed (we need 3 5's, 2 4'2 , etc) It works
fine the first time you open the file. However, if you change your answers
(say, rated it 4 and then want to change the rating to 3), the code stops
working.

Why is this? How can I solve this issue?

Thanks so much,
Michelle K

Can you post your code? Hard to know without seeing it.

"Michelle K" wrote:

I have a survey form where users can rate corporate performance. It is made
up of radio buttons to rank efficiency. i designed it to prevent closing and
printing if the answers are too skewed (we need 3 5's, 2 4'2 , etc) It works
fine the first time you open the file. However, if you change your answers
(say, rated it 4 and then want to change the rating to 3), the code stops
working.

Why is this? How can I solve this issue?

Thanks so much,
Michelle K

Here is the code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)


If (Range("C27") < 3 And Range("D27") < 2 And Range("E27") < 1 And
Range("F27") < 2 And Range("G27") < 3 _
And Range("I27") < 3 And Range("J27") < 2 And Range("K27") < 1 And
Range("L27") < 2 And Range("M27") < 3 _
And Range("C42") < 2 And Range("D42") < 2 And Range("E42") < 1 And
Range("F42") < 2 And Range("G42") < 2 _
And Range("I42") < 2 And Range("J42") < 2 And Range("K42") < 1 And
Range("L42") < 2 And Range("M42") < 2 _
And Range("C57") < 2 And Range("D57") < 2 And Range("E57") < 1 And
Range("F57") < 2 And Range("G57") < 2 _
And Range("I57") < 2 And Range("J57") < 2 And Range("K57") < 1 And
Range("L57") < 2 And Range("M57") < 2) Then
MsgBox "You must complete the survey before closing." _
& " Please note the number of selections required per action.",
vbExclamation, "CANNOT CLOSE"
Cancel = True
End If

End Sub

it's a simple code that just prevents the user from closing Excel without
answering the survey accordingly. It runs once and after that it doesn't
anymore.

"K Dales" wrote:

Can you post your code? Hard to know without seeing it.

"Michelle K" wrote:

I have a survey form where users can rate corporate performance. It is made
up of radio buttons to rank efficiency. i designed it to prevent closing and
printing if the answers are too skewed (we need 3 5's, 2 4'2 , etc) It works
fine the first time you open the file. However, if you change your answers
(say, rated it 4 and then want to change the rating to 3), the code stops
working.

Why is this? How can I solve this issue?

Thanks so much,
Michelle K

Do you have other event related code where you disable events

Application.EnableEvents = False

It sounds like events are getting disabled and never reenabled.

--
Regards,
Tom Ogilvy

"Michelle K" wrote in message
...
Here is the code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)


If (Range("C27") < 3 And Range("D27") < 2 And Range("E27") < 1 And
Range("F27") < 2 And Range("G27") < 3 _
And Range("I27") < 3 And Range("J27") < 2 And Range("K27") < 1 And
Range("L27") < 2 And Range("M27") < 3 _
And Range("C42") < 2 And Range("D42") < 2 And Range("E42") < 1 And
Range("F42") < 2 And Range("G42") < 2 _
And Range("I42") < 2 And Range("J42") < 2 And Range("K42") < 1 And
Range("L42") < 2 And Range("M42") < 2 _
And Range("C57") < 2 And Range("D57") < 2 And Range("E57") < 1 And
Range("F57") < 2 And Range("G57") < 2 _
And Range("I57") < 2 And Range("J57") < 2 And Range("K57") < 1 And
Range("L57") < 2 And Range("M57") < 2) Then
MsgBox "You must complete the survey before closing." _
& " Please note the number of selections required per action.",
vbExclamation, "CANNOT CLOSE"
Cancel = True
End If

End Sub

it's a simple code that just prevents the user from closing Excel without
answering the survey accordingly. It runs once and after that it doesn't
anymore.

"K Dales" wrote:

Can you post your code? Hard to know without seeing it.

"Michelle K" wrote:

I have a survey form where users can rate corporate performance. It
is made
up of radio buttons to rank efficiency. i designed it to prevent
closing and
printing if the answers are too skewed (we need 3 5's, 2 4'2 , etc)
It works
fine the first time you open the file. However, if you change your
answers
(say, rated it 4 and then want to change the rating to 3), the code
stops
working.

Why is this? How can I solve this issue?

Thanks so much,
Michelle K

I tried adding these lines but they don't seem to be working:

Public Sub ResetEvents()

Application.EnableEvents = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = True

End Sub

I am sure I am missing something or have written these under the wrong
events. Help!

"Tom Ogilvy" wrote:

Do you have other event related code where you disable events

Application.EnableEvents = False

It sounds like events are getting disabled and never reenabled.

--
Regards,
Tom Ogilvy

"Michelle K" wrote in message
...
Here is the code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)


If (Range("C27") < 3 And Range("D27") < 2 And Range("E27") < 1 And
Range("F27") < 2 And Range("G27") < 3 _
And Range("I27") < 3 And Range("J27") < 2 And Range("K27") < 1 And
Range("L27") < 2 And Range("M27") < 3 _
And Range("C42") < 2 And Range("D42") < 2 And Range("E42") < 1 And
Range("F42") < 2 And Range("G42") < 2 _
And Range("I42") < 2 And Range("J42") < 2 And Range("K42") < 1 And
Range("L42") < 2 And Range("M42") < 2 _
And Range("C57") < 2 And Range("D57") < 2 And Range("E57") < 1 And
Range("F57") < 2 And Range("G57") < 2 _
And Range("I57") < 2 And Range("J57") < 2 And Range("K57") < 1 And
Range("L57") < 2 And Range("M57") < 2) Then
MsgBox "You must complete the survey before closing." _
& " Please note the number of selections required per action.",
vbExclamation, "CANNOT CLOSE"
Cancel = True
End If

End Sub

it's a simple code that just prevents the user from closing Excel without
answering the survey accordingly. It runs once and after that it doesn't
anymore.

"K Dales" wrote:

Can you post your code? Hard to know without seeing it.

"Michelle K" wrote:

I have a survey form where users can rate corporate performance. It
is made
up of radio buttons to rank efficiency. i designed it to prevent
closing and
printing if the answers are too skewed (we need 3 5's, 2 4'2 , etc)
It works
fine the first time you open the file. However, if you change your
answers
(say, rated it 4 and then want to change the rating to 3), the code
stops
working.

Why is this? How can I solve this issue?

Thanks so much,
Michelle K

If events are disabled, your second procedure will never get triggered.

Are you sure it doesn't run. Perhaps the condition doesn't warrant a
message box?

Try putting in a msgbox at the top (temporarily

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "In workbook Close"

If (Range("C27") < 3 And Range("D27") < 2 And Range("E27") < 1 And
Range("F27") < 2 And Range("G27") < 3 _
And Range("I27") < 3 And Range("J27") < 2 And Range("K27") < 1 And
Range("L27") < 2 And Range("M27") < 3 _
And Range("C42") < 2 And Range("D42") < 2 And Range("E42") < 1 And
Range("F42") < 2 And Range("G42") < 2 _
And Range("I42") < 2 And Range("J42") < 2 And Range("K42") < 1 And
Range("L42") < 2 And Range("M42") < 2 _
And Range("C57") < 2 And Range("D57") < 2 And Range("E57") < 1 And
Range("F57") < 2 And Range("G57") < 2 _
And Range("I57") < 2 And Range("J57") < 2 And Range("K57") < 1 And
Range("L57") < 2 And Range("M57") < 2) Then
MsgBox "You must complete the survey before closing." _
& " Please note the number of selections required per action.",
vbExclamation, "CANNOT CLOSE"
Cancel = True
End If

End Sub

You will need to save the workbook to retain the code modification.
--
Regards,
Tom Ogilvy


"Michelle K" wrote in message
...
I tried adding these lines but they don't seem to be working:

Public Sub ResetEvents()

Application.EnableEvents = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = True

End Sub

I am sure I am missing something or have written these under the wrong
events. Help!

"Tom Ogilvy" wrote:

Do you have other event related code where you disable events

Application.EnableEvents = False

It sounds like events are getting disabled and never reenabled.

--
Regards,
Tom Ogilvy

"Michelle K" wrote in message
...
Here is the code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)


If (Range("C27") < 3 And Range("D27") < 2 And Range("E27") < 1 And
Range("F27") < 2 And Range("G27") < 3 _
And Range("I27") < 3 And Range("J27") < 2 And Range("K27") < 1 And
Range("L27") < 2 And Range("M27") < 3 _
And Range("C42") < 2 And Range("D42") < 2 And Range("E42") < 1 And
Range("F42") < 2 And Range("G42") < 2 _
And Range("I42") < 2 And Range("J42") < 2 And Range("K42") < 1 And
Range("L42") < 2 And Range("M42") < 2 _
And Range("C57") < 2 And Range("D57") < 2 And Range("E57") < 1 And
Range("F57") < 2 And Range("G57") < 2 _
And Range("I57") < 2 And Range("J57") < 2 And Range("K57") < 1 And
Range("L57") < 2 And Range("M57") < 2) Then
MsgBox "You must complete the survey before closing." _
& " Please note the number of selections required per action.",
vbExclamation, "CANNOT CLOSE"
Cancel = True
End If

End Sub

it's a simple code that just prevents the user from closing Excel
without
answering the survey accordingly. It runs once and after that it
doesn't
anymore.

"K Dales" wrote:

Can you post your code? Hard to know without seeing it.

"Michelle K" wrote:

I have a survey form where users can rate corporate performance.
It
is made
up of radio buttons to rank efficiency. i designed it to prevent
closing and
printing if the answers are too skewed (we need 3 5's, 2 4'2 ,
etc)
It works
fine the first time you open the file. However, if you change
your
answers
(say, rated it 4 and then want to change the rating to 3), the
code
stops
working.

Why is this? How can I solve this issue?

Thanks so much,
Michelle K

I do not have Excel on this machine, so I can't test this theory, but
is it because "Cancel=True". When the code runs a second time, is
Cancel still set to true?

No that is an event argument to stop the close event happening.

--
HTH

Bob Phillips

"p.hall" wrote in message
oups.com...
I do not have Excel on this machine, so I can't test this theory, but
is it because "Cancel=True". When the code runs a second time, is
Cancel still set to true?

You have put it in Thisworkbook code module?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Michelle K" wrote in message
...
Here is the code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)


If (Range("C27") < 3 And Range("D27") < 2 And Range("E27") < 1 And
Range("F27") < 2 And Range("G27") < 3 _
And Range("I27") < 3 And Range("J27") < 2 And Range("K27") < 1 And
Range("L27") < 2 And Range("M27") < 3 _
And Range("C42") < 2 And Range("D42") < 2 And Range("E42") < 1 And
Range("F42") < 2 And Range("G42") < 2 _
And Range("I42") < 2 And Range("J42") < 2 And Range("K42") < 1 And
Range("L42") < 2 And Range("M42") < 2 _
And Range("C57") < 2 And Range("D57") < 2 And Range("E57") < 1 And
Range("F57") < 2 And Range("G57") < 2 _
And Range("I57") < 2 And Range("J57") < 2 And Range("K57") < 1 And
Range("L57") < 2 And Range("M57") < 2) Then
MsgBox "You must complete the survey before closing." _
& " Please note the number of selections required per action.",
vbExclamation, "CANNOT CLOSE"
Cancel = True
End If

End Sub

it's a simple code that just prevents the user from closing Excel without
answering the survey accordingly. It runs once and after that it doesn't
anymore.

"K Dales" wrote:

Can you post your code? Hard to know without seeing it.

"Michelle K" wrote:

I have a survey form where users can rate corporate performance. It
is made
up of radio buttons to rank efficiency. i designed it to prevent
closing and
printing if the answers are too skewed (we need 3 5's, 2 4'2 , etc)
It works
fine the first time you open the file. However, if you change your
answers
(say, rated it 4 and then want to change the rating to 3), the code
stops
working.

Why is this? How can I solve this issue?

Thanks so much,
Michelle K

Show the code and indicate where it goes wrong might be a start.

--
HTH

Bob Phillips

"Michelle K" wrote in message
...
I have a survey form where users can rate corporate performance. It is
made
up of radio buttons to rank efficiency. i designed it to prevent closing
and
printing if the answers are too skewed (we need 3 5's, 2 4'2 , etc) It
works
fine the first time you open the file. However, if you change your
answers
(say, rated it 4 and then want to change the rating to 3), the code stops
working.

Why is this? How can I solve this issue?

Thanks so much,
Michelle K

I'm using Excel 2003 on an XP box. If I write a vba event handler procedure
that has any error in it, for example mismatch, out of range, whatever, ... I
stop the run, make appropriate changes but, I have to completely shutdown
excel, start it back up in order to get the procedure to run again. If I do
not do this the event handler is not called when I click on a cell, (which
fires the event).

....any suggestions, is this a setting I can change somewhere?
--
j.f.dolan

It sounds like your code turns off events somewhere and it's not getting turned
back on.

Inside the VBE
hit ctrl-g (to see the immediate window)
type this and hit enter:
application.enableevents = true



joed wrote:

I'm using Excel 2003 on an XP box. If I write a vba event handler procedure
that has any error in it, for example mismatch, out of range, whatever, ... I
stop the run, make appropriate changes but, I have to completely shutdown
excel, start it back up in order to get the procedure to run again. If I do
not do this the event handler is not called when I click on a cell, (which
fires the event).

...any suggestions, is this a setting I can change somewhere?
--
j.f.dolan

--

Dave Peterson

That was exactly the problem and your solution has given me an even greater
understanding of events and their use, (and traps)

Thank you very much

---joe
--
j.f.dolan


"Dave Peterson" wrote:

It sounds like your code turns off events somewhere and it's not getting turned
back on.

Inside the VBE
hit ctrl-g (to see the immediate window)
type this and hit enter:
application.enableevents = true



joed wrote:

I'm using Excel 2003 on an XP box. If I write a vba event handler procedure
that has any error in it, for example mismatch, out of range, whatever, ... I
stop the run, make appropriate changes but, I have to completely shutdown
excel, start it back up in order to get the procedure to run again. If I do
not do this the event handler is not called when I click on a cell, (which
fires the event).

...any suggestions, is this a setting I can change somewhere?
--
j.f.dolan

--

Dave Peterson