Dynamically Add Macro To Each Sheet ?

[Logit]

How can I add (dynamically) a macro to each new worksheet created ?

This is the macro to be added :

Private Sub Worksheet_Deactivate()
    Sheets("Dynamically Created Name").Visible = False
End Sub

This is the macro that dynamically creates the new worksheets:

Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long

l = 1

    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
    

    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name Then
            l = l + 1
                With wSheet
                    .Range("A1").Name = "Start_" & wSheet.Index
                    .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
                    SubAddress:="Index", TextToDisplay:="Back to Index"
                    .Cells(2, 1) = "NAME"
                    .Cells(2, 1).Font.Bold = True
                    .Cells(2, 1).ColumnWidth = 25
                    .Cells(2, 2) = "AMT DUE"
                    .Cells(2, 2).Font.Bold = True
                    .Cells(2, 2).ColumnWidth = 25
                    .Cells(2, 3) = "COMMENTS"
                    .Cells(2, 3).Font.Bold = True
                    .Cells(2, 3).ColumnWidth = 60
                    
                    '>>>>>>   I believe the added code should go here   <<<<<<<<
                    
                End With

                Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
    Next wSheet
Sheets("Index").Activate
Sheets("Index").Range("A1").Select
End Sub

The macro will need to 'pick up' the dynamically created worksheet name for insertion into

Sheets("Dynamically Created Name").Visible = False

Thank you for your assistance.

[Logit]

Here is some code located on Chip Pearson's website. Trying to make it work:

Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long

'////////////////////////////////////////// New Code Follows Below
'DIM statements for adding macro to each sheet begins here
Dim VBComp As VBIDE.VBComponent  '<----- User Defined Type error message received.
Dim CodeMod As VBIDE.CodeModule '<----- User Defined Type error message received.
Dim S As String
Dim LineNum As Long
'////////////////////////////////////////// New Code Above

l = 1

    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
    

    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name Then
            l = l + 1
                With wSheet
                    .Range("A1").Name = "Start_" & wSheet.Index
                    .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
                    SubAddress:="Index", TextToDisplay:="Back to Index"
                    .Cells(2, 1) = "NAME"
                    .Cells(2, 1).Font.Bold = True
                    .Cells(2, 1).ColumnWidth = 25
                    .Cells(2, 2) = "AMT DUE"
                    .Cells(2, 2).Font.Bold = True
                    .Cells(2, 2).ColumnWidth = 25
                    .Cells(2, 3) = "COMMENTS"
                    .Cells(2, 3).Font.Bold = True
                    .Cells(2, 3).ColumnWidth = 60
                    
                    '>>>>>>   I believe the added code should go here   <<<<<<<<
                    '>>>   New Macro Code Follows
                    Set CodeMod = VBComp.CodeModule
                    LineNum = CodeMod.CountOfLines + 1
                    S = "Private Sub Worksheet_Deactivate()" & vbCrLf & _
                        "Sheets("" & ActiveSheet.Name & "").Visible = False" & vbCrLf & _
                        "End Sub"
                    CodeMod.InsertLines LineNum, S
                    '>>>> New Macro Code Above
                    
                End With

                Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
    Next wSheet
Sheets("Index").Activate
Sheets("Index").Range("A1").Select
End Sub

[Winon]

Hi Logit,

Man, you are difficult after all!

Have you checked here?: www.cpearson.com

Kind Regards.

Edit: Sorry, I see you did!

[Logit]

Yep, that's where I got the code I'm experimenting with. Any suggestions how to make it work Winon ?

And how are the gale winds in S. Africa ? Saw it on the news this morning.

[Winon]

Just an idea, why don't you create a template and make it very hidden. Then Create your Sheets from there, it should copy your VBA Code into each newly created Sheet, and nobody will be any wiser!

[Winon]

Nah, I get the same error messages - very frustrating.

No gale winds yet, where I live.

[Kyle123]

Why not just use the workbook level sheet deactivate?

[Logit]

Kyle123 ????

I don't understand.

[Logit]

Ok ... I am struggling with both of these versions:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    While ActiveSheet.Visible <> "Index"
     ActiveSheet.Visible = xlSheetHidden
    Wend
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim ws As Worksheet
Dim shtName As String
shtxName = Sheets("Index")

    For Each ws In Application.ActiveWorkbook.Worksheets
        If sht.Name <> shtxName Then
            ws.Visible = xlSheetHidden
        End If
    Next
