Bizarre macro problem
 

Hi all
This one has me stumped! I have a macro that at a certain point opens
another workbook in order to copy some data into the current worksheet.
However, it has suddenly started to stop running as soon as the workbook has
opened. By 'stop' I mean literally that, the macro just stops running - no
error message, Excel doesn't crash or any of the usual - the macro just
stops. However, when I try putting a break point just before the line that
opens the workbook and step through line by line it works fine. If the break
point is just after this line then the macro stops before it gets to it.
This started happening yesterday and re-booting did not help but shutting
down overnight did seem to fix it for the first few attempts but it has no
reverted to stopping again. Any help would be greatly appreciated!
Thanks
Andy

Bizarre macro problem
 

Sounds like it is expecting something to be active which isn't. Post the
code, sample data and highlight the problem area.

--
HTH

Bob Phillips

"Andy" wrote in message
...
Hi all
This one has me stumped! I have a macro that at a certain point opens
another workbook in order to copy some data into the current worksheet.
However, it has suddenly started to stop running as soon as the workbook
has
opened. By 'stop' I mean literally that, the macro just stops running -
no
error message, Excel doesn't crash or any of the usual - the macro just
stops. However, when I try putting a break point just before the line
that
opens the workbook and step through line by line it works fine. If the
break
point is just after this line then the macro stops before it gets to it.
This started happening yesterday and re-booting did not help but shutting
down overnight did seem to fix it for the first few attempts but it has no
reverted to stopping again. Any help would be greatly appreciated!
Thanks
Andy

Bizarre macro problem
 

two suggestions

1. try using a doevents just before and after your troublesome line
The doEvents statement tell excel to stop doing what it is doing an
let other processes have a go. It maybe that your breakpoint is jus
releasing the process and that the doevents would do the same thing.

2. Put an error trap around the code. depending on how the code wa
activated an error may just halt the code. It is particularly importan
to do this in user defined functions and dependant code that is calle
from the worksheet.

Sub mycode()

Dim str As String

On Error GoTo AnError

'my code ...........
str = 1 / 0

GoTo TheEnd
AnError:
MsgBox "error : " & Err.Number & " " & Err.Description
Stop 'you will have to decide what to do here
TheEnd:

End Sub

hope this help

--
tony
-----------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=39244

Bizarre macro problem
 

Have you tried running your work book on a different machine t
determine whether it's your machine, or your code that's causing th
probs

--
Rich_
-----------------------------------------------------------------------
Rich_z's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread.php?threadid=39244

Bizarre macro problem
 

Have you tried running your work book on a different machine t
determine whether it's your machine, or your code that's causing th
probs

--
Rich_
-----------------------------------------------------------------------
Rich_z's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread.php?threadid=39244

Bizarre macro problem
 

Hi Tony

Your first suggestion works!!! In fact I only needed to add DoEvents before
opening the workbook.

Out of interest I tried error trapping but that didn't work.

Thank you all sooooo much for your help and for maintaining my sanity!
Andy

"tony h" wrote:


two suggestions

1. try using a doevents just before and after your troublesome line.
The doEvents statement tell excel to stop doing what it is doing and
let other processes have a go. It maybe that your breakpoint is just
releasing the process and that the doevents would do the same thing.

2. Put an error trap around the code. depending on how the code was
activated an error may just halt the code. It is particularly important
to do this in user defined functions and dependant code that is called
from the worksheet.

Sub mycode()

Dim str As String

On Error GoTo AnError

'my code ...........
str = 1 / 0

GoTo TheEnd
AnError:
MsgBox "error : " & Err.Number & " " & Err.Description
Stop 'you will have to decide what to do here
TheEnd:

End Sub

hope this helps


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=392442

Bizarre macro problem
 

I think it was the combination of doEvents and releasing the shiftkey.

Andy wrote:

Hi Tony

Your first suggestion works!!! In fact I only needed to add DoEvents before
opening the workbook.

Out of interest I tried error trapping but that didn't work.

Thank you all sooooo much for your help and for maintaining my sanity!
Andy

"tony h" wrote:


two suggestions

1. try using a doevents just before and after your troublesome line.
The doEvents statement tell excel to stop doing what it is doing and
let other processes have a go. It maybe that your breakpoint is just
releasing the process and that the doevents would do the same thing.

2. Put an error trap around the code. depending on how the code was
activated an error may just halt the code. It is particularly important
to do this in user defined functions and dependant code that is called
from the worksheet.

Sub mycode()

Dim str As String

On Error GoTo AnError

'my code ...........
str = 1 / 0

GoTo TheEnd
AnError:
MsgBox "error : " & Err.Number & " " & Err.Description
Stop 'you will have to decide what to do here
TheEnd:

End Sub

hope this helps


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=392442



--

Dave Peterson