trouble with add-in 1

[Luis939]

im creating an Add-in, however i need to call a sub that is stored in another workbook, im running excel 2000 and im not sure i can do this with this version but ive tried something i read that says projectname.modulename.procedurename

however my modules are only numbered and it says that projectname is not the name of the file just the code name of the project which by default is vbaproject
but its not working for me, i can only access procedures from within the same workbook...any help please thanks!

 

[RobBroekhuis]

Try application.run "MyWorkbook.xls!MyMacro"
I would think long and hard, though, whether this is really what you want to do (rather than somehow bring the external code into the AddIn).


Rob

 

[Luis939]

well i just figured out that i have to 'reference' the project that i want to use, however, im not sure how i would be able to call a proc in the activeworkbook without using a name, becuase what i wanted to do was create a menu whose items run macros which would be stored in the activeworkbook....how would that be done i think vba wants a specific filename but what if my activeworkbook changes its name? thanks

 

[RobBroekhuis]

How about

application.run activeworkbook.name & "!MyMacro"



Rob

 

[Luis939]

HAHAAAAAAA YOU ARE GENIOUS!!!!!!!!!!!!!!!!!!!!!

 

[Luis939]

why would this stupid book tell me that i have to reference it, i mean that is correct, but your method was the simplest!

 

[Luis939]

Alright, unfortunatley Ive fallen into a king of loophole, i was finally able to achieve an add-in that created a custom menu (actually its a menu whose items represent the months of the year in as they appear in a calendar, since they are placed in alphabetical order by VBA) The thing was that whenever one of those items would be selected it would execute a macro from whatever workbook would be active at that time, however, when the add-in creates the menu, it takes the name of the activeworkbook, which happens to be the name of the add-in, NOT the workbook on the screen, so that menu item now points to the add-in workbook. how would i affectively make it point to whatever workbook is active at the time i select the menu item, not the add-in, thanks a mil!!!

 

[Luis939]

just to clarify, i MUST execute the add-in just once while my desired workbook is active (that is, the workbook that contains my macros), because it creates the menu jsut once. If i have it create the menu each time Excel opens, then everytime it reads activeworkbook.name it will error because the addin executes before a file is open; but there is no workbook open yet which causes the error. That is why whenever i do create the menu with my workbook active, activeworkbook.name is exactly the name of the file, but what if i open another workbook at another time? It will still be pointing to that first workbook's name, and thats precisely what im trying to avoid, thanks! it doesnt seem to work after the add-in is first executed?

 

[RobBroekhuis]

We can help, but only if we understand better what you're trying to achieve. Do you want the menu items to change every time a new workbook is opened? How do you know that this new workbook has the macros you're looking for? I think a little background about your application would help in getting the right answer. I'm still unclear why the macros need to reside in individual workbooks, rather than in the add-in itself.



Rob

 

[Luis939]

Ok ill try, basically ive created 12 macros for essentially 4 different users, the 12 macros simply represent the 12 months so whenever one is selected they simply pass a few parameters to one master macro they all point to.
now each month the macro processes different files for each month (hence the 12 menu items) now it is true that they used to use the old macro window but i wanted it so they can have an easier menu 1-becuase they seem to always forget how to summon the macro window 2-because i dont want them to keep searching for a month if its not in the order they are used to seeing it on a calendar (dont want to annoy the people who pay me lol) and 3-well heck i guess i want to show these people the different cool stuff i can do with vba-to keep my job going lol
now it is true that i can tell the add-in to run on the specific file i want it to use when the add-in is created, but next year that file name will change to report2004.xls, so thats why i wanted this addin to accomodate such a change so that it would pick the macro from whatever file is open at that given time. I would have liked to have done it so that the menu is created everytime excel opens but the addin does that before the file is active so thats how i fall into trouble, i hope im not missing anything in my logic

 

[Luis939]

