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

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" <donwiss@no_spam.com> wrote in message
news:pnbte1t50rmsm618lvi1fvdotn4j5ar5ng@4ax.com...
> 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).

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" <henry.best1@ntlworld.com> wrote in message
news:UryHe.24398$Ag3.17697@newsfe4-gui.ntli.net...
> 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" <donwiss@no_spam.com> wrote in message
> news:pnbte1t50rmsm618lvi1fvdotn4j5ar5ng@4ax.com...
>> 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).
>
>

On Tue, 02 Aug 2005, STEVE BELL <AYNrand451@verizon.net> 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).

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" <donwiss@no_spam.com> wrote in message
news:d7gte11i1memmefbsimjjkpb3vsladnhuk@4ax.com...
> On Tue, 02 Aug 2005, STEVE BELL <AYNrand451@verizon.net> 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).

On Tue, 02 Aug 2005, STEVE BELL <AYNrand451@verizon.net> 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).