End Sub

The goal is to keep worksheet INDEX visible at all times. Another worksheet is made visible from a hyperlink on INDEX (could be one of many). When this other worksheet loses focus via a hyperlink
*back* to INDEX, the sheet losing focus should be made xlHidden.

Neither one generates an error .. which is good. However, neither one will allow any sheet except INDEX to be VISIBLE.

What am I doing wrong ?

[xlnitwit]

Perhaps this

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
        If LCase$(sh.Name) <> "index" Then sh.Visible = xlSheetHidden
End Sub

[Logit]

No go xlnitwit. But I do thank you for your interest.

????

[Logit]

Here's a small example workbook.

Understand there will be a lot more sheets than what is in this sample.

[xlnitwit]

What does "no go" mean? What happens, or doesn't, versus the desired behaviour?

[Logit]

When clicking on the hyperlink, located in A1 of the worksheet (anyone except INDEX), the goal is to have that sheet change to xlHidden.

Only INDEX sheet and one other (user selected by hyperlink) should be visible at any given time when viewing one of the sheets in the INDEX List.
If no sheets are being viewed, then only INDEX should be seen.

I hope that is understandable. Sometimes written words don't convey accurately. Sorry.

[xlnitwit]

When clicking on the hyperlink, located in A1 of the worksheet (anyone except INDEX), the goal is to have that sheet change to xlHidden.

Only INDEX sheet and one other (user selected by hyperlink) should be visible at any given time when viewing one of the sheets in the INDEX List.
If no sheets are being viewed, then only INDEX should be seen.

I hope that is understandable. Sometimes written words don't convey accurately. Sorry.

Which is exactly what the code I suggested will do. As soon as you click the link back to Index from any sheet, that sheet is hidden. Of course, that will prevent your hyperlinks from working since you cannot link to a hidden sheet.

[Logit]

** Bump **

[Winon]

Your INDEX list shows all as 1 Apr while the Tabs are in numerical sequence?

[Logit]

Are you referring to the workbook I sent you (link) ?

If so, I don't understand.

[Winon]

It happens when you Click on "Create Tabs" Button.

[Logit]

Or was that Apr 1 ....

[Winon]

Correct. I'll download it again to double check.

[Logit]

Sorry, it's late here. Rather than enter an actual date in Col. B , type in April 1 , then drag that down the column. A quirk of the macro wanting only text, not dates.

[scottiex]

maybe something like this is useful?
(faking the hyperlinks and running the macro on the selection_change fake links - so you should remove the hyperlinks if you do this)

Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ws As Worksheet
Dim shtName As String
On Error Resume Next
If Not Target Is Nothing Then
shtName = Format(Target, "mmmm d")
Sheets(shtName).Visible = True
  ThisWorkbook.FollowHyperlink Address:=ThisWorkbook.Path & "\" & ThisWorkbook.Name, SubAddress:="'" & shtName & "'!A1"
End If
    For Each ws In Application.ActiveWorkbook.Worksheets
        If ws.Name <> "Index" And ws.Name <> shtName Then
            ws.Visible = xlSheetHidden
        End If
    Next
End Sub

[Logit]

scottiex

Thank you for your response. Its curious that your macro works on the sample file previously attached but it doesn't on the actual full workbook it is intended for.

I've looked at the code in both and the way the hyperlinks are created and cannot see a difference.

Confused

[scottiex]

Can't test from home - but it seems you changed the format of the months
I am looking for a date to reformat to "mmmm d" format
but you have "mmm d" format now.
ie the line

 shtName = Format(Target, "mmmm d")

You would at least need to change that to match one way or the other.

Maybe get the macro to format the sheet just to be safe?

[Logit]

Ok .. this is not working as I hoped. I apologize for the confusion.


Let's take a completely different approach. I've tested this in the final workbook to verify it works. It does.

Although I've searched the Net for an answer how to do this, I can't find anything that actually works.

I want to paste the following macro code into all sheets except the INDEX sheet. Can anyone assist ?

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Worksheets("Index").Select
    Target.Parent.Worksheet.Visible = False
End Sub

[xlnitwit]

Use the Workbook level equivalent event and test the sheet name. But that will leave you in the same situation- namely that all the other sheets are hidden so the hyperlinks won't work.

[Logit]

Thanks xlnitwit.

