Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, I'm not very familar with Excel and am a bit confused with where things needs to go.

    In my test spreadsheet I have a simple sub in the 'This Workbook' object.

    Sub showPlantForm()

    frmGetPlantInfo_v3.Show

    End Sub

    I have added a button to a toolbar and assign it to this 'macro' The 'assign macro' thing keeps trying assign itself to the a fully qualify path rather than just ThisWorkbook.macroname.

    Now, I want to save the development spreadsheet as an add-in but I'm not sure where either the open form sub or the button need to live.

    Sorry for the probably rather basic question, I'm out of my normal happy environment (Access) and finding Excel a bit quirky.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You need to move your code to a normal module, the Thisworkbook module is meant for workbok event code, not for macros called by buttons.
    Check out this article on building addins
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you, still a bit stuck though.

    Right, the original xls workbook has an extra code module containing a single sub (to show my form).

    I've saved the workbook as an add-in, opened a blank spreadsheet and referenced the add-in.

    I can't see the show form 'macro'. There are no macros visible to the assign macro dialog.

    I do remember reading something the other data about custom functions in an add-in not being visible to the workbook and there was as simple fix to sort it out. I can't for the life of me remember where I read this.

    Edit: I just typed the sub name ( I hate using the word macro) into the macro name box in the macro dialog and clicking the button opens my form fine. I am baffled why I can't pick it from a list though.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I would put a button on a custom toolbar to load your form from the addin instead of a button on another workbook. The article I referred to tells you exactly how to do that.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I've read some of the article. The bit on adding an item to a menu seems to imply I need to write code (copying the example) to both add and remove a menu item. It doesn't explain from where (or when) these routines need calling.

    As per usual there is no time to do these things, so I'm finding what's required to achieve the simple task just showing a form, more than a little long winded.

    To quote the article 'The sub RemoveMenu should be called by the utilities’ closing code so the entry is removed when the utility is closed.'

    I don't understand what the 'utility' is referring to. Does is mean when the add-in is loaded or unloaded?

    I appreciate your patience on this.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It means you put a bit of code in the Thisworkbook module of the addin, for example:
    Code:
    Option Explicit
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        RemoveMenu
    End Sub
    
    Private Sub Workbook_Open()
        CreateMenu
    End Sub
    Then in a normal module you have the two subs called RemoveMenu and CreateMenu respectively:
    Code:
    Sub RemoveMenu()
    'Code to remove menu item goes here
    End Sub
    
    Sub CreateMenu()
    'Code to create menu item goes here
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorted, thank you.

    Now I just need to re-write some code. I was writing a string (an access database path) to a custom document property. This doesn't seem to work with the add-in.

    I guess I need to look at ini files right?

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You can write a string to a custom document property in an addin too, but then you have to save the addin every time. Registry is better.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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