Solved

How Do I Reference ActiveWorkbook From VBA Add-In

Posted on 2011-03-11
11
386 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now