I have several sheets within an Excel file, the first one is called 'Contents' and holds information in respect to all the other sheets within the file. I want to be able to double click on a sheet name in the list and be taken directly to that sheet

Comments

• Thanks for your answer Tinkerbell, I seem to recall doing the same thing many years ago using Visual Basics but cannot for the life of me remember how I did it. I realise I could do it via Hyperlinks but it is the VBA version I am looking for, in which case can you 'jog' my memory on how to do it please....

  • Report
  

  by Panch09 on March 17th, 2009

• There are a couple of methods I can think of but haven't thought through fully or tested so I apologise in advance for any unwanted effects!
  
  I think the easiest way is to create a control button somewhere on the 'list' sheet. So you would select the relevant cell and then click the 'jump to' control button. The VBA is dead easy for that.
  
  something like
  
  private sub swap()
  sheet_var = activecell.value
  worksheets(sheet_var).select
  end sub
  
  and then just call this sub from the commandbutton_click.
  
  There would be other ways to do it, such as on sheet selection change but you might get funny results if you do that when you reselect the list sheet. There'd be a way round it but I am feeling very lazy today sorry! ;) But if that's the preferred method give me a shout and I will knock something up.

  • Report
  

  by Tinkerbell on March 17th, 2009

• Scatch that last past it works fine
  
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  On Error GoTo error_hand
  
  sheet_var = ActiveCell.Value
  Worksheets(sheet_var).Select
  
  error_hand:
  Exit Sub
  
  End Sub
  
  But beware that if you click on a cell that has something different in there (like 'title' and no corresponding sheet) you will get an error. Of course you could either lock down cells so they are non selectable or write a quick error catch in the VB like I have done above.

  • Report
  

  by Tinkerbell on March 17th, 2009

• Thanks for your help, but I was hoping to do away with the use of buttons. I have a file I use quite regulally that has the ability to go to a sheet within the file by double clicking on the cell containing the sheet name, it was created a number of years ago and cant remember how i created it.

  • Report
  

  by Panch09 on March 17th, 2009

• try this then:
  
  Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  on error goto err_hand
  sheet_var = ActiveCell.Value
  Worksheets(sheet_var).Select
  err_hand:
  exit sub
  End Sub
  
  That should do what you want I think.
  
  Again I have included an error handle. You just need to include this in the VB code for the list worksheet.

  • Report
  

  by Tinkerbell on March 17th, 2009

Comments