PDA

View Full Version : [SOLVED] Running an Add-in



austenr
12-16-2004, 09:38 AM
I have created an add-in and have it in the add-in under tools, how do you run one?

austenr
12-16-2004, 10:57 AM
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
12-16-2004, 11:16 AM
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
12-16-2004, 11:30 AM
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
12-16-2004, 11:52 AM
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
12-16-2004, 12:14 PM
How do you edit an add-in? I have tried before but was not able to open it

mvidas
12-16-2004, 12:21 PM
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
12-16-2004, 12:51 PM
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
12-16-2004, 01:05 PM
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
12-16-2004, 01:44 PM
.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
12-16-2004, 03:31 PM
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
12-16-2004, 04:12 PM
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
12-16-2004, 04:37 PM
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
12-16-2004, 08:12 PM
Try this for your OnAction

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

mvidas
12-16-2004, 08:17 PM
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
12-16-2004, 08:22 PM
Hmmm...Still says it can not find it. I will attach an up to date zip file.

Jacob Hilderbrand
12-16-2004, 08:38 PM
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
12-16-2004, 09:24 PM
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
12-16-2004, 09:42 PM
You're Welcome

Take Care