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

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.

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

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

Maybe it's causing trouble???

wrote:

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.

--

Dave Peterson

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

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.

--
Jim
wrote in message
...
| 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.

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

On Mar 27, 3:33 pm, "Jim Rech" wrote:
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.

--
wrote in message

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

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.

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

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

wrote:

On Mar 27, 3:33 pm, "Jim Rech" wrote:
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.

--
wrote in message

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

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.

--

Dave Peterson

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

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.

--
Jim
wrote in message
...
On Mar 27, 3:33 pm, "Jim Rech" wrote:
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.

--
wrote in message

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

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.

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

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.

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

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.

--
Jim
wrote in message
...
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.