VBA - Create a new sheet + hyperlink name

[excelnewbie990]

I have a workbook with a template sheet and a mainsheet.
Is there a way that when I double click on cell in the mainsheet it:

1. creates a new sheet from the template I have
2. displays the value (number) in the hyperlink that would be entered in the new sheet in cell C15 for example?
3. Would update the main sheet if the value in C15 changes(new sheet)

From looking at various sources online I have managed creating a new sheet from the template, however the text that's entered as the hyperlink always seems to be the value from the template sheet and not the new sheet created.
Would appreciate any guidance on how to achieve this?

This is my current code

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

    ' Check if the double-clicked cell is in column C
    If Target.Column = 3 Then
        ' Set reference to the template sheet
        Set templateSheet = Me.Parent.Worksheets("template") ' template sheet name

        ' Copy the template sheet
        templateSheet.Copy After:=Me.Parent.Worksheets(Me.Parent.Worksheets.Count)

        ' Set reference to the new sheet which is a copy of the template
        Set ws = Me.Parent.Worksheets(Me.Parent.Worksheets.Count)

        ' Get the user-entered text from cell C15 of the new sheet
        userText = ws.Range("C15").Value

        ' Add Hyperlink
        Target.Hyperlinks.Add Anchor:=Target, Address:="", SubAddress:=ws.Cells(1, 1).Address(True, True, , True), TextToDisplay:=userText
        ' Set font size of the hyperlink
        Target.Font.Size = 10
        Target.Font.Color = 1
        Target.Font.Bold = 1

        ' Set the value of the double-clicked cell to the user-entered text
        Target.Value = userText

        ' Disable original Doubleclick action
        Cancel = True
    End If
End Sub

 

[Sektor]

I guess (not looked thoroughly) that the problem is that Targetrefers to new sheet rather main sheet. Also, as a note, for creating workbooks from templates, I'd do the proper way: create XLTX file and call Worbooks.Add(path_to_template_file).

 

[excelnewbie990]

I guess (not looked thoroughly) that the problem is that Targetrefers to new sheet rather main sheet. Also, as a note, for creating workbooks from templates, I'd do the proper way: create XLTX file and call Worbooks.Add(path_to_template_file).

I appreciate your response but can you clarify? Why should target refer to the main sheet when I would want the value in the new sheet to be written in the cell in the main sheet?

 

[excelnewbie990]

I have a workbook with a template sheet and a mainsheet.
Is there a way that when I double click on cell in the mainsheet it:

1. creates a new sheet from the template I have
2. displays the value (number) in the hyperlink that would be entered in the new sheet in cell C15 for example?
3. Would update the main sheet if the value in C15 changes(new sheet)

From looking at various sources online I have managed creating a new sheet from the template, however the text that's entered as the hyperlink always seems to be the value from the template sheet and not the new sheet created.
Would appreciate any guidance on how to achieve this?

This is my current code

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

    ' Check if the double-clicked cell is in column C
    If Target.Column = 3 Then
        ' Set reference to the template sheet
        Set templateSheet = Me.Parent.Worksheets("template") ' template sheet name

        ' Copy the template sheet
        templateSheet.Copy After:=Me.Parent.Worksheets(Me.Parent.Worksheets.Count)

        ' Set reference to the new sheet which is a copy of the template
        Set ws = Me.Parent.Worksheets(Me.Parent.Worksheets.Count)

        ' Get the user-entered text from cell C15 of the new sheet
        userText = ws.Range("C15").Value

        ' Add Hyperlink
        Target.Hyperlinks.Add Anchor:=Target, Address:="", SubAddress:=ws.Cells(1, 1).Address(True, True, , True), TextToDisplay:=userText
        ' Set font size of the hyperlink
        Target.Font.Size = 10
        Target.Font.Color = 1
        Target.Font.Bold = 1

        ' Set the value of the double-clicked cell to the user-entered text
        Target.Value = userText

        ' Disable original Doubleclick action
        Cancel = True
    End If
End Sub

edit: I attached an example file Example3
So in this case for example say you double clicked on cell C3, It would then open a new sheet and you fill out the table, then the value of the total in cell C10 would be shown in cell C3 in the main sheet. (Hope i'm making sense)