I'm trying to add a keyboard shortcut for a function I have. I have the
following code in the "ThisWorkbook" 'module':

Option Explicit
Const DATECOL As String = "B"

Public Sub FindToday()
' Keyboard Shortcut: Ctrl+T
Dim r As Range

On Error Resume Next
Set r = ThisWorkbook.Worksheets(1).Range(DATECOL & ":" &
DATECOL).Find(what:=Date, LookIn:=xlValues)
If Not r Is Nothing Then
r.Select
End If
ThisWorkbook.Sheets(1).Activate
End Sub

Public Sub Workbook_Open()
Application.MacroOptions Macro:="FindToday", HasShortcutKey:=True,
ShortcutKey:="t"
FindToday

End Sub

I get:

Run-time error "1004":

Method 'MacroOptions' of object '_Application' failed
<<

I've tried all sorts of things (making the routines public, trying
Macro:=ThisWorkbook.Name & "!" & "FindToday", trying it without
assigning the shortcut key - all fail.

Any suggestions, please?!

Put FindToday in its own module, leave the Workbook_Open where it is.
Bob Umlas
Excel MVP

"is" wrote in message
oups.com...
I'm trying to add a keyboard shortcut for a function I have. I have the
following code in the "ThisWorkbook" 'module':

Option Explicit
Const DATECOL As String = "B"

Public Sub FindToday()
' Keyboard Shortcut: Ctrl+T
Dim r As Range

On Error Resume Next
Set r = ThisWorkbook.Worksheets(1).Range(DATECOL & ":" &
DATECOL).Find(what:=Date, LookIn:=xlValues)
If Not r Is Nothing Then
r.Select
End If
ThisWorkbook.Sheets(1).Activate
End Sub

Public Sub Workbook_Open()
Application.MacroOptions Macro:="FindToday", HasShortcutKey:=True,
ShortcutKey:="t"
FindToday

End Sub

I get:

Run-time error "1004":

Method 'MacroOptions' of object '_Application' failed
<<

I've tried all sorts of things (making the routines public, trying
Macro:=ThisWorkbook.Name & "!" & "FindToday", trying it without
assigning the shortcut key - all fail.

Any suggestions, please?!

You need to put the FindToday macro in a standard code module, not
ThisWorkbook.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"is" wrote in message
oups.com...
I'm trying to add a keyboard shortcut for a function I have. I have the
following code in the "ThisWorkbook" 'module':

Option Explicit
Const DATECOL As String = "B"

Public Sub FindToday()
' Keyboard Shortcut: Ctrl+T
Dim r As Range

On Error Resume Next
Set r = ThisWorkbook.Worksheets(1).Range(DATECOL & ":" &
DATECOL).Find(what:=Date, LookIn:=xlValues)
If Not r Is Nothing Then
r.Select
End If
ThisWorkbook.Sheets(1).Activate
End Sub

Public Sub Workbook_Open()
Application.MacroOptions Macro:="FindToday", HasShortcutKey:=True,
ShortcutKey:="t"
FindToday

End Sub

I get:

Run-time error "1004":

Method 'MacroOptions' of object '_Application' failed
<<

I've tried all sorts of things (making the routines public, trying
Macro:=ThisWorkbook.Name & "!" & "FindToday", trying it without
assigning the shortcut key - all fail.

Any suggestions, please?!

As a minimum,
Move your function to a general module (insert=Module). No a sheet module
or the thisworkbook module.

--
Regards,
Tom Ogilvy


"is" wrote in message
oups.com...
I'm trying to add a keyboard shortcut for a function I have. I have the
following code in the "ThisWorkbook" 'module':

Option Explicit
Const DATECOL As String = "B"

Public Sub FindToday()
' Keyboard Shortcut: Ctrl+T
Dim r As Range

On Error Resume Next
Set r = ThisWorkbook.Worksheets(1).Range(DATECOL & ":" &
DATECOL).Find(what:=Date, LookIn:=xlValues)
If Not r Is Nothing Then
r.Select
End If
ThisWorkbook.Sheets(1).Activate
End Sub

Public Sub Workbook_Open()
Application.MacroOptions Macro:="FindToday", HasShortcutKey:=True,
ShortcutKey:="t"
FindToday

End Sub

I get:

Run-time error "1004":

Method 'MacroOptions' of object '_Application' failed
<<

I've tried all sorts of things (making the routines public, trying
Macro:=ThisWorkbook.Name & "!" & "FindToday", trying it without
assigning the shortcut key - all fail.

Any suggestions, please?!

Tom Ogilvy wrote:

As a minimum,
Move your function to a general module (insert=Module). No a sheet module
or the thisworkbook module.

--
Regards,
Tom Ogilvy



Hurray! Thanks very much guys. A bit embarrassing - I used to know this
stuff! Thanks again.
Cheers!