Double click cell to open a specific work sheet

[sophie_san]

I have found the VBA code to solve the above mentioned problem - but it will only work for one cell per sheet:

<code class="vb keyword">Private</code> <code class="vb keyword">Sub</code> <code class="vb plain">Worksheet_BeforeDoubleClick(</code><code class="vb keyword">ByVal</code> <code class="vb plain">Target </code><code class="vb keyword">As</code> <code class="vb plain">Range, Cancel </code><code class="vb keyword">As</code> <code class="vb keyword">Boolean</code><code class="vb plain">)</code>
<code class="vb spaces"> </code><code class="vb keyword">Dim</code> <code class="vb plain">xStr </code><code class="vb keyword">As</code> <code class="vb keyword">String</code>
<code class="vb spaces"> </code><code class="vb plain">xStr = </code><code class="vb string">"Sheet name"</code>
<code class="vb spaces"> </code><code class="vb keyword">If</code> <code class="vb keyword">Not</code> <code class="vb plain">Intersect(Target, Range(</code><code class="vb string">"A1"</code><code class="vb plain">)) </code><code class="vb keyword">Is</code> <code class="vb keyword">Nothing</code> <code class="vb keyword">Then</code>
<code class="vb spaces"> </code><code class="vb plain">Sheets(xStr).Activate</code>
<code class="vb spaces"> </code><code class="vb keyword">End</code> <code class="vb keyword">If</code>
<code class="vb keyword">End</code> <code class="vb keyword">Sub</code>



Is it possible to have lots of these codes on one sheet, to have double click on A1 do this, double click on A2 do that etc.? How do I do it? Just keep copying the code above (with adjustments to sheet name and ("A1") did not work... the error message told me "Compile error: Ambiguous name detected: Worksheet_BeforeCoubleClick".

Thanks a lot!

 

[Fluff]

Hi & welcome to the board.
How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
      Sheets(Choose(Target.row, "[COLOR=#ff0000]HPTOOL[/COLOR]", "[COLOR=#ff0000]Fluff[/COLOR]", "[COLOR=#ff0000]Pcode[/COLOR]", "[COLOR=#ff0000]Pick[/COLOR]", "[COLOR=#ff0000]MA[/COLOR]")).Activate
      Cancel = True
   End If
End Sub

Change the values in red to match you sheet names.
This will work for cells A1 to A5

 

[sophie_san]

Wow, that was fast! Thanks a lot - it did the trick!

 

[Fluff]

Glad to help & thanks for the feedback