plus its true i can have them import that macro into say personal.xls and the menu's simply point to personal.xls!macro, but the engineers arent too techsavy and didnt want to do anythign liek that, they dont want to interact with the vba interface at all they think they would get confused, so its my job to make everything as convenient to them as possible you know :-\

 

[Luis939]

besides i make constant changes on the macro since thigns always change wihtout warning, and it would simply annoy then heck out of them to import a macro alot, so til now ive updated that macro on the 4 seperate reports they each run everymonth

 

[RobBroekhuis]

I still say it's easier to put the macros into the AddIn itself (I agree that they should NOT go into personal.xls - that can be a nightmare for distribution). Just write the macros to check the activeworkbook (to make sure it's one that conforms to the format you want to operate on) and then do their thing. Then you can set up the menu when the AddIn is first opened (i.e., when Excel is launched). Distributing an AddIn is not difficult - I can set you up with some code to do that quite straightforwardly.


Rob

 

[Luis939]

Yea that sounds like a better idea than all that other fuss, can i take a look at that code,thanks alot Rob!

 

[Luis939]

ok rob your gonna hate me for this, but ive come across yet another problem, probably a more serious one, just for the heck of it, i did what you said and put the whole code in the add-in, now theres just one problem, i have a for-loop that scans every worksheet for the same range (myrange) and whenever i said if myrange is nothing then end
and that would work perfectly fine the old way, but now that im doing it through the add-in, thats not working, if vba doesnt find that range i will get a warning prompt saying that the data has not been found on the sheet, and i dont know what the heck to do about it, my whole macro depends on doing the work on which ever worksheet has the range, ive tried putting
on error resume next
but nothing! nothing will work, n the thing that gets me is that if i take the same code from the add-in BUT execute it through the VBA interface, then it WILL work....but how come it wont work through that stupid menu i set up! AHH!!! helppppppppppppppppppppppppp lol thanks!

 

[finanl]

Luis,

I still am not exactly certain what you are trying to do but the following code will create a custom menu for you and I would suggest, like Rob did, to put your macro within the Add-In, make it a different module within the add-in. The last bit of code starting with Private Sub Workbook_Open() should go in the This Workbook section of the VBE. I have to tried to include enough comments to follow where your own macro names should go. Hope this helps

Rich

Sub CreateMenu()
' This sub should be executed when the workbook is opened.
' NOTE: There is no error handling in this subroutine

Dim MenuSheet As Worksheet
Dim MenuObject As CommandBarPopup

Dim MenuItem As Object
Dim SubMenuItem As CommandBarButton
Dim Row As Integer
Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId

''''''''''''''''''''''''''''''''''''''''''''''''''''
' Location for menu data
Set MenuSheet = ThisWorkbook.Sheets("MenuSheet&quot
''''''''''''''''''''''''''''''''''''''''''''''''''''

' Make sure the menus aren't duplicated
Call DeleteMenu

' Initialize the row counter
Row = 2

' Add the menus, menu items and submenu items using
' data stored on MenuSheet

Do Until IsEmpty(MenuSheet.Cells(Row, 1))
With MenuSheet
MenuLevel = .Cells(Row, 1)
Caption = .Cells(Row, 2)
PositionOrMacro = .Cells(Row, 3)
Divider = .Cells(Row, 4)
FaceId = .Cells(Row, 5)
NextLevel = .Cells(Row + 1, 1)
End With

Select Case MenuLevel
Case 1 ' A Menu
' Add the top-level menu to the Worksheet CommandBar

MaxControlIndex = FindMaxControlIndex()
If PositionOrMacro > MaxControlIndex Then PositionOrMacro = MaxControlIndex

Set MenuObject = Application.CommandBars(1). _
Controls.Add(Type:=msoControlPopup, _
Before:=PositionOrMacro, _
Temporary:=True)
MenuObject.Caption = Caption

Case 2 ' A Menu Item
If NextLevel = 3 Then
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
Else
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = PositionOrMacro
End If
MenuItem.Caption = Caption
If FaceId <> &quot;&quot; Then MenuItem.FaceId = FaceId
If Divider Then MenuItem.BeginGroup = True

