Working with Worksheets placed in an Add-In ??? 1

[marco02]

Hi

How can I work with Worksheets placed in a Add-in ? How can I show them to the user ?

[History]
I used to have my macros in a workbook, the initilization was automatic and I hid the workbook containing the macros in the beginning. When I needed to show information to the user or manipulate the data in the worksheets I used to hide/unhide my workbook containing the 'secret' worksheets and work from there.

[Now]
For convenience I decided to transform my Workbook as Add-in (so the user don't have to launch the automatic macros himself each time) but now I can't manage to access, nor show to the user the worksheets I have in the Add-in.

How can I work with a Worksheet placed in a Add-in ?
(i need .Activate, .Visible properties etc ...)

thks alot!

 

[rmikesmith]

Marco02,

Worksheets contained within an Excel Add-In are hidden from the user. If you want them to be visible, you will need to copy them to a standard workbook, which can be done using vba code.

Hope this helps

M. smith

 

[marco02]

thanks mike,

so how do i refer to my sheets in my add-in ?

> Addins("name_addin&quot.Worksheets("name_sheet&quot.Copy

... and so on ? I have to create a new workbook, dump all my sheets in it and work from there ?? That's not very convenient is it ...

marco.

 

[rmikesmith]

Marco,

To refer to worksheets in your add-in, use something like the following:

Thisworkbook.Worksheets("SheetName")

To copy worksheet to a new workbook and manipulate do this:

Sub CopySheets()
Dim Wkb As Workbook

'Copies worksheet "Sheet1" to new workbook
ThisWorkbook.Worksheets("Sheet1").Copy
'Set a variable reference to the new workbook
Set Wkb = ActiveWorkbook
'Copies worksheet "Sheet2" to the end of the newly created workbook
ThisWorkbook.Worksheets("Sheet2").Copy After:=Wkb.Worksheets(Wkb.Worksheets.Count)
End Sub

Add-Ins are best used to provide custom worksheet functions or code procedures that manipulate other objects (e.g., workbooks, the application interface, etc.).

Reply with your results or if you have other questions.

Regards,
Mike

 

[marco02]

thks mike.
I decided to use the Application.statusbar to provide user with info and do what you told me when i need to work on worksheets.
thank again,
marco