[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2051
  • Last Modified:

SendKeys in Excel Macros

When I include the command
     SendKeys "^{HOME}"
in an Excel 97 macro, I got a "VBA5.dll Not Found" error.  I installed Visual Basic Service Pack 3, which contains the file -- same results.  Then I copied the file to c:\WINNT\system32 and now get a Dr. Watson every time I run the macro.  I'm using NT 4.0, SP6.  What's going on?  Is there an alternative command to do a Ctrl-Home?
0
EDC
Asked:
EDC
  • 3
  • 2
1 Solution
 
calacucciaCommented:
Hi EDC,

The alternative I see is more simple than the sendkeys, but I do not understand why you have never tried this.

Here it is:

ActiveSheet.Cells(1,1).Select

or

ActiveSheet.Range("A1").Select
0
 
sergeTDCommented:
To make effective the use of
"SendKeys "{home}", True" and others "Sendkey" instructions, you have to use LOTUS navigation keys:
->Menu TOOLS->Options->Tab TRANSITION->select "Transition   Navigation KEYS".
Hope this helps.
Serge TD
0
 
sergeTDCommented:
I have also to precise an important point:

To be effective, the sendkeys instructions must not be executed in VBA Debug mode(step by step), so use F5 rather than F8 if you launch your macro from the Vbasic Window.

Serge TD
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
EDCAuthor Commented:
Thanks for your efforts!  However:

SergeTD, I launch my macro from a button on the spreadsheet.  And I turned on "Transition Navigation Keys" and still get the Dr. Watson.  (I even quit Excel and rebooted.)  And other users are getting the VBA5.dll error, too, even with "Transition Navigation Keys" on (I haven't bothered them further with taking them towards the Dr. Watson error).

Calacuccia, earlier in my macro I do some filtering and a freeze panes, so Ctrl-Home takes me to, for example, D880 -- but it may not be the same cell each time I run it..
0
 
EDCAuthor Commented:
Further investigation revealed that there is a problem with the particular workbook I am working with (it's the one that generates Dr. Watsons with MsgBox, OnTime, and who knows what else).  

When I used sergeTD's answer in another test workbook, it worked fine.  Thanks!
0
 
sergeTDCommented:
Hi EDC,
I am pleased you have found a solution with my little help.
I am using very often Sendkeys and if you need more help
about them, just use a comment to this question: I will
try to help you furthermore.
SergeTD,
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now