Is there a default location where Excel looks for addins? I created an addin, the macros work, and the structure is in place, but when I try to use my drop-down menu item, I get an error saying that the macro can't be found. When I created the addin, I let it save to the default location, but this is apparently not where Excel is looking for it. Thanks for the help.
Default Addin Location?
-
-
-
Re: Default Addin Location?
Use Tools > Addins & Browse to find the addin.
I don't understand though, if the menu is present, the addin should be open.
-
Re: Default Addin Location?
That's exactly what I thought. The menu item appears when I check the addin in Tools|addins and disappears when I uncheck it. When I try to select the menu item, it says the macro can't be found. I'm new to VBA, and I'd hate to think I went through this whole learning process, only to hit a wall here. I am at a loss as far as what to do next.
-
Re: Default Addin Location?
can you see the addin in the Visual Basic Editor?
I don't think there is a problem with the addin's location, you must have an error in he addin. Is the menu generated by code?
-
Re: Default Addin Location?
No (not the .xla). Should I be able to see it? I still have the .xls, but the addin (.xla) should be off in the background somewhere, without the .xls being open, correct?
-
-
Re: Default Addin Location?
does your add in have any reference to itself?
that means does it refer to a cell within its sheets?maybe if you elaborat on how the add in works or for what purpose we'd find more clues:p
-
Re: Default Addin Location?
Yes. The menu structure is a single drop-down item (no submenus) created with VBA code (I guess it would have to be, wouldn't it?)
-
-
Re: Default Addin Location?
The addin does some simple data preprocessing (deleting blank rows, validating specific data formats, etc.) It only refers to cells in the active worksheet (at least, I hope that's what it does. I did say I was new to this, right?)
The addin has a dropo down "Data" menu item associated with it. It has no optional submenu items. It's more of a menu function.
I suppose I should figure out how to attach the code. Then you'd know better what I'm trying to do.
-
Re: Default Addin Location?
Quote from mtlayThe addin has a dropo down "Data" menu item associated with it. It has no optional submenu items. It's more of a menu function.
sorry i still can't get it,
is it possible if you post the menu code here?
(pls remember to use the code tags using the # icon)
thanks -
-
Re: Default Addin Location?
Quote from mtlayNo (not the .xla). Should I be able to see it? I still have the .xls, but the addin (.xla) should be off in the background somewhere, without the .xls being open, correct?
If you can't see the addin then it isn't installed. What .xls are you referring to?
-
Re: Default Addin Location?
I'll read the instructions and give it a shot. Thanks again for the help. Be right back.
OK, here we go (I hope). I apologize in advance if I did this wrong.
Code
Display MorePrivate Sub Workbook_AddinInstall() Call MenuCommand End Sub Private Sub Workbook_AddinUninstall() Call MenuCommand_Remove End Sub Sub MenuCommand() CommandBars("Worksheet Menu Bar").Controls("Data").Controls.Add(Type:=msoControlButton).Caption = "Reformat Vendor Data" CommandBars("Worksheet Menu Bar").Controls("Data").Controls("Reformat Vendor Data").OnAction = "ReformatVendorData" CommandBars("Worksheet Menu Bar").Controls("Data").Controls("Reformat Vendor Data").BeginGroup = True End Sub Sub MenuCommand_Remove() CommandBars("Worksheet Menu Bar").Controls("Data").Controls("Reformat Vendor Data").Delete End Sub Sub ReformatVendorData() ' ' Reformat Vendor Data Macro ' ' Keyboard Shortcut: Ctrl+a ' ' ' 'To Delete All Blank Rows ... ' ' Insert test field column, format, and load formulae Range("A2").Select Selection.EntireColumn.Insert Columns("A:A").Select Selection.NumberFormat = "General" Range("A2").Select ActiveCell.FormulaR1C1 = "=ISBLANK(RC[8])" Selection.AutoFill Destination:=Range("A2:A1000"), Type:=xlFillDefault Range("A2:A1000").Select ActiveWindow.LargeScroll Down:=-29 ActiveWindow.SmallScroll Down:=-12 Range("A2").Select ' ' Delete blank rows and scroll Dim y For y = 2 To 1000 If Application.ActiveCell = True Then ActiveCell.EntireRow.Select Selection.Delete Shift:=x1up Else: ActiveCell.Offset(1, 0).Select End If Next y Range("A2").Select ' ' Delete test column Columns("A:A").Select Selection.Delete Shift:=x1left Range("A2").Select ' ' 'To Flag Empty Revision Levels ... ' ' Insert test field column, format, and load formulae Range("M2").Select Selection.EntireColumn.Insert Columns("M:M").Select Selection.NumberFormat = "General" Range("M2").Select ActiveCell.FormulaR1C1 = "=ISNONTEXT(RC[1])" Selection.AutoFill Destination:=Range("M2:M1000"), Type:=x1FillDefault Range("M2:M1000").Select ActiveWindow.LargeScroll Down:=-29 ActiveWindow.SmallScroll Down:=-12 Range("M2").Select ' ' Determine Data Size Dim x Dim z Dim w Range("H2").Select x = 0 For z = 1 To 1000 If VarType(Application.ActiveCell) = 8 Then x = x + 1 ActiveCell.Offset(1, 0).Select End If Next z Range("M2").Select ' ' Shade Missing Rev Levels For w = 1 To x If Application.ActiveCell = False Then ActiveCell.Offset(1, 0).Select Else ActiveCell.Offset(0, 1).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With ActiveCell.Offset(1, -1).Select End If Next w ' ' Delete test column Columns("M:M").Select Selection.Delete Shift:=x1left Range("A2").Select ' ' 'To Flag Improper Vendor Codes ... ' 'Goto top of Vendor Code column, do comparison Range("G2").Select w = 0 For w = 1 To x If Len(Application.ActiveCell) <> 3 Then With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With End If ActiveCell.Offset(1, 0).Select Next w Range("A2").Select ' ' End Sub
Not the classiest code, but it does what I need. Some of the help I used came from your site, and I used "Excel VBA Macro Programming", by Richard Shepherd as a programming reference.
-
Re: Default Addin Location?
I was referring to the macro code in the .xls that I converted to an .xla.
-
-
Re: Default Addin Location?
I assume the code was to be inserted in addition to the Workbook_install and Workbook_uninstall subroutines. Anyway, that's what I did. The only difference is that I get a message box asking if I want to enable or disable macros. I still get the error message saying the macro could not be found.
By the way, I was asked earlier if I could see the .xla. It is there in the Visual Basic Editor with the following structure:
1. ReformatVendorData (Reformat Vendor Data.xla)
1a. Microsoft Excel Objects
1a1. Sheet1 (Sheet1)
1a2. This Workbook
1b. Modules
1b1. Module1 (My macro code)
1b2. Module2 (menucommand_remove)
1b3. Module3 (menucommand)But it still says it can't find the macro ...
-
-
Re: Default Addin Location?
I posted the code before lunch, but it seems it didn't stick to the wall. Here it is again.
Code
Display More' Under ReformatVendorData (Reformat Vendor Data.xla) ' In Microsoft Excel Objects|This Workbook Private Sub Workbook_AddinInstall() Call MenuCommand End Sub Private Sub Workbook_AddinUninstall() Call MenuCommand_Remove End Sub ' In Modules|Module 1 Sub ReformatVendorData() ' ' Reformat Vendor Data Macro ' ' Keyboard Shortcut: Ctrl+a ' ' ' 'To Delete All Blank Rows ... ' ' Insert test field column, format, and load formulae Range("A2").Select Selection.EntireColumn.Insert Columns("A:A").Select Selection.NumberFormat = "General" Range("A2").Select ActiveCell.FormulaR1C1 = "=ISBLANK(RC[8])" Selection.AutoFill Destination:=Range("A2:A1000"), Type:=xlFillDefault Range("A2:A1000").Select ActiveWindow.LargeScroll Down:=-29 ActiveWindow.SmallScroll Down:=-12 Range("A2").Select ' ' Delete blank rows and scroll Dim y For y = 2 To 1000 If Application.ActiveCell = True Then ActiveCell.EntireRow.Select Selection.Delete Shift:=x1up Else: ActiveCell.Offset(1, 0).Select End If Next y Range("A2").Select ' ' Delete test column Columns("A:A").Select Selection.Delete Shift:=x1left Range("A2").Select ' ' 'To Flag Empty Revision Levels ... ' ' Insert test field column, format, and load formulae Range("M2").Select Selection.EntireColumn.Insert Columns("M:M").Select Selection.NumberFormat = "General" Range("M2").Select ActiveCell.FormulaR1C1 = "=ISNONTEXT(RC[1])" Selection.AutoFill Destination:=Range("M2:M1000"), Type:=x1FillDefault Range("M2:M1000").Select ActiveWindow.LargeScroll Down:=-29 ActiveWindow.SmallScroll Down:=-12 Range("M2").Select ' ' Determine Data Size Dim x Dim z Dim w Range("H2").Select x = 0 For z = 1 To 1000 If VarType(Application.ActiveCell) = 8 Then x = x + 1 ActiveCell.Offset(1, 0).Select End If Next z Range("M2").Select ' ' Shade Missing Rev Levels For w = 1 To x If Application.ActiveCell = False Then ActiveCell.Offset(1, 0).Select Else ActiveCell.Offset(0, 1).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With ActiveCell.Offset(1, -1).Select End If Next w ' ' Delete test column Columns("M:M").Select Selection.Delete Shift:=x1left Range("A2").Select ' ' 'To Flag Improper Vendor Codes ... ' 'Goto top of Vendor Code column, do comparison Range("G2").Select w = 0 For w = 1 To x If Len(Application.ActiveCell) <> 3 Then With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With End If ActiveCell.Offset(1, 0).Select Next w Range("A2").Select ' ' End Sub ' In Modules|Module 2 Sub MenuCommand_Remove() CommandBars("Worksheet Menu Bar").Controls("Data").Controls("Reformat Vendor Data").Delete End Sub ' In Modules|Module 3 Sub MenuCommand() CommandBars("Worksheet Menu Bar").Controls("Data").Controls.Add(Type:=msoControlButton).Caption = "Reformat Vendor Data" CommandBars("Worksheet Menu Bar").Controls("Data").Controls("Reformat Vendor Data").OnAction = "ReformatVendorData" CommandBars("Worksheet Menu Bar").Controls("Data").Controls("Reformat Vendor Data").BeginGroup = True End Sub
I removed the previously suggested addin installation code, since I was getting multiple installations. I'd rather leave it as being installed when the addin is checked, and removed when it's unchecked. But why can't it find the macro ?? :confused:
-
Re: Default Addin Location?
Quote from mtlayi see you still dont have this code i suggested earlier in ThisWorkbook module:
why dont you give it a try?
-
Re: Default Addin Location?
As I said a few posts back, I was getting multiple installations of the menu item, and I still got the error message. I took it out because the menu seemed to be getting set up correctly in my original code. It looks like what I had (ugly as it was) to set up the menu actually worked (to format the menu only. Obviously, the menu item isn't pointing to the right location). I guess it just didn't get saved properly as an addin.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!