If I manually copy and paste the macro (Post #26) into each Sheet level module, the hyperlinks work and the sheets hide when returning to INDEX.

Rather than do this manually, if there is code that will paste the macro dynamically into all sheets, it will make it easier for the user.

Some websites talk about using a text file and somehow copy / paste that into the sheet module. Other sites talk about creating a .bas file as an
add-in.

Too confusing ---

Is there a method to simply paste the macro into the VBE sheet module ?

(see attached)

[xlnitwit]

It will work, but only once for each sheet. Thereafter, the hyperlinks on the index sheet will not work because the target sheets are hidden. In any event, the code I posted previously does the same thing and you need only paste it once into the ThisWorkbook module. It will run for all sheets in the workbook, including any new sheets, other than the Index sheet.

[Logit]

?????

Works as many times as needed here.

[xlnitwit]

I didn't realise you already had code in the Index sheet to reshow the other sheets. But now I am confused. If you simply remove the code from each of the other sheets and add my code from post 10 to the ThisWorkbook module, it appears to solve your problem entirely, so I'm not sure what "no go" meant?

[Logit]

Regretfully, it doesn't work here for some reason.

I can tell it is going through the 'motions' of executing but the sheet never appears. Not even a flicker.

[xlnitwit]

So this doesn't work for you?

[Logit]

Got it figured out !

The hyperlinks COL A needed to be formatted as d-mmm

That wasn't done previously which was causing the error.

Woo hoo !!! Got it done !!!

Thanks xlnitwit. My hat off to you sir !

[Zer0Cool]

After reading it looks like what you are trying to do is keep the "Index" sheet visible at all times and having all other sheets hidden until a link to one of them is clicked on the "Index" sheet, at which point you want to show the desired sheet and switch to it. If I am correct the below will work.

I didnt see anyone else state it but your original request to duplicate/automatically generate code is a bad idea and not required for what you are trying to do. Its possible but overly complicated.

Attached is your sample with code in the "Index" sheet to do exactly what I think you are looking to accomplish. I will also paste the code below. I used the Worksheet_Activate event of the "Index" sheet and the Worksheet_FollowHyperlink event of the "Index" sheet.

This acts so that when the "Index" sheet is the active sheet, all others are hidden. When a hyperlink on the "Index" sheet is clicked, that sheet is displayed and becomes the active sheet, leaving the "Index" sheet and the current sheet active. When going back to the "Index" sheet all other sheets are re-hidden.

Option Explicit

Private Sub Worksheet_Activate()

'******DECLARATIONS***************************************************
Dim wbThis As Workbook
Dim wsSht As Worksheet
Dim strIndex As String

'Error Handler
On Error GoTo Handler

'******PREPERATION****************************************************
Application.ScreenUpdating = False

Set wbThis = ThisWorkbook

'Get name of active sheet
strIndex = wbThis.ActiveSheet.Name

'******CODE BODY******************************************************
'Loop sheets and hide any that are not named after the active sheet
For Each wsSht In wbThis.Worksheets
    If wsSht.Name <> strIndex Then
        wsSht.Visible = xlSheetHidden
    End If
Next

'******EXIT CODE******************************************************
Exit_Sub:
    Application.ScreenUpdating = True
    Exit Sub

'******ERROR HANDLER**************************************************
Handler:
    MsgBox "Error" & Err & ": " & Error(Err) & "."
    GoTo Exit_Sub
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

'******DECLARATIONS***************************************************
Dim wbThis As Workbook
Dim wsSht As Worksheet

'Error Handler
On Error GoTo Handler

'******PREPERATION****************************************************

Set wbThis = ThisWorkbook

'******CODE BODY******************************************************
'Set sheet visible according to hyperlink and activate to switch to it
wbThis.Worksheets(Target.Name).Visible = True
wbThis.Worksheets(Target.Name).Activate

'******EXIT CODE******************************************************
Exit_Sub:
    Exit Sub

'******ERROR HANDLER**************************************************
Handler:
    MsgBox "Error" & Err & ": " & Error(Err) & "."
    GoTo Exit_Sub
End Sub

Hope this helps

[Logit]

Thanks Zer0Cool

Really good code. I'll add this to what I've acquired.

Cheers !

[Winon]

Hi Logit,

So glad you got your issue resolved.

Thank you to all the Contributors who have helped out here, while I was catching up on very needed sleep.

Please don't bother us again Mr Logit.

You may however keep on awarding me some Rep. points, thank you, my friend.

Regards.

[Logit]

May the gale winds of March transport you to locations of your dreams !