Excel Bug or Macro Problem?

[Don Wiss]

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]

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:[email protected]...
> 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).

[STEVE BELL]

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" <[email protected]> wrote in message
news:[email protected]...
> 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:[email protected]...
>> 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 Wiss]

On Tue, 02 Aug 2005, STEVE BELL <[email protected]> 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).

[STEVE BELL]

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:[email protected]...
> On Tue, 02 Aug 2005, STEVE BELL <[email protected]> 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 Wiss]

On Tue, 02 Aug 2005, STEVE BELL <[email protected]> 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).

[STEVE BELL]

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" <donwiss@no_spam.com> wrote in message
news:[email protected]...
> On Tue, 02 Aug 2005, STEVE BELL <[email protected]> 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).

[Don Wiss]

On Tue, 02 Aug 2005, STEVE BELL <[email protected]> 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).

[STEVE BELL]

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" <donwiss@no_spam.com> wrote in message
news:[email protected]...
> On Tue, 02 Aug 2005, STEVE BELL <[email protected]> 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).

[Don Wiss]

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" <[email protected]>
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

[STEVE BELL]

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" <donwiss@no_spam.com> wrote in message
news:[email protected]...
> 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" <[email protected]>
> 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
>

[Don Wiss]

On Wed, 03 Aug 2005, STEVE BELL <[email protected]> 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).

[STEVE BELL]

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" <donwiss@no_spam.com> wrote in message
news:[email protected]...
> On Wed, 03 Aug 2005, STEVE BELL <[email protected]> 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).

[Don Wiss]

On Wed, 03 Aug 2005, STEVE BELL <[email protected]> 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).