Case 3 ' A SubMenu Item
Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
SubMenuItem.Caption = Caption
SubMenuItem.OnAction = PositionOrMacro
If FaceId <> &quot;&quot; Then SubMenuItem.FaceId = FaceId
If Divider Then SubMenuItem.BeginGroup = True
End Select
Row = Row + 1
Loop
End Sub

Sub DeleteMenu()
' This sub should be executed when the workbook is closed
' Deletes the Menus
Dim MenuSheet As Worksheet
Dim Row As Integer
Dim Caption As String

On Error Resume Next
Set MenuSheet = ThisWorkbook.Sheets(&quot;MenuSheet&quot
Row = 2
Do Until IsEmpty(MenuSheet.Cells(Row, 1))
If MenuSheet.Cells(Row, 1) = 1 Then
Caption = MenuSheet.Cells(Row, 2)
Application.CommandBars(1).Controls(Caption).Delete
End If
Row = Row + 1
Loop
On Error GoTo 0
End Sub

Private Function FindMaxControlIndex() As Integer

FindMaxControlIndex = 0

For Each Control In Application.CommandBars(1).Controls
If Control.Index > FindMaxControlIndex Then FindMaxControlIndex = Control.Index
Next Control

FindMaxControlIndex = FindMaxControlIndex + 1

End Function

Public Sub CreateMenuSheet()

Sheets.Add Type:=&quot;Worksheet&quot;
With ActiveSheet
.Move after:=Worksheets(Worksheets.Count)
.Name = &quot;MenuSheet&quot;
.Visible = False
End With

Dim MS As Range
Set MS = Worksheets(&quot;MenuSheet&quot.Range(&quot;A1&quot

' Specify whether Menu Position: 1 - Name, 2 - Within Menu
MS.Offset(1, 0) = &quot;1&quot;
MS.Offset(2, 0) = &quot;2&quot;
MS.Offset(3, 0) = &quot;2&quot;
' Specifies actual Name of Menu
MS.Offset(1, 1) = &quot;&Utlities&quot;
'Specifies Name within Menu
MS.Offset(2, 1) = &quot;Im&port Data&quot;
MS.Offset(3, 1) = &quot;Se&lect Sheet&quot;
'Specifies Location of Menu on ToolBar
MS.Offset(1, 2) = &quot;11&quot;
'Specifies Name of macro to call when menu item is selected
MS.Offset(2, 2) = &quot;frmFilter&quot;
MS.Offset(3, 2) = &quot;ShowfrmChange&quot;
'Specifies whether a divider is wanted
MS.Offset(2, 3) = &quot;&quot;
MS.Offset(3, 3) = &quot;&quot;
'Specifies FaceId of Menu item
MS.Offset(2, 4) = &quot;266&quot;
MS.Offset(3, 4) = &quot;48&quot;

End Sub

Private Sub Workbook_Open()

On Error Resume Next
Call CreateMenuSheet

Call DeleteMenu
Call CreateMenu


End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)

On Error Resume Next

Call DeleteMenu
Call CreateMenu

End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)

On Error Resume Next

Call DeleteMenu

End Sub

 

[Luis939]

ok rob nevermind sorry but i didnt realize that the add-in IS taking the macro from the activeworkbook...i thought it pointed to the workbook that was active at the time the menu was created but it wasnt doing that, so i dont have to specify which workbook to take it from, so sorry for putting you thru this, but i still have much testing to do ill be sure to check back wiht you if anything thanks Rob, i owe ya!

 

[Luis939]

Rob do you still have that code to distribute Add-ins, i would really like to see what you have to offer, thanks!!

 

[RobBroekhuis]

Luis,
I think you already have it. You asked the question in thread 707-704280. See if that addresses it.


Rob

 

[Luis939]

wow thanks, and you said I had a long memory! lol