Excel 2007 VBA setting cells(x,y).value causes abnormal exit from subroutine/functions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

beau.moore@gmail.com - 27 Mar 2008 19:32 GMT

Hi All,

I am having a difficult time trying to resolve this issue.

I have a simple workbook, with a simple active x button. The onclick
subroutine for the button is very simple.

Private Sub CommandButton1_Click()
Sheets("Sheet1").Cells(1, 1).Value = 12345
Sheets("Sheet1").Cells(1, 2).Value = "test"
Sheets("Sheet1").Cells(11, 10).Value = "more testing"
End Sub

My problem is that when I click the button, only the first statement
is executed, Sheets("Sheet1").Cells(1, 1).Value = 12345, then nothing.
If I step through with debug, it highlights the first statement then
when I step through (F8) it does set the value but then it immediately
exits the subroutine.

I can use other statements and functions with no problem, but the
moment I try to alter the value of a cell it causes the sub or
function to exit.

Is there some configuration issue I am missing? I can't find any from
google or the news groups about anyone having problem like this.

Any help anyone can provide is greatly appreciated.

Reply to this Message

Dave Peterson - 27 Mar 2008 19:49 GMT

Do you have any events that fire when you make a change to Sheet1?

Maybe it's causing trouble???

> Hi All,
>
[quoted text clipped - 23 lines]
>
> Any help anyone can provide is greatly appreciated.

Signature

Dave Peterson

Reply to this Message

Jim Rech - 27 Mar 2008 20:33 GMT

Does this problem occur only when calc mode is automatic? If so and if you
have any UDFs it might be an interaction with them.

Signature

Jim

| Hi All,
|
[quoted text clipped - 23 lines]
|
| Any help anyone can provide is greatly appreciated.

Reply to this Message

beau.moore@gmail.com - 27 Mar 2008 22:06 GMT

> Does this problem occur only when calc mode is automatic? If so and if you
> have any UDFs it might be an interaction with them.
[quoted text clipped - 30 lines]
> |
> | Any help anyone can provide is greatly appreciated.

I am not doing anything special, just a simple new excel workbook. I
have never set calc mode so it is still what it is defaulted to from
the base installation.

There are no other user defined functions firing, I believe that to be
true for two reasons. One it is a new blank workbook and I have
defined none. Two, the subroutine aborts execution right after I set
the value of the cell. Isn't VBA an event driven procedural language?
meaning that two events cant really fire at the exact same time? When
this subroutine fails it is if it is executing the statement "exit
sub", at least as far as the debugger shows.

I tried to do a simple test to see if it was exiting the subroutine or
just crashing with no error. I took and made a simple subroutine and
then called the offending subroutine. If the subroutine was exiting
gracefully then the next statement after the call function would be
executed. (see code)

Private Sub CommandButton1_Click()
MsgBox "Calling Subroutine"
Call test
MsgBox "Completed Subroutine"
End Sub

Private Sub test()
Sheets("Sheet1").Cells(11, 10).Value = 54321
Sheets("Sheet1").Cells(12, 10).Value = 12345
End Sub

When I ran this I only got the first msgbox, so what ever is
interrupting the code is failing after the first statement in the
test() subroutine: Sheets("Sheet1").Cells(11, 10).Value = 54321.

Reply to this Message

Dave Peterson - 27 Mar 2008 23:26 GMT

And if you create a small test workbook with just enough info to test your
routine, does it work ok?

> > Does this problem occur only when calc mode is automatic? If so and if you
> > have any UDFs it might be an interaction with them.
[quoted text clipped - 63 lines]
> interrupting the code is failing after the first statement in the
> test() subroutine: Sheets("Sheet1").Cells(11, 10).Value = 54321.

Signature

Dave Peterson

Reply to this Message

Jim Rech - 28 Mar 2008 16:01 GMT

>>One it is a new blank workbook

That rules out my idea.

One thing I would try is starting Excel in safe mode just to be sure no
add-in or something else is involved.

From Start->Run run "Excel.exe /s" (no quotes, there is a space before the
slash). Then try your code.

Signature

Jim

>> Does this problem occur only when calc mode is automatic? If so and if
>> you
[quoted text clipped - 64 lines]
> interrupting the code is failing after the first statement in the
> test() subroutine: Sheets("Sheet1").Cells(11, 10).Value = 54321.

Reply to this Message

beau.moore@gmail.com - 28 Mar 2008 20:44 GMT

When I tried safe mode it worked, but it prompted me to enable active
x controls. It is good that it worked but now I need to find out why
it does work in normal mode.

Reply to this Message

Jim Rech - 28 Mar 2008 22:06 GMT

So the next step is to find what workbook or add-in that loads with Excel is
causing the problem. You should look at Add-ins under Excel Options on the
Office button menu. If you see any add-ins loading there you could unload
them all or one at a time and test your macro. Also look in your XLSTART
folder(s). Most anything there loads with Excel also. You could move them
out (with Excel closed) and test your macro.

Signature

Jim

> When I tried safe mode it worked, but it prompted me to enable active
> x controls. It is good that it worked but now I need to find out why
> it does work in normal mode.

Reply to this Message