Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Bug or Macro Problem?
Every so often I have a problem where the macro code clearly runs, but the
action that it is supposed to do doesn't happen. Usually I just abandon what I want to do and do something else. In this case I don't want to give in. I have a cell change event on a data validation drop down list. It then calls another macro. Since the sheet is protected, I issue a Protect with UserInterfaceOnly:=True. I am then either hiding or unhiding some rows on that sheet. But the macro runs and the rows don't change. This problem has one unique characteristic that I've seen before. If I put a stop in the macro, Alt-F11 won't take me to the workbook. I can use the task bar to get to it, but then a click anyplace on the worksheet gets a dull thud noise. No menu works. Can't change sheets. All one can do it to use the task bar and return to Visual Basic. What gives? Thanks, Don <www.donwiss.com (e-mail link at home page bottom). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Bug or Macro Problem?
Don,
When you want to change back to the workbook from the VBE, you have to deactivate any running macro. To do this, click on the little black square (Reset) button on the VBA toolbar. A breakpoint in your code only pauses the macro but doesn't deactivate it. The macro is still active, waiting for you to hit F8 to step forward or Run to run to the next breakpoint or the end. If you want to see the sheet and run the macro at the same time, resize the windows so both are visible. You still won't be able to do anything on the sheet until the macro has been deactivated. Henry "Don Wiss" wrote in message ... Every so often I have a problem where the macro code clearly runs, but the action that it is supposed to do doesn't happen. Usually I just abandon what I want to do and do something else. In this case I don't want to give in. I have a cell change event on a data validation drop down list. It then calls another macro. Since the sheet is protected, I issue a Protect with UserInterfaceOnly:=True. I am then either hiding or unhiding some rows on that sheet. But the macro runs and the rows don't change. This problem has one unique characteristic that I've seen before. If I put a stop in the macro, Alt-F11 won't take me to the workbook. I can use the task bar to get to it, but then a click anyplace on the worksheet gets a dull thud noise. No menu works. Can't change sheets. All one can do it to use the task bar and return to Visual Basic. What gives? Thanks, Don <www.donwiss.com (e-mail link at home page bottom). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Bug or Macro Problem?
Henry,
I am always going back and forth with the macros in break mode or when stepping through code (with F8). The only time when I have problems is when a cell is in the middle of being edited (the cursor is still somewhere in a cell). One of the things that might be causing the problem is that all the cells are protected and can't be selected. The only way to help isolate the prolem is: 1. Put Option Explicit at the top of all modules 2. Compile the project 3. Down load a copy of Code Cleaner and "fix" your code 4. Carefully step through the code and monitor everything that happens. Make sure you exercise all the possibities within the code. 5. Make sure your code is as simplistic as possible (remove selections, replace complicated copy/paste code with simpler code, etc) 6. ????? (ask and ask again) (and post some or all of your code; mark where you think the problem is coming from).... -- steveB Remove "AYN" from email to respond "Henry" wrote in message ... Don, When you want to change back to the workbook from the VBE, you have to deactivate any running macro. To do this, click on the little black square (Reset) button on the VBA toolbar. A breakpoint in your code only pauses the macro but doesn't deactivate it. The macro is still active, waiting for you to hit F8 to step forward or Run to run to the next breakpoint or the end. If you want to see the sheet and run the macro at the same time, resize the windows so both are visible. You still won't be able to do anything on the sheet until the macro has been deactivated. Henry "Don Wiss" wrote in message ... Every so often I have a problem where the macro code clearly runs, but the action that it is supposed to do doesn't happen. Usually I just abandon what I want to do and do something else. In this case I don't want to give in. I have a cell change event on a data validation drop down list. It then calls another macro. Since the sheet is protected, I issue a Protect with UserInterfaceOnly:=True. I am then either hiding or unhiding some rows on that sheet. But the macro runs and the rows don't change. This problem has one unique characteristic that I've seen before. If I put a stop in the macro, Alt-F11 won't take me to the workbook. I can use the task bar to get to it, but then a click anyplace on the worksheet gets a dull thud noise. No menu works. Can't change sheets. All one can do it to use the task bar and return to Visual Basic. What gives? Thanks, Don <www.donwiss.com (e-mail link at home page bottom). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Bug or Macro Problem?
On Tue, 02 Aug 2005, STEVE BELL wrote:
I am always going back and forth with the macros in break mode or when stepping through code (with F8). Of course. The only time when I have problems is when a cell is in the middle of being edited (the cursor is still somewhere in a cell). That may be it! Remember I wrote this was a data validation drop down list. So the cursor is still in the drop down when it fires off the change event. I am using xl2002. So one solution would be to change to a real combo box. Or is there a way to do the change after the data validation has finished? Don <www.donwiss.com (e-mail link at home page bottom). |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Bug or Macro Problem?
Don,
Depends on what you mean when you say the cursor is still in the drop-down. Most of the time I use a predefined list and just select what I want - the change event fires. If I am typing something into the drop-down nothing happens until I use the Tab or Enter Key. Until then nothing will happen. In fact when you are working in a cell (any cell) all kinds of actions are put on hold until the Tab key, Enter key, (arrow key*), are clicked. * Only works for numeric entries. In summary - the entry must be complete. Many a time I leave the cursor active in a cell and go crazy until I realize that the interior of a cell is still active... And than I am using Excel 2k... -- steveB Remove "AYN" from email to respond "Don Wiss" wrote in message ... On Tue, 02 Aug 2005, STEVE BELL wrote: I am always going back and forth with the macros in break mode or when stepping through code (with F8). Of course. The only time when I have problems is when a cell is in the middle of being edited (the cursor is still somewhere in a cell). That may be it! Remember I wrote this was a data validation drop down list. So the cursor is still in the drop down when it fires off the change event. I am using xl2002. So one solution would be to change to a real combo box. Or is there a way to do the change after the data validation has finished? Don <www.donwiss.com (e-mail link at home page bottom). |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Bug or Macro Problem?
On Tue, 02 Aug 2005, STEVE BELL wrote:
Depends on what you mean when you say the cursor is still in the drop-down. Most of the time I use a predefined list and just select what I want - the change event fires. If I am typing something into the drop-down nothing happens until I use the Tab or Enter Key. Until then nothing will happen. Well, there are only two choices on this list. The user would never type them in. They would drop down the list and select. I'm not sure just what the status is of the cell when this sub is then run: Private Sub Worksheet_Change(ByVal Target As Excel.Range) End Sub Should I be using another type of event? I see on the list a SelectionChange event. But trying it right now a data validation drop down doesn't trigger it. Don <www.donwiss.com (e-mail link at home page bottom). |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Bug or Macro Problem?
Don,
Don't know about Excel 2002, or Excel 2003 But selecting from a Data Validation List fires the change event in 2000. Selecting change is when you select another cell with the arrow key, enter key, tab key, or mouse. Change is when the contents of a cell are changed. Usually including changing the selection of a Data Validation. (Of course if the Data Validation already shows your choice and you don't change it - than it hasn't changed) -- steveB Remove "AYN" from email to respond "Don Wiss" wrote in message ... On Tue, 02 Aug 2005, STEVE BELL wrote: Depends on what you mean when you say the cursor is still in the drop-down. Most of the time I use a predefined list and just select what I want - the change event fires. If I am typing something into the drop-down nothing happens until I use the Tab or Enter Key. Until then nothing will happen. Well, there are only two choices on this list. The user would never type them in. They would drop down the list and select. I'm not sure just what the status is of the cell when this sub is then run: Private Sub Worksheet_Change(ByVal Target As Excel.Range) End Sub Should I be using another type of event? I see on the list a SelectionChange event. But trying it right now a data validation drop down doesn't trigger it. Don <www.donwiss.com (e-mail link at home page bottom). |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Bug or Macro Problem?
On Tue, 02 Aug 2005, STEVE BELL wrote:
But selecting from a Data Validation List fires the change event in 2000. But can you then do something on that same sheet? 1. Give a cell the Range name "TestRange" (or simply put the cell address in the range in the macro.) 2. Give the cell a data validation drop down list. 3. Put this macro behind the sheet. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("TestRange").Address Then Stop End If End Sub 4. Drop down the list and change the selection. When stopped can you Alt-F11 to the sheet and do anything? Don <www.donwiss.com (e-mail link at home page bottom). |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Bug or Macro Problem?
Don,
You should be able to do what ever you want. Just remember that the change event will fire with a change in any cell. You can restrict this by using If Target.Address=$A$1 ''' change to the appropriate cell or If Target.Row = 1 ''' change to the appropriate row # or If Target.Column = 1 ''' change to the appropriate column # And it is wise to add the following to most code to suppress event firing during the code: At the beginning Application.EnableEvents = False at the end Application.EnableEvents = True -- steveB Remove "AYN" from email to respond "Don Wiss" wrote in message ... On Tue, 02 Aug 2005, STEVE BELL wrote: But selecting from a Data Validation List fires the change event in 2000. But can you then do something on that same sheet? 1. Give a cell the Range name "TestRange" (or simply put the cell address in the range in the macro.) 2. Give the cell a data validation drop down list. 3. Put this macro behind the sheet. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range("TestRange").Address Then Stop End If End Sub 4. Drop down the list and change the selection. When stopped can you Alt-F11 to the sheet and do anything? Don <www.donwiss.com (e-mail link at home page bottom). |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Bug or Macro Problem?
Nope I can't. The workbook has 45 sheets, dozens of forms and modules. When
you get that big, Excel gets flaky. I switched to a combo box from the control toolbar and all is fine. On Tue, 02 Aug 2005 16:16:47 GMT, "STEVE BELL" wrote: Don, You should be able to do what ever you want. Just remember that the change event will fire with a change in any cell. You can restrict this by using If Target.Address=$A$1 ''' change to the appropriate cell or If Target.Row = 1 ''' change to the appropriate row # or If Target.Column = 1 ''' change to the appropriate column # And it is wise to add the following to most code to suppress event firing during the code: At the beginning Application.EnableEvents = False at the end Application.EnableEvents = True |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Bug or Macro Problem?
Don,
Without seeing your workbook - it is not my place to judge. But with a large workbook - I must agree that "funny" things start happening. My experience has been to find ways to simplify the workbook. 1. Make sure that what excel sees as the last cell, really is the last cell. 2. Remove as many custom number formats as possible. 3. Keep all formatting as simple as posssible. 4. If you can get rid of formulas - this helps. One of the things I do is have excel fill in the formulas and than replace with values. 5. Try to keep your code and formulas as streamlined as possible. 6. In code get rid of as many selections as you can. 7. Compile your code and look for places where it slows down. Page set up code is notoriously slow is one example - remove as many lines from it as possible. 8. and the list goes on - the general rule is simplify, simplify, simplify.... good luck.... -- steveB Remove "AYN" from email to respond "Don Wiss" wrote in message ... Nope I can't. The workbook has 45 sheets, dozens of forms and modules. When you get that big, Excel gets flaky. I switched to a combo box from the control toolbar and all is fine. On Tue, 02 Aug 2005 16:16:47 GMT, "STEVE BELL" wrote: Don, You should be able to do what ever you want. Just remember that the change event will fire with a change in any cell. You can restrict this by using If Target.Address=$A$1 ''' change to the appropriate cell or If Target.Row = 1 ''' change to the appropriate row # or If Target.Column = 1 ''' change to the appropriate column # And it is wise to add the following to most code to suppress event firing during the code: At the beginning Application.EnableEvents = False at the end Application.EnableEvents = True |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Bug or Macro Problem?
On Wed, 03 Aug 2005, STEVE BELL wrote:
1. Make sure that what excel sees as the last cell, really is the last cell. You mean no stray cells at bottom or right? There are none of those. 2. Remove as many custom number formats as possible. That I have not done. 3. Keep all formatting as simple as posssible. I never format an empty or hidden cell. Most of the custom ones I use are to either blank out a zero or to display a dash. 4. If you can get rid of formulas - this helps. One of the things I do is have excel fill in the formulas and than replace with values. I do that elsewhere, where there is a calc button, but not here. All is either on auto calc, or calculations are done on sheet deactivates. 5. Try to keep your code and formulas as streamlined as possible. It is absolutely as streamlined as possible. 6. In code get rid of as many selections as you can. Selections? What are those? 7. Compile your code and look for places where it slows down. Page set up code is notoriously slow is one example - remove as many lines from it as possible. I do compile and periodically clean. What is page set up code? You mean when you print? 8. and the list goes on - the general rule is simplify, simplify, simplify.... As I noted it is as simple as possible. There is absolutely no redundant code. The problem is they want all in one workbook, and this tool does all deal pricing and documentation, and covers three different lines of business. Plus a whole section of sheets that captures the deal info and ships to Europe (controlled by a mega add-in). Don <www.donwiss.com (e-mail link at home page bottom). |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Bug or Macro Problem?
Don,
Sounds like I'm preaching to the choir... What is page set up code? You mean when you print? YES. Selections? What are those? Like Range("A1").Select (but you must be putting me on)... But remember - even the best coders find ways to make their code run faster & smoother... Keep on Exceling... -- steveB Remove "AYN" from email to respond "Don Wiss" wrote in message ... On Wed, 03 Aug 2005, STEVE BELL wrote: 1. Make sure that what excel sees as the last cell, really is the last cell. You mean no stray cells at bottom or right? There are none of those. 2. Remove as many custom number formats as possible. That I have not done. 3. Keep all formatting as simple as posssible. I never format an empty or hidden cell. Most of the custom ones I use are to either blank out a zero or to display a dash. 4. If you can get rid of formulas - this helps. One of the things I do is have excel fill in the formulas and than replace with values. I do that elsewhere, where there is a calc button, but not here. All is either on auto calc, or calculations are done on sheet deactivates. 5. Try to keep your code and formulas as streamlined as possible. It is absolutely as streamlined as possible. 6. In code get rid of as many selections as you can. Selections? What are those? 7. Compile your code and look for places where it slows down. Page set up code is notoriously slow is one example - remove as many lines from it as possible. I do compile and periodically clean. What is page set up code? You mean when you print? 8. and the list goes on - the general rule is simplify, simplify, simplify.... As I noted it is as simple as possible. There is absolutely no redundant code. The problem is they want all in one workbook, and this tool does all deal pricing and documentation, and covers three different lines of business. Plus a whole section of sheets that captures the deal info and ships to Europe (controlled by a mega add-in). Don <www.donwiss.com (e-mail link at home page bottom). |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Bug or Macro Problem?
On Wed, 03 Aug 2005, STEVE BELL wrote:
Sounds like I'm preaching to the choir... I've been professionally programming pricing applications for 24 years. Including the writing and selling of a software package to the Wall St community. Now VBA for insurance. Page set up code is notoriously slow is one example - remove as many lines from it as possible. What is page set up code? You mean when you print? YES. I do change a custom footer and set print ranges, though most is preset. Printing is slow, but then I'm doing things, like looping to find unused rows to hide. But they don't print often, and when they do they print all the pages at once. (A print menu comes up with three columns for the three lines of business.) Selections? What are those? Like Range("A1").Select (but you must be putting me on)... I rarely use them. Generally only in the clear macro, which they don't use. Instead I use With blocks. I do use them when there is a user error. The edit code takes them to the error before the MsgBox. Don <www.donwiss.com (e-mail link at home page bottom). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel-Macro Problem | Excel Discussion (Misc queries) | |||
Macro problem in Excel | Excel Discussion (Misc queries) | |||
problem with macro on Excel | Excel Worksheet Functions | |||
Excel 2k3 Macro Problem | Excel Programming | |||
Excel XP macro problem | Excel Programming |