VB Code stops working

[Michelle K]

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

[K Dales]

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

[Bob Phillips]

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

--
HTH

Bob Phillips

"Michelle K" <[email protected]> wrote in message
news:[email protected]...
> 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

[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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[p.hall]

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?

[Bob Phillips]

You have put it in Thisworkbook code module?

--

HTH

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


"Michelle K" <[email protected]> wrote in message
news:[email protected]...
> 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

[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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

[Bob Phillips]

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

--
HTH

Bob Phillips

"p.hall" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

[Michelle K]

I added a message box into the top of the code and it worked - meaning, it is
reading the code. How come then that it isn't evaluating the condition
anymore?

"Tom Ogilvy" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>

[Tom Ogilvy]

There are so many conditions in your code that is has to meet to do
anything, it would not be surprising that all the conditions are not met.
(You may actually have a logic error and you are not checking what you think
you are checking). There must be an easier check you can perform, but I
have no knowledge of what you are doing, so I can't suggest anything
specific. I would think the worksheet functions countif or sumif might be
able to play a roll here.

--
Regards,
Tom Ogilvy

"Michelle K" <[email protected]> wrote in message
news:[email protected]...
> I added a message box into the top of the code and it worked - meaning, it
is
> reading the code. How come then that it isn't evaluating the condition
> anymore?
>
> "Tom Ogilvy" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >

[Michelle K]

Thanks Tom.

It's a survey that lets you rate performance from 1-5 and importance from
1-5 as well. At the end of each section, we want users to have 3 items with
a rating of 5, 2 items with a rating of 4 and so on. I am not the idea
behind the requirements. I am just writing the code for a committee here at
work. So what i did was to add radio buttons and then tallied how many were
5's, etc at the bottom of each section (Rows 27, 42 and 57).

Let me check my logic and see how it goes. If you think of anything else,
please let me know. It's one of the big boss' pet projects.

"Tom Ogilvy" wrote:

> There are so many conditions in your code that is has to meet to do
> anything, it would not be surprising that all the conditions are not met.
> (You may actually have a logic error and you are not checking what you think
> you are checking). There must be an easier check you can perform, but I
> have no knowledge of what you are doing, so I can't suggest anything
> specific. I would think the worksheet functions countif or sumif might be
> able to play a roll here.
>
> --
> Regards,
> Tom Ogilvy
>
> "Michelle K" <[email protected]> wrote in message
> news:[email protected]...
> > I added a message box into the top of the code and it worked - meaning, it
> is
> > reading the code. How come then that it isn't evaluating the condition
> > anymore?
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

[Tom Ogilvy]

I think your logic is wrong. You should be using OR instead of AND. AND
would require that the user had the wrong number in every one of the cells
that you check. OR would stop closing if any cell that you checked did not
meet the criteria.

--
Regards,
Tom Ogilvy

"Michelle K" <[email protected]> wrote in message
news:[email protected]...
> Thanks Tom.
>
> It's a survey that lets you rate performance from 1-5 and importance from
> 1-5 as well. At the end of each section, we want users to have 3 items
with
> a rating of 5, 2 items with a rating of 4 and so on. I am not the idea
> behind the requirements. I am just writing the code for a committee here
at
> work. So what i did was to add radio buttons and then tallied how many
were
> 5's, etc at the bottom of each section (Rows 27, 42 and 57).
>
> Let me check my logic and see how it goes. If you think of anything else,
> please let me know. It's one of the big boss' pet projects.
>
> "Tom Ogilvy" wrote:
>
> > There are so many conditions in your code that is has to meet to do
> > anything, it would not be surprising that all the conditions are not
met.
> > (You may actually have a logic error and you are not checking what you
think
> > you are checking). There must be an easier check you can perform, but I
> > have no knowledge of what you are doing, so I can't suggest anything
> > specific. I would think the worksheet functions countif or sumif might
be
> > able to play a roll here.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Michelle K" <[email protected]> wrote in message
> > news:[email protected]...
> > > I added a message box into the top of the code and it worked -
meaning, it
> > is
> > > reading the code. How come then that it isn't evaluating the
condition
> > > anymore?
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in
message
> > > > > > news:[email protected]...
> > > > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[Michelle K]

PERFECT!

Thanks so much!

"Tom Ogilvy" wrote:

> I think your logic is wrong. You should be using OR instead of AND. AND
> would require that the user had the wrong number in every one of the cells
> that you check. OR would stop closing if any cell that you checked did not
> meet the criteria.
>
> --
> Regards,
> Tom Ogilvy
>
> "Michelle K" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Tom.
> >
> > It's a survey that lets you rate performance from 1-5 and importance from
> > 1-5 as well. At the end of each section, we want users to have 3 items
> with
> > a rating of 5, 2 items with a rating of 4 and so on. I am not the idea
> > behind the requirements. I am just writing the code for a committee here
> at
> > work. So what i did was to add radio buttons and then tallied how many
> were
> > 5's, etc at the bottom of each section (Rows 27, 42 and 57).
> >
> > Let me check my logic and see how it goes. If you think of anything else,
> > please let me know. It's one of the big boss' pet projects.
> >
> > "Tom Ogilvy" wrote:
> >
> > > There are so many conditions in your code that is has to meet to do
> > > anything, it would not be surprising that all the conditions are not
> met.
> > > (You may actually have a logic error and you are not checking what you
> think
> > > you are checking). There must be an easier check you can perform, but I
> > > have no knowledge of what you are doing, so I can't suggest anything
> > > specific. I would think the worksheet functions countif or sumif might
> be
> > > able to play a roll here.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Michelle K" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I added a message box into the top of the code and it worked -
> meaning, it
> > > is
> > > > reading the code. How come then that it isn't evaluating the
> condition
> > > > anymore?
> > > >
> > > > "Tom Ogilvy" wrote:
> > > >
> > > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in
> message
> > > > > > > news:[email protected]...
> > > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>