Linking a Cell to Pull Up a Hidden Worksheet on Click

[LactoseO.D.'d]

I am creating a macro that will be available from the menu when the user right clicks on a cell in a space we'll call worksheet 1. When they select the macro, a new worksheet (worksheet 2) will populate from a hidden worksheet and name itself after the cell two columns to the right of the selected cell. Worksheet 2 has an area for computations and a value that is ultimately linked back to the original right clicked cell on worksheet 2. When the user clicks worksheet 1, or leaves worksheet 2 in general, I want worksheet 2 to hide until the user left clicks on the original cell in worksheet 1.

That sounds like a lot, but the good news I can handle most of it (I was completely green when I first started coming here, *muscle flex* look at me now). I do not know how to write a macro that will call up the generated worksheet on click for any cell. I am also not sure about what event handler I can use to hide the sheet when it is no longer selected.

 

[Gerald Higgins]

Could the user click on lots of different cells, and this would have to work for all of them ?
Or is there only ever one cell that they will click ?

 

[LactoseO.D.'d]

They should be able to assign this to any cell. So lots of cells. Only one would be something I could handle... this... baffles me and I hope its possible.

 

[LactoseO.D.'d]

I'll post some sample code tomorrow. Right now, I think it can be done, but I'm not really sure about the two parts I wrote about above.

 

[LactoseO.D.'d]

Ok here is what I have so far:

Sub Makenew()
Dim Act As Range
Dim target As Range
Dim Name As String
Set Act = ActiveCell


Name = Act.Offset(0, -2)
ActiveWorkbook.Names.Add Name:=("data" & Name), RefersTo:=Act
Sheets("WBS.CostTemp").Copy After:=Worksheets(Worksheets.Count)
ActiveWindow.ActiveSheet.Name = Name

Worksheets(wkstname).Select

ActiveCell.Select
Selection.Formula = "='" & Name & "'!B3"

If target.Address = "data" & Name Then MsgBox "I'm A1"

End Sub

wkstname is a global variable defined from an input box

Everything works except the last bit. I am trying to get the selected cell to run a macro on click. Right now, it is a msgbox, but it will eventually load the created sheet.