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). |
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 |