macros to make macros

[Guest]

The vba code I am working with generates a file with multiple worksheets. As
the worksheets are created, names are assigned.
I would like to add some code to ThisWorkBook for the created file so that
when a specific sheet in the new workbook is activated, a toolbar appears.

I am not sure how to write a code that will write code in another file.

Any thoughts?
TIA
Papa J

 

[Dave Peterson]

Chip Pearson has some instructions on how to remove code (or add code) on the
fly.
http://www.cpearson.com/excel/vbe.htm

But I would take a different approach. Are you showing the toolbar based on the
name of the worksheet?

If yes, are they nicely named--always begin with the same string (or always
contain the same indicator)?

Maybe just modifying the code to look for that indicator would be enough:

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If LCase(Sh.Name) Like "indicator*" Then
Application.CommandBars("custom1").Visible = True
Else
Application.CommandBars("custom1").Visible = False
End If

End Sub

 

[Guest]

I like the idea. I'll give it a shot.
Thanks

Chip Pearson has some instructions on how to remove code (or add code) on the
fly.
http://www.cpearson.com/excel/vbe.htm

But I would take a different approach. Are you showing the toolbar based on the
name of the worksheet?

If yes, are they nicely named--always begin with the same string (or always
contain the same indicator)?

Maybe just modifying the code to look for that indicator would be enough:

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If LCase(Sh.Name) Like "indicator*" Then
Application.CommandBars("custom1").Visible = True
Else
Application.CommandBars("custom1").Visible = False
End If

End Sub