Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code stops working
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code stops working
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code stops working
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code stops working
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code stops working
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code stops working
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code stops working
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code stops working
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code stops working
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? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code stops working
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" 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code stops working
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" wrote in message ... 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" 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code stops working
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" wrote in message ... 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" 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code stops working
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" wrote in message ... 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" wrote in message ... 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" 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code stops working
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" wrote in message ... 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" wrote in message ... 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" 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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code stops working
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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code stops working
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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code stops working
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF just STOPS WORKING | Excel Worksheet Functions | |||
Autofilter Stops Working | Excel Worksheet Functions | |||
VBA Stops Working? | Excel Programming | |||
Hyperlink stops working | Excel Worksheet Functions | |||
Worksheet_SelectionChange stops working | Excel Programming |