Running an Add-in

[austenr]

I have created an add-in and have it in the add-in under tools, how do you run one?

[austenr]

Let me re-phrase that. I do not see it available on the tools menu. I have other add-ins that show up but this one does not.

[mvidas]

Hello again,

Did you put the file in "C:\Documents and Settings\austenr\Application Data\Microsoft\AddIns" (or the corresponding AddIns directory for you)? Once it is in there, and you go to "Tools" then "Add-Ins...", you should just be able to check off the box for the addin you just made. Otherwise, you could go to Tools / Add-Ins, then click browse to add the .xla file from elsewhere on your system.

Or are you asking how you can get the macro(s) you wrote in that addin to be added as menu options?

Matt

[austenr]

The latter....I cannot get it to show in the Tools menu. I am also having trouble making a reference to it. I am enclosing my zip file of my add-in.

[mvidas]

So you have the add-in installed, and you just want to have a menu item created when the addin is installed? And you want the "FixReport" macro to be run when this menu item is clicked?

Add the following to the ThisWorkbook of SallysFix.xla (and clear out what is in Sheet1):

Option Explicit

Private Sub Workbook_AddinInstall()
 On Error Resume Next
 Application.CommandBars("Tools").Controls("Sally's Fix").Delete
 On Error GoTo 0
 With Application.CommandBars("Tools").Controls.Add
  .Caption = "Sally's Fix"
  .Tag = "Sally's Fix"
  .OnAction = "'" & ThisWorkbook.Name & "'!ThisWorkbook.FixReport"
 End With
 MsgBox """Sally's Fix"" option added to Tools menu"
End Sub
Private Sub Workbook_AddinUninstall()
 On Error Resume Next
 Application.CommandBars("Tools").Controls("Sally's Fix").Delete
End Sub
Sub FixReport()
 Application.ScreenUpdating = False
 Range("D:F").Copy
 Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=True _
  , Transpose:=False
 Range("D:F").Clear
 Range("A1").Select
 Application.CutCopyMode = False
 Application.ScreenUpdating = True
End Sub

This way, when the add in is installed, "Sally's Fix" will be added to the Tools menu, and when it is uninstalled it will be deleted.

As far as making a reference to the file (I'm assuming you mean within VBA), It would be listed on the references menu as "VBAProject". You can change this to make it easier to figure out which VBAProject you're referring to. I'm attaching the workbook, with the code changed, and "VBAProject" changed to "SallyFix". Now in Tools/References in VBA, just check the box for "SallyFix", should be near the top, once the add in is installed.

Let me know if you have any more questions
Matt

[austenr]

How do you edit an add-in? I have tried before but was not able to open it

[mvidas]

Once the add-in is installed, you should see it in your project window in the VBE (control-R in the VBE opens the project window if it is not already opened). From there, you can edit the add-in.
As an FYI, if you change any code of an add-in, make sure you save it in visual basic! It will not give you a prompt saying "warning: sallysfix.xla has changed. do you want to save it?" or anything, and if you close excel or uninstall the addin any changes will be lost.

[austenr]

Well I got in on the tools menu and was able to edit the add-in. If I try to run it I get the following message:

The Macro "C:\WINNT\Profiles\ahr1267\Desktop\SallysFix.xla'!'SallysFix.xla"This Workbook.FixReport' cannot be found. I am attaching the updated zip file.

[mvidas]

Hmm, it's now an .xls file, and not an add-in anymore. I'm thinking you may have zipped up the wrong file, as the updated code isn't in there.

But the fact that your code says:

"C:\WINNT\Profiles\ahr1267\Desktop\SallysFix.xla'!'SallysFix.xla"This Workbook.FixReport'

looks like the OnAction line in your code must be

.OnAction = """" & ThisWorkbook.FullName & "'!'" & ThisWorkbook.Name & """This Workbook.FixReport'"

Could you either confirm what the .OnAction line is, or attach the actual addin?

[austenr]

.OnAction = "'" & ThisWorkbook.Name & "'ThisWorkbook.FixReport"

This is my on action line. I am also attaching my .xla zip. Sorry about that.

BTW how do you stop your personal.xls workbook from opening every time EXCEL opens?

[austenr]

Well I got it to add to the Tools menu. The problem is that when you click on it off the tools menu it says it cannot find it. The add-in is on my desktop where another add-in is located and it works perfectly. Here is the latest version of the add-in. I am thinking it is in the OnAction statement.

[mvidas]

OK, it definately was a problem with the OnAction line

Originally, I said to use

.OnAction = "'" & ThisWorkbook.Name & "'!ThisWorkbook.FixReport"

This is the correct line to use.

In your addin you had the following line commented out:

'.OnAction = "'" & ThisWorkbook.Name & "'ThisWorkbook.FixReport"

As that was giving you trouble. Somehow the ! got lost between here and your workbook. When I asked if you were using:

.OnAction = """" & ThisWorkbook.FullName & "'!'" & ThisWorkbook.Name & """This Workbook.FixReport'"

I only put that because that was the format your line was entering, not what should have been used. You changed your bad line (the one without the !) to that to see if it would help, and it obviously didn't.

If you change it to the first one I posted in this message (with the !), it should fix it right up.

Let me know if it doesn't, I'm not attaching the addin again, as only one line needs to be changed.

Matt

[austenr]

Well I changed it back to the original .OnAction line and get the same error message as the first one in this post. ?????

[Jacob Hilderbrand]

Try this for your OnAction

.OnAction = ThisWorkbook.Name & "!ThisWorkbook.FixReport"

[mvidas]

Very good point, Jacob, I didn't think about that. Maybe even

If ThisWorkbook.fullpath Like "* *" Then
      .OnAction = "'" & ThisWorkbook.Name & "'!ThisWorkbook.FixReport"
     Else
      .OnAction = ThisWorkbook.Name & "!ThisWorkbook.FixReport"
     End If

[austenr]

Hmmm...Still says it can not find it. I will attach an up to date zip file.

[Jacob Hilderbrand]

You have the code in ThisWorkbook and also in Sheet1. You changed the code in the Sheet1 Code Module which had no effect. Remove the code from Sheet1 and change the code in ThisWorkbook and it should work.

See attached example.

[austenr]

Thanks a lot DRJ. Never attempted one of these before. Works great..Thanks to everyone for all your input and help. You can mark this one solved....

[Jacob Hilderbrand]

You're Welcome

Take Care