Templates in Add-in

[Eric]

Hello,

I am learning VBA and I just created several templates/forms (sheets) that I
saved into an Add-in.
The Add-in creates automatically a menu item and I would like to be able to
call the templates (sheets) saved in the Add-in from this menu.

How can I do that? What would be the command to execute?

Thank you.

Eric

 

[Guest]

thisworkbook.Worksheets(1).Range("A1").Value

 

[Eric]

Tom,

The command does not work (doesn't do anything, not even an error) but maybe
I wasn't clear enough.

My Add-in had 2 sheets called test1 and test2. The Add-in is not opened but
just "loaded" through Tools / Add-ins ....
Once loaded the Add-in creates a menu. I'd like to be able to create
automatically a new workbook with a new sheet based on (copied from) test1
or test2 from the loaded Add-in.

Thank you again.

Eric

 

[Guest]

Sheet1.Copy
or
Sheets("Sheet1").Copy

 

[Eric]

Sorry but this does not work either. The sheet I want to copy is in the
Add-in but is not apparent..

Thank you.

Eric

 

[Guest]

Have you made the sheet in the addin hidden (which is not necessary since the
sheets in an addin are not visible by virtue of being an addin? That being
the case...

Sheet1.Visible = xlSheetVisible
Sheet1.Copy

 

[Eric]

Still does not work, and no, the sheet is visible.

How does the command know that the sheet is coming from the Add-in?

Here's what I have so far:

With cbMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "Test template"
.OnAction = Sheets("Test1").Copy
End With

Thank you.


Eric

 

[Gord Dibben]

Eric

Try this............

Workbooks("myaddin.xla").Sheets("Sheet1").Copy


Gord Dibben MS Excel MVP

 

[Guest]

Your OnAction must be a text string and not an executable action... something
like this...

With cbMenu.Controls.Add(msoControlButton, 1, , , True)
.Caption = "Test template"
.OnAction = "Module1.CopySheet1"
End With

'In module 1 place this code...

public sub CopySheet1()
sheet1.copy
end sub

 

[Eric]

Hi Jim,

It works!! My question now is why? How does it know that sheet1 is the one
in my Add-in and not the one in the current workbook for instance?

Thank you.

Eric

 

[JE McGimpsey]

That's your responsibility in writing the add-in. Make sure the
reference is fully qualified. For example:

Public Sub CopySheet1()
ThisWorkbook.Sheets("Test1").Copy
'...
End Sub

 

[Eric]

OK, thank you.

That's your responsibility in writing the add-in. Make sure the
reference is fully qualified. For example:

Public Sub CopySheet1()
ThisWorkbook.Sheets("Test1").Copy
'...
End Sub

 

[Eric]

Thank you.

Eric

Try this............

Workbooks("myaddin.xla").Sheets("Sheet1").Copy


Gord Dibben MS Excel MVP

 

[Guest]

thats is why I told you

thisworkbook.Worksheets(1)

You didn't say what you wanted to do so I didn't elaborate. But to refer to
the elements of the Addin when using code contained in the addin, use the
qualifier thisworkbook.

 

[Eric]

Makes sense now, thank you.

Eric

thats is why I told you

thisworkbook.Worksheets(1)

You didn't say what you wanted to do so I didn't elaborate. But to refer
to
the elements of the Addin when using code contained in the addin, use the
qualifier thisworkbook.