Creating an Add-In

jhol4

Board Regular
Joined
Nov 18, 2002
Messages
71
Hi Guys

I have created a tool that I wanted to save as an add-in, however I'm not sure if it is possible. Whilst I have created Add-ins before, this one is different as it includes a number of worksheets that are either used as templates to be copied into another workbook, or used for storing information.

This is a one button tool that unhides the worksheets when the macros are running, and then hides them when the macro is finished.

So my question is, what is the best way to set this tool up (so that it can be sent to collegues for use). Can you actually save it as an add-in with the worksheets being used? Or am I better off treating it as an excel workbook and writing an auto_open & auto_close macro in everyone's Personal workbook?

All thoughts are appreciated.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi,

You'll still be able to use sheets from within an add-in just like a normal workbook. Just don't try and activate the sheets or anything as the code will fail. For instance, don't use code like this:-

ThisWorkbook.Sheets("Template").Activate
ActiveSheet.Copy

Instead, use something like:-

ThisWorkbook.Sheets("Template").Copy

There is no need to unhide sheets in order to copy them.

HTH,
Dan
 
Upvote 0
Thanks for the reply dk, however I'm not sure if this will work.

There are times where I need to get (or put) information from specific cells (range names) within the worksheets. Can I still do this without activating the window?
 
Upvote 0
Unless you actually want the user to enter values into cells then you'll be fine. There is no need to activate a cell to get its value e.g. this code will retrieve a value from a cell even if the sheet is hidden:-

MsgBox Sheets("Sheet2").Range("C3")

When coding you should rarely (perhaps never) need to activate anything as you can refer to most objects directly. This is good programming practice as it will allow your code to run substantially faster.

Regards
Dan
 
Upvote 0
Sorry to keep bringing up issues here dk, but it how do I ensure that I end up at the right reference or range name if the tool activated from a different workbook, and there will be two documents open with the same range name (the template and the copy of the template)

Thanks for all your help, much appreciated!
 
Upvote 0
No worries...

To refer to a range/sheet within the add-in precede it with ThisWorkbook e.g.

Code:
Sub ReferToMe()
Dim MyVal

MyVal = ThisWorkbook.Worksheets("Sheet Name").Range("A1")

End Sub

Similarly, to refer to the current workbook (the active one) use ActiveWorkbook. If you want to refer to multiple open workbooks then you're best off creating a variable to refer to each workbook e.g.

Code:
Sub ReferToDifferentWBs()
    Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook

    Set wb1 = Workbooks.Add
    Set wb2 = Workbooks.Add
    Set wb3 = Workbooks.Open(Filename:="H:\temp\some book.xls")


    wb1.Sheets(1).Range("A1") = "This is the workbook referred to by WB1"
    wb2.Sheets("Sheet1").Name = "Any old name"
    wb3.Sheets.Add
    wb3.ActiveSheet.Range("A1") = "Hello"

End Sub

Get the idea?

HTH
Dan
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top