Getting ‘focus’ back to Excel using Sendkeys statements

[Zakynthos]

Hi,

When I run the following program alone from Excel, it works fine:

Sheets("NEWTSform").Select
Select Case Range("H19").Value
Case 4
Call inputSMIDtonewtsorder
End Select

Select Case Range("H19").Value
Case 3
Call inputSMIEtonewtsorder
End Select


However, when I add this to another program which, again works fine as it
is, the program listed above doesn’t run properly – it will activate the
Window named in AppActivate, 'Staff Management' , but the ‘Call’ subroutines
don’t run - it just 'freezes'

I add in the following to the end of the previous routine (which works fine
alone) to get the ‘focus’ back to Excel before running it with:

Appactivate “Vantage Interactive Advanced.xlsâ€
With ThisWorkbook.Sheets (“NEWTSformâ€)

Appactivate “Staff Managementâ€


Sheets("NEWTSform").Select

Select Case Range("H19").Value
Case 4
Call inputSMIDtonewtsorder
End Select

Select Case Range("H19").Value
Case 3
Call inputSMIEtonewtsorder
End Select

End with

End Sub

It errors here, usually with End sub highlighted with an “Expected ‘End with
without with’ or ‘End Select’ without ‘Select’ or something similar!.

Where am I going run – it must be possible to do, if they run alone
independently, they should run end-on-end.

I’ve tried various combinations of code to reclaim the ‘focus’ back to
Excel - but it still errors!

Many thanks!

 

[Halim]

HI,
Remove first end select from
Select Case Range("H19").Value
Case 4
Call inputSMIDtonewtsorder
End Select

Select Case Range("H19").Value
Case 3
Call inputSMIEtonewtsorder
End Select

to:
Select Case Range("H19").Value
Case 4
Call inputSMIDtonewtsorder
Case 3
Call inputSMIEtonewtsorder
End Select

 

[Zakynthos]

Halim,

Many thanks for your suggestion - which I tried, but am still getting the
same error - End sub highlighted and an error comment of "Expected 'End with'"

I guess it's something to do with the AppActive code switching focus - I'm
showing below (with my commented notes) the end of the previous (working OK)
subroutine and this one:


'CLOSE WINDOW

DoEvents
SendKeys "%fc"


'activate SMI window and input as appropriate D or E info:


AppActivate "Microsoft Excel - Vantage Interactive Advanced tm"

With ThisWorkbook.Sheets("NEWTSform")

AppActivate "Staff Management"


Select Case Range("H19").Value
Case 4


Call inputSMIDtonewtsorder
Case 3
Call inputSMIEtonewtsorder
End Select


End With



End Sub


Many thanks!

 

[steve_doc]

Hi Zakynthos

Might not be totally relavent but the syntax is AppActivate not Appactivate

 

[NickHK]

Do you really want AppActivate ?
You seem to be activating workbooks not other applications. Check the Help
for AppActivate. What is this supposed to do ?
AppActivate "Staff Management"

And where are the keys being sent with SendKeys ?
It is a very unreliable method.

NickHK

 

[Zakynthos]

Nick,

Your questions helped me clarify what I was doing - AppActivate was fine, I
did activate the windows I needed and the code ran Ok as an 'application.run '

Once again, may thanks - I don't think I'd have got the solution without you.

Best wishes,

Tony

 

[Zakynthos]

Steve,

Yes, of course, a typo, but nevertheless I'm grateful to you for pointing it
out - yours and the other helpful comments helped me focus on my errors - now
all works perfectly!

Once again, thanks for your help.

Best wishes,

Tony