Workbook open problem

[UnsolvedCoding]

Greetings one and all,

This one has been bothering me for awhile and Microsoft hasn't been any help.

I have code to open a workbook, however due to the known bug in Excel sometimes the code simply stops when the workbook is opened. Microsofts patch (below) deals with the shift key and isn't helping and I need to find a way to open the workbook, run the code to fill out the worksheets and then close the workbook.

I can step through the process and most of the time run it directly, and if the workbook is open I can skip the line to open the workbook and it will run fine. But getting the code to run all the time with the workbook open event is problematic.

Does anyone know how to resolve this?

'Declare API
Declare Function GetKeyState Lib "User32" _
(ByVal vKey As Integer) As Integer
Const SHIFT_KEY = 16
 
Function ShiftPressed() As Boolean

'Returns True if shift key is pressed
    ShiftPressed = GetKeyState(SHIFT_KEY) < 0
End Function


Sub Demo()
    Do While ShiftPressed()
        DoEvents
    Loop
    Workbooks.Open = "C:\My Documents\ShiftKeyDemo.xls"
End Sub

It has taken me a while to make sense of what I hear at work involving computers. There is much talk of bugs and questions about Raid.
Therefore I have come to the logical conclusion that the only way to have a properly functioning computer is to regularly spray it with Raid bug killer.

 

[SkipVought]

Hi,

It's not at all clear what's happening and what's running when.

Where is the code that you posted running?

So some other workbook is being opened or not. And then some other code is running to "fill out the worksheets and [save and] close the workbook."

Got a shell game here unless you detail the process.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[UnsolvedCoding]

The main workbook holds all the code and runs it, the other workbook simply is a log of data entry errors. So as the macro in the main workbook runs, it opens the log but when the log is opened the code simply stops. There are no error warnings, no pop ups, nothing.

I found out from various web sites that this is a random problem and it still occurs in all versions of Excel and found this Microsfot link [URL unfurl="true"]https://support.microsoft.com/en-us/kb/555263[/url] pointing to what happened originally.

There is the possibility of doing the Application.OnKey in the workbook open event as a work around.

It has taken me a while to make sense of what I hear at work involving computers. There is much talk of bugs and questions about Raid.
Therefore I have come to the logical conclusion that the only way to have a properly functioning computer is to regularly spray it with Raid bug killer.

 

[SkipVought]

Have you stepped thru the code that stops?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[UnsolvedCoding]

Yes. When it is stepped it works.

I changed the macro keyboard shortcuts to have no shift and the code runs as expected.



It has taken me a while to make sense of what I hear at work involving computers. There is much talk of bugs and questions about Raid.
Therefore I have come to the logical conclusion that the only way to have a properly functioning computer is to regularly spray it with Raid bug killer.

 

[SkipVought]

I've always been wary of shiftless individuals. But this seems like a perfectly good resolution.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

You could mark "notify before state loss" in VBE general options.

The line:
[tt]Workbooks.Open = "C:\My Documents\ShiftKeyDemo.xls"[/tt]
shouldn't work, firsty, Workbooks.Open returns object, secondly, the way you try to pass the path as argument is not proper.

combo

 

[SkipVought]

Combo, good call! I passed right over that

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[UnsolvedCoding]

Combo, I tried what you suggested with the VBE and it will still open the workbook and stop running.

Skip, The shift key was removed from the macro shortcut but now I am running into problems of users needing to bold or copy information and macros running instead of ctrl-c copying etc.

We did determine that Excel doesn't use keyboard shortcuts for Ctrl-E, Ctrl-J, , Ctrl-L, Ctrl-M and Ctrl-Q.




It has taken me a while to make sense of what I hear at work involving computers. There is much talk of bugs and questions about Raid.
Therefore I have come to the logical conclusion that the only way to have a properly functioning computer is to regularly spray it with Raid bug killer.

 

[SkipVought]

So as the macro in the main workbook runs, it opens the log but when the log is opened the code simply stops."

So when you're referring to the macro that stops when the log file is opened, what is supposed to happen in the process that follows the log file opening in the macro?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[zelgar]

If a macro is running, you can't interact with a spreadsheet (or Excel) until it is finished or you manually stop the code (e.g., Ctrl-C).

The reason why the macro stops working when you open the log is that the macro is trying to open a file that is already open. One thing you could try is having the macro check to see if the log file is already open before you try an open it in the macro. If it is open, I would strongly recommend you have it require the user to close the log file before the macro would continue, but it's up to you.

 

[combo]

According to your macro, after [tt]Workbooks.Open...[/tt] is [tt]End Sub[/tt]. What do you expect?
If you need to lock macros in a workbook you try to open, temporary setting [tt]Application.AutomationSecurity=msoAutomationSecurityForceDisable[/tt] is a better choice.
If you need to disable only the [tt]Workbook_Open()[/tt] macro, [tt]Application.EnableEvents=False[/tt] will do it.

combo