VBA for Excel: Macros with DataEntryMode not running synchronously

[SB9999]

Hi all,

I was wondering if someone could help me!

I have three spreadsheets: server1.xls, client1.xls and client2.xls. The server1.xls sheet has a macro which executes a macro in the client1.xls spreadsheet and then another macro in the client2.xls spreadsheet. This is done by a "Run" statement in order.

My problem is this: each of those macros in the client1 and client2 spreadsheets involve going to data entry mode (Application.DataEntryMode). The problem is that once Client1.xls goes to data entry mode, at the same time, execution for some reason will return to server1.xls and it will attempt to "Run" the macro in the client2.xls spreadsheet.

I don't want this to happen until data entry mode in client1 is complete. If I remove the dataentrymode functionality the macros are run properly in their correct order, one after the other as they should. For some reason when I introduce dataentrymode I run into this problem.

Thanks in advance!!
Sam

 

[SkipVought]

Hi, SB9999,

Sounds to me like you want to

1. Run a routine where the LAST thing is to turn on DataEntryMode - the programs stops and...

2. The user enters data. Then...

3. When the user is done, run the next thing which begins by turning OFF the DataEntryMode

Am I missing something?
Skip,
metzgsk@voughtaircraft.com

 

[SB9999]

Hi Skip,

Yes that's what I'm doing. In each of the "client" workbooks the first thing that is done is turn on the data entry mode and then after data entry mode is done, it calls a subroutine (defined in Application.Onkey where the user can hit ESC to exit data entry mode) where the first thing done is turn off data entry mode. Even still, I would have thought as each of the client workbooks is opened it would "wait" until data entry mode exits. But instead it's going back to the "server" one and trying to run the next client workbook and macro anyway.

 

[SkipVought]

Dont' think that your program waits. Skip,
metzgsk@voughtaircraft.com

 

[SB9999]

Right, but I want it to wait until the data entry mode in client1.xls exits and the rest of the macro code in that worksheet is run, before attempting to open client2.xls and run the macro in there.

 

[SkipVought]

How do you know when the user is done entering data? Skip,
metzgsk@voughtaircraft.com

 

[SB9999]

The user hits ESC which triggers the subroutine (set up in Application.Onkey) that exits data entry mode and continues executing in that sheet.

If this is more helpful, here's the code for the server sheet:


Sub testmacro()
Run "Client1.xls!macro1"
Run "Client2.xls!macro2"
End Sub

And here's the code for each of the clients

Public Sub macro1()
ThisWorkbook.Activate
Application.Goto Reference:=Range("TESTR&quot 'range
Call DEntry
End Sub

Private Sub DEntry()
With Application
.OnKey "{ESC}", "dataEntryFinished" 'if User hits ESC - calls data entry mode exit routine
.Goto Range("TESTR&quot
.StatusBar = "Hit ESC when finished"
End With
Application.DataEntryMode = xlOn

End Sub

Public Sub dataEntryFinished()
'data entry mode exit routine
Application.DataEntryMode = xlOff
With Application
.OnKey "{ESC}"
.StatusBar = False
End With
MsgBox "Data Entry Mode Complete."
Call endingRoutine
End Sub

Public Sub endingRoutine()
MsgBox ("In EndingRoutine.&quot
Application.DisplayAlerts = False
Application.ScreenUpdating = True
ActiveWorkbook.Close
End Sub