Solved

How Do I Reference ActiveWorkbook From VBA Add-In

Posted on 2011-03-11
11
396 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:ronaldj
11 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35114546
Instead of ActiveWorkbook try ThisWorkbook

Sid
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35114693
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35116783
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:ronaldj
ID: 35117853
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35119919
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
 

Author Comment

by:ronaldj
ID: 35132032
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35132123
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
 

Author Comment

by:ronaldj
ID: 35138127
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
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 35138716
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
 

Author Closing Comment

by:ronaldj
ID: 35138798
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question