Templates in Add-in

  • Thread starter Thread starter Eric
  • Start date Start date
E

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
 
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
 
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
 
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
 
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
 
Eric

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

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


Gord Dibben MS Excel MVP
 
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
 
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
 
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
 
OK, thank you.


JE McGimpsey said:
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
Click to expand...
 
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.
 
Makes sense now, thank you.

Eric


Tom Ogilvy said:
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.
Click to expand...
 
Back
Top