Hotkey

[selsley]

Can anyone tell me how I can allocate an F-key to open a particula
worksheet while in Excel please?

TIA

Steve Englan

 

[Dave Peterson]

Take a look at application.onkey in VBA's help.

Then in a workbook that opens each time you open excel (either personal.xls or a
workbook in your XLStart folder):

Put this in a general module:

Option Explicit
Sub auto_open()
Application.OnKey "{f1}", "OpenMyFile"
End Sub
Sub auto_close()
Application.OnKey "{F1}"
End Sub
Sub OpenMyFile()

Dim testWkbk As Workbook
Dim myFileName As String
Dim myPath As String

myFileName = "book1.xls"
myPath = "C:\my documents\excel\"

If Dir(myPath & myFileName) = "" Then
MsgBox "It's no longer in that location!"
Exit Sub
End If

Set testWkbk = Nothing
On Error Resume Next
Set testWkbk = Workbooks(myFileName)
On Error GoTo 0

If testWkbk Is Nothing Then
Workbooks.Open Filename:=myPath & myFileName
Else
MsgBox "A file with that name is already open!"
Exit Sub
End If
End Sub


Change the F-key, myPath and myFilename as required.

 

[Chip Pearson]

Steve,

Take a look at the OnKey method. For example,

Sub Auto_Open()
' assign the key to a macro
Application.OnKey "{F10}","OpenTheWorkbook"
End Sub

Sub OpenTheWorkbook()
Workbook.Open filename:="C:\your_path\filename.xls"
End Sub

Sub Auto_Close()
' restore key to default behavior
Application.OnKey "{F10}"
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[selsley]

Chip

Many thanks for this. Another problem solved by someone on this forum!

Stev

 

[selsley]

Chip

Tried this but I'm doing something wrong. What code should I have, fo
example, to open worksheet "Sundries" while I'm in a differen
worksheet in workbook "Main"

TIA

Steve in Cheltenham, Englan

 

[David McRitchie]

You could get that by recording a macro to see what you get.
Add the following line to the macro.
sheets("Sundries").activate

Davd Peterson's code would check if the workbook
were already open. Use "Main.xls" instead of "book1.xls"
and fix the pathname. In Chip Pearson's fix the full pathname
that includes filename.xls