Using VBA: double-clicking a cell creates a new sheet, the cell also hyperlink to a new sheet

Question

Good, so I'm brand new. A new role in the work. I received this request:

• Double-clicking on any cell in a sheet creates a new sheet.
• The original cell, which double-clicks, now turns into a hyperlink, referring to the newly created sheet.
• The double-click function for this cell, which was originally double-clicked, has been removed.

I'm honestly stuck. I have not programmed using VBA yet. I figured out how to create a new sheet with a double click, and I figured out how to hyperlink. But I can’t understand how the hyperlink to the newly created worksheet is and remove the double-click function from this source cell!

Here is all that I have ...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     Sheets.Add
End Sub

Also, forgive me if I break any rules here, this is my first post here. Thank you so much for your help!

Answer 1

You're on the right track, you just need a few more components. The key point is the declaration and use of object variables ( wsin this case)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim ws As Worksheet

    ' Prevent screen flicker
    Application.ScreenUpdating = False

    ' Add worksheet
    Set ws = Me.Parent.Worksheets.Add

    ' Add Hyperlink
    Target.Hyperlinks.Add Anchor:=Target, Address:="", SubAddress:=ws.Cells(1, 1).Address(True, True, , True), TextToDisplay:=ws.Name & "!A1"

    ' Restore original sheet as active
    Me.Activate

    ' Disable original Doubleclick action
    Cancel = True

    ' Restore screen updating
    Application.ScreenUpdating = True
End Sub