Results 1 to 6 of 6

Creating an Add-In

This is a discussion on Creating an Add-In within the Excel Questions forums, part of the Question Forums category; Hi Guys I have created a tool that I wanted to save as an add-in, however I'm not sure if ...

  1. #1
    Board Regular
    Join Date
    Nov 2002
    Posts
    71

    Default Creating an Add-In

    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.

  2. #2
    dk
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,932

    Default Re: Creating an Add-In

    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

  3. #3
    Board Regular
    Join Date
    Nov 2002
    Posts
    71

    Default Re: Creating an Add-In

    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?

  4. #4
    dk
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,932

    Default Re: Creating an Add-In

    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

  5. #5
    Board Regular
    Join Date
    Nov 2002
    Posts
    71

    Default Re: Creating an Add-In

    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!

  6. #6
    dk
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,932

    Default Re: Creating an Add-In

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com