Dynamically Create a Macro Based on Cell Name

[LactoseO.D.'d]

Hello gents,

I have a macro set up right now. The user clicks a cell, and selects it from the right click drop down menu. When that happens, the macro will create a spreadsheet based on the value appearing 3 columns over from the selected cell. The macro also names the selected cell "data"+Name of cell 3 columns over to uniquely identify it.

What I want to do, is use that unique name to tie a macro to the cell that will open the created worksheet when the user double clicks on the cell (or maybe add a button next to the cell that does the same thing). I want to do this for any cell the user may run the drop-down macro on.

The challenge seems to be the dynamic reference. I could use some help/ideas.

 

[Weaver]

If your code can create a new sheet, then it might not take too much change to make it a) check to see if the sheet already exists b) create it if it doesn't c) open it.

Post your code, let's have a look at it.

 

[LactoseO.D.'d]

WKSHT is a user input public var, previously declared

Sub Makenew()
Dim Act As Range
Dim target As Range
Dim Name As String
Dim Mac As String

Set Act = ActiveCell

Application.ScreenUpdating = False
Name = Act.Offset(0, -2)
ActiveWorkbook.Names.Add Name:=("data" & Name), RefersTo:=Act
Sheets("Template").Visible = True

Sheets("Template").Copy After:=Worksheets(Worksheets.Count)
ActiveWindow.ActiveSheet.Name = Name
Sheets("Template").Visible = False
Worksheets(WKSHT).Select

ActiveCell.Select
Selection.Formula = "='" & Name & "'!B3"
ActiveCell.Offset(1, 1).Range.Select
ActiveCell.Buttons.Add().Select

Set Mac = ActiveCell.Name
'Create macro named Mac
'Macro Mac opens spreadsheet Name

Application.ScreenUpdating = True
 End Sub

 

[Weaver]

So you're naming the activecell according to the text in the cell 2 cols to the left, but with the word 'data' in front of it, creating a new copy of the template, naming it and then inserting a formula that pulls the value of cell B3 of the new sheet. Then that cell needs to become 'active' in that clicking on it again will open up the relevant worksheet?

What you could do is have a double click event handler that checks to see if the cell clicked has a name beginning with the word 'data', strip off these 4 letters if it does and then open the named sheet.

HTH

 

[Weaver]

Update:

Add this code to your 'thisworkbook' module

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    On Error GoTo noName
    n = Target.Name.Name
    If Left(n, 4) = "data" Then
        sht = Mid(n, 5, 32)
        Worksheets(sht).Activate
        Exit Sub
    End If
noName:
    Cancel = False
End Sub

I've attached it there as I'm guessing you may want to use this on more than 1 sheet.

 

[LactoseO.D.'d]

That sounds like what I am trying to do. I've never written anything like that. Anything I can look at to get started?

 

[Weaver]

I think your reply cross posted my attempt at a solution.

 

[LactoseO.D.'d]

Yeah there was a crosspost there.

Tried your code. That works great! Thank you sir.