VB Code stops working

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" <MichelleK@discussions.microsoft.com> wrote in message
news:86203478-61C2-42F2-98A3-46784DED8B3F@microsoft.com...
> 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" <MichelleK@discussions.microsoft.com> wrote in message
> news:86203478-61C2-42F2-98A3-46784DED8B3F@microsoft.com...
> > 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" <MichelleK@discussions.microsoft.com> wrote in message
news:147CC56D-5FAC-45CB-8E49-09C897F6F21F@microsoft.com...
> 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" <MichelleK@discussions.microsoft.com> wrote in message
> > news:86203478-61C2-42F2-98A3-46784DED8B3F@microsoft.com...
> > > 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" <hallpa1@yahoo.com> wrote in message
news:1114614555.766012.110960@g14g2000cwa.googlegroups.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" <MichelleK@discussions.microsoft.com> wrote in message
news:86203478-61C2-42F2-98A3-46784DED8B3F@microsoft.com...
> 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" <MichelleK@discussions.microsoft.com> wrote in message
news:9DAE49DA-6202-4F06-830A-1FF3882D149F@microsoft.com...
> 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