Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: code in worksheet not working

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have the code below in one of my worksheets. When I first put the code there it worked great. I could double click on a cell, and it took me to the sheet with that cell's name. I tried it today, and i get nothing. What am I missing?

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    On Error Resume Next
    Sheets(Target.Value).Select
    End Sub

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Remove the On Error statement and report back what happens.

    Do you get an error or does nothing happen?

    Jay

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I took out the error statement and got a run-time error '13' Type mismatch and it highlighted Sheets(Target.Value).Select

    The sheet has some merged cells, but otherwise just text.

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-11 10:07, jrnyman wrote:
    I took out the error statement and got a run-time error '13' Type mismatch and it highlighted Sheets(Target.Value).Select

    The sheet has some merged cells, but otherwise just text.
    Will the following work?

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    On Error Resume Next
    Sheets(CStr(Target.Value)).Select
    End Sub

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No luck. Same error, same line. I really don't understand why this would work yesterday, but not today. What can you do to disable code in worksheets?

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-11 10:18, jrnyman wrote:
    No luck. Same error, same line. I really don't understand why this would work yesterday, but not today. What can you do to disable code in worksheets?
    Place the following in a regular module and then run the sub
    --------------------------
    Sub test()
    Application.EnableEvents = True
    End Sub
    -------------------------

    Then, try the double click again. I am guessing that your enable event setting was set to false.

    Jay

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good guess, but no luck there either.

  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Sorry.

    I cannot duplicate the error. Possibly double click on one of the sheet tabs and copy the text. Paste to the corresponding cell on the main sheet. Other than that, I am at a loss.

    Jay


  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just an update...

    I found the error. The cells I wanted to double-click on were merged cells, and the script could not recognize what the value of the merged cells was. As soon as I unmerged them it worked.


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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