+ Reply to Thread
Results 1 to 5 of 5

MacroOptions problem

  1. #1
    is
    Guest

    MacroOptions problem

    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?!


  2. #2
    Bob Umlas
    Guest

    Re: MacroOptions problem

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

    "is" <[email protected]> wrote in message
    news:[email protected]...
    > 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?!
    >




  3. #3
    Bob Phillips
    Guest

    Re: MacroOptions problem

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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?!
    >




  4. #4
    Tom Ogilvy
    Guest

    Re: MacroOptions problem

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

    --
    Regards,
    Tom Ogilvy


    "is" <[email protected]> wrote in message
    news:[email protected]...
    > 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?!
    >




  5. #5
    is
    Guest

    Re: MacroOptions problem


    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!


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1