Assign Macro / Hyperlink Problem

Okay, here's a problem I have been toying with for too long now.

I have an add-in that has a sheet that other office users can insert into their own workbook. This sheet originally had SHAPES on it assigned to macros within the worksheet code. Problems arise when a person inserts two of these sheets on the same workbook. I tried renaming the VBCompnonent Name (CodeName) before inserting any new sheet but that didn't work.

Next, I swapped all my SHAPES to cell HYPERLINKS and captured the cell name and used SELECT CASE to assign its relevant macro since hyperlinks were more related to the sheetname, not codename. This still didn't solve my problem.

In both cases, unless the macro was already triggered, it would point towards the newly inserted sheetcode. I've found one solution using BEFORE_DOUBLECLICK but I would rather not have to use this as a single click is more user friendly.

Any thoughts would be greatly appreciated!!

Cheers!

I've worked it out (but feel free to give a better solution)

I now test the hyperlink subaddress and the sheet name that the hyperlink is in in the Worksheet_FollowHyperlink event. If they don't match then I change the subaddress to contain the current sheet name.

This, however, doesn't stop a 'Reference is not valid' error dialog box showing up which I believe can't be fixed with Application.DisplayAlerts = False because it happens before entering the Worksheet_FollowHyperlink event. This only shows up once for each change to the worksheet name so I guess I can live with that.

Cheers!

Just in case this thread is viewed in the future, I'll mention my final solution to my initial problem.

I've done away with hyperlinks because of the error it throws and that hyperlinks are followed not just on clicking but also on enter through a cell.

I've worked out that when reseting the VBComponent name (CodeName), I also need to change the SHAPES onaction. The followong code renames a CodeName from 'JournalImport" to "JI383944122569444" (for example) which will always be unique because it is based on time. It then resets the SHAPES onaction. I've used two replace functions because it didn't work with just one for some unknown reason (or with application.substitute).

'Change the sheets 'CodeName' to avoiding assigned macro issues if multiple sheets are inserted
NewCodeName = "JI" & Now * 10000000000#
ActiveWorkbook.VBProject.VBComponents("JournalImport").Name = NewCodeName

For j = 1 To ActiveSheet.Shapes.Count Step 1
ActiveSheet.Shapes(j).OnAction = Replace(ActiveSheet.Shapes(j).OnAction, "JournalImport", NewCodeName)
ActiveSheet.Shapes(j).OnAction = Replace(ActiveSheet.Shapes(j).OnAction, "MyAddIn.xla", _
"'" & ActiveSheet.Parent.Name & "'")
Next j

Cheers!