How Do I Reference ActiveWorkbook From VBA Add-In

I have an Excel Workbook "MenuUpdateTest.xls". On Workbook Open(), code is triggered which adds new item to standard command bar. First item under menu correctly returns the current full path of the ActiveWorkbook (C:\CodeTest\Excel_Workbooks\MenuUpdateTest.xls. So far so good. Save as an add-in (.xla). Close everything. Open random workbook (Q:\FredOrderSheet.xls). Tick off add-in and menu is created. However, the item on menu bar returns the path of the add-in (C:\users\rjusa\AppData\Roaming\Microsoft\AddIns\MenuUpdateTest.xla). NOT what I expected! ! How do I reference the "Fred" (thought active?) workbook??? Confused. Thanks, Ron.
Sub CreateSSMenu_Wcodestyle()

Dim cbpop As CommandBarControl, cbctl As CommandBarControl, cbsub As CommandBarControl
Call RemoveSSMenu_Wcodestyle
Set cbpop = CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup, Temporary:=True)
With cbpop
 .Caption = "MenuTest"
 .Visible = True
End With
    actvWb = ActiveWorkbook.Name
    cp = ActiveWorkbook.Path
    currAppFullPath = ActiveWorkbook.FullName
Set cbsub = cbpop.Controls.Add(Type:=msoControlPopup)
With cbsub
  .Enabled = False
  .BeginGroup = False
  .Caption = "" & currAppFullPath & ""
End With

End Sub

Open in new window

ronaldjAsked:
Who is Participating?
 
Chris BottomleySoftware Quality Lead EngineerCommented:
All i can say is jolly well done in doing the majority of the work.  I am pleased that you found my comments of some use but that in no way detracts from your own excellence!

Chris
0
 
SiddharthRoutCommented:
Instead of ActiveWorkbook try ThisWorkbook

Sid
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
The button is being assigned at run time ... to reflect the activeworkbook you need to add an event to detect for example focus chane and then in that event you have re-run the button assignment so it then reflects the active document of the moment.

Each time you change workbooks this needs to occur.  In this way the button reflects the 'live' workbook because the button settings otherwise only reflect the document active at the time the static act of assignment was made.

Chris
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
zorvek (Kevin Jones)ConsultantCommented:
When your add-in is being loaded it is active. You need to delay processing and give Excel time to settle and put your add-in in the background. I use Application.OnTime to do this.

   Public Sub SomeEventHandler()
       Do Some Stuff
       Application.OnTime Now, "PostSomeEventHandler"
   End Sub

   Public Sub PostSomeEventHandler()
       Do Some More Stuff
   End Sub

Kevin
0
 
ronaldjAuthor Commented:
Re: SiddharthRout:

Tried "ThisWorkbook" in second attempt before coming to forum. No luck. MS offers the following:

ThisWorkbook Property

The ThisWorkbook property returns the workbook where the Visual Basic code is running. In most cases, this is the same as the active workbook. However, if the Visual Basic code is part of an add-in, the ThisWorkbook property won't return the active workbook. In this case, the active workbook is the workbook calling the add-in, whereas the ThisWorkbook property returns the add-in workbook.

If you'll be creating an add-in from your Visual Basic code, you should use the ThisWorkbook property to qualify any statement that must be run on the workbook you compile into the add-in.

0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
As I stated and based on my own word add-in I developed recently.

The button operations are based on the document/workbook from which the code is run.  To make it work in the context then each time the active workbook changes you need to run the code using activeworkbook.  i.e. open/close or actvate/deactivate.  Each time the focus changes execute the code in the add-in using activeworkbook and a button press on the menu will work correctly.

You do need to ensure of course that you do not recursivly create a new menu each time... personally I delete and re-create the menu each time the focus shifts and it's as good as transparent to me as the user.

Chris
0
 
ronaldjAuthor Commented:
Re: chris_bottomley:
Chris, sorry for delay in response but on the tail end of walking pneumonia and still a bit fuzzy.
As a direct result of your first comment, I re-wrote orininal sub and created new which is called to attain focus. Ergo, I am no longer returned the path of the add-in BUT rather what's open. So far so good!  However, if more than one Word doc or XL Workbook open, current code will fail. The array I create does hold all visible so Ubound does correctly test >0; I can't delete existing menu since that is where the code running from. . With test >0 the "trigger" to get focus is the activeapp and this works from the xls version. One thought is change cbsub to cbsub1 and re-write the caption. I don't know how I would properly reference this and incorporate or if it would work. Getting little frustrated, any ideas would be most welcome. Thanks.
Sub CreateSSMenu_Wcodestyle()
Dim cbpop As CommandBarControl, cbctl As CommandBarControl, cbsub As CommandBarControl
Call RemoveSSMenu_Wcodestyle
Call WbOpen
Set cbpop = CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup, Temporary:=True)
With cbpop
 .Caption = "MenuTest"
 .Visible = True
 .OnAction = "WbOpen"
End With
Set cbsub = cbpop.Controls.Add(Type:=msoControlPopup)
With cbsub
  .Enabled = False
  .BeginGroup = False
  .Caption = "" & currAppFullPath & ""
End With
End Sub

Sub WbOpen()
Dim a As Integer
Dim wkb As Workbook
a = 0
For Each wkb In Workbooks
    If Windows(wkb.Name).Visible Then
       ReDim Preserve WbsOpenArray(a)
       WbsOpenArray(a) = wkb.Name
       a = a + 1
    End If
Next
If UBound(WbsOpenArray) = 0 Then
   ThisWorkbook.Activate
currAppFullPath = "" & WbsOpenArray(0) & ""
End If
End Sub

Open in new window

0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
In what way does the current code fail. i.e if if it works for one workbook open then it will in theory work for all though I do wonder if the fail is due to always setting currAppFullPath to the first document in the WbsOpenArray array.

Chris

0
 
ronaldjAuthor Commented:
Re: chris_bottomley:

Well Chris, EUREKA! Pre-pneumonia, I had outlined, on paper, what the code module should accomplish and some thoughts how to get there. During illness, forgot about outline and just started throwing up (no pun intended) code. Now, quasi post-pneumonia, revisited outline. Your insights, coupled with my outline, allowed me to solve my enigma late yesterday PM. In a nutshell:
in module CreateSSMenu_Wcodestyle
in line 4, commented out Call WbOpen
in line 15, commented out .Caption = "" & currAppFullPath & ""
in module WbOpen
lines 33, 34 were replaced with the following:
Application.CommandBars("Worksheet Menu Bar").Controls("MenuTest").Controls(1).Caption = "" & currAppFullPath & ""
Exit Sub
End If

If UBound(WbsOpenArray) > 0 Then
currAppFullPath = ActiveWorkbook.FullName
Application.CommandBars("Worksheet Menu Bar").Controls("MenuTest").Controls(1).Caption = "" & currAppFullPath & ""
End If
End Sub

I opened 8 different workbooks and toggeled back and forth in random order. Clicking MenuTest returned the desired result. Gee, this wasn't hard! I can explain later why I used the dynamic array approach, has bearing on later events. While you didn't provide code, definitely stimulated the thought process. thank you. Please respond so I can award points. Ron
0
 
ronaldjAuthor Commented:
The concept to solve my problem was spot on. While code suggestions were not provided, the logic supplied, after some thinking,  allowed me to write the correct code. Well done.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.