Custom menu (Office 2002)

This is probably a piece of cake for you, guys.

I have created 3 workbooks, each holding 1 macro hiding certain rows, and 1 macro that shows these rows again, since I don't want to print several
pages with just zeros in 5 columns. I have also added a new menu to the default menu, where the user then can either hide or show the rows. My custom menu opens and closes when the workbook opens or closes. Very neat! :)

Now to my problem. If I open two of these books, and then closes one of them, the custom menu also closes, although I still have one workbook open.

Can I avoid closing the custom menu as long as any of these workbooks are open?

Hi
Something along below approach should get You started:

Code:

---

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wbBook As Workbook
Dim wsCommandbar As CommandBar
Dim bFlag As Boolean
bFlag = False
With Application
    Set wsCommandbar = .CommandBars(1)
    For Each wbBook In .Workbooks
        If wbBook.Name = "AA.xls" Or wbBook.Name = "BB.xls" Then
            bFlag = True
            Exit For
        End If
    Next wbBook
End With
On Error Resume Next
If bFlag = False Then wsCommandbar.Controls("MyMenu").Delete
On Error GoTo 0
End Sub

---

Kind regards,
Dennis

I think the best idea for you here would be to make use of the Workbook_Activate() and Workbook_Deactivate() Events.

You will still need to create your Menus within the Workbook_Open() Event, but when you use Controls.Add() make sure that you utilize the 'Temporary' parameter, setting it = True. This way you will not have to worry about deleting the Control when you Workbook closes, for when Excel Quits, these temporary controls will not be present the next time Excel is opened (unless explicitly created again, of course).

You may also need to pick up if your Control already exists due to another Workbook having already created it. In this case, you would not call Controls.Add(), but would simply Set a variable to the Control in question so that you can set it's .Visible property as required.

Within the Workbook_Activate() and Workbook_Deactivate() Events I would set the .Visible property of this Control to True and False, respectively.

Another thought is to toggle it's .Enabled property, so that you can see where it is all the time, but grayed out and unusable.

That's about it, I hope this was clear!

-- Mike

Howdy Mike,

Good suggestion :thumb

Quote:

---

That's about it, I hope this was clear!

---

With an example it would be clear for newbies as well ;)

Kind regards,
Dennis

Dennis:

I changed the code tag from code to vba in your post above

This has now been implemented.
I'm just not sure it looks right...

Nice :thumb

Dennis

Missing some Keywords, but I know that Mark can take care of that in a jiffy. :)

From the above, it's missing:
Dim
End
With
If
On Error
Resume

And he may wish to pick up 'On Error Goto 0' as a block, for the linelabel is generally Black, but is Blue when using 'On Error Goto 0'.

Very cool site going on here... I'm most impressed...

-- Mike

(P.S.: You guys may want to shunt off this part of the discussion to an Administrative area or something...)

Ok, now back to our show...

The following should run nicely. It's designed to be placed in the 'ThisWorkbook' class module:

Code:

---

Option Explicit
Dim WithEvents m_cbButton As Office.CommandBarButton
Const m_TAG = "My Unique Tag"
 
Private Sub m_cbButton_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
' The Sub MyMacro() is where your code to Hide/Show rows would be:
Call MyMacro
End Sub
 
Private Sub Workbook_Open()
Dim cbBar As Office.CommandBar
Set cbBar = Application.CommandBars(1)
' (Note that CommandBars(1) is the "Worksheet Menu Bar".)
On Error Resume Next
' Try to find the existing CommandBarButton, if present:
Set m_cbButton = cbBar.FindControl(msoControlButton, Tag:=m_TAG)
On Error GoTo 0
If m_cbButton Is Nothing Then
    ' If we did not find the CommandBarButton, then we make it:
    Set m_cbButton = cbBar.Controls.Add(msoControlButton, Temporary:=True)
    m_cbButton.Tag = m_TAG
    m_cbButton.FaceId = 2950 ' <-- 2950 is a Smiley Face :-)
End If
End Sub
 
Private Sub Workbook_Activate()
m_cbButton.Visible = True
End Sub
 
Private Sub Workbook_Deactivate()
m_cbButton.Visible = False
End Sub[/vba] Within a standard code module, you would then need code that looks something like this:[vba]Sub MyMacro()
If ThisWorkbook Is ActiveWorkbook Then
    ' Your code goes here.
    ' Your code goes here.
    ' Your code goes here.
End If
End Sub

---

The point of this is to avoid all your workbooks from fireing every time you hit this button, for it sounds like your Macro is designed to apply to only the ActiveWorkbook.

See the attached Workbook, which you can make copies of and try running 2 or 3 of them at the same time along with Workbooks that do not have this button-related code within it. You'll see that it runs pretty smoothly...

I hope this helps! :),
Mike

[Edit: Hmm... struggling with the VB-Tags a bit! Ok, switched to Code-Tags :) You really have a great site here.]

Mike - very nice :)

In case the OP use a non-english version of Excel then this line may fail:

Code:

---

Set cbBar = Application.CommandBars("Worksheet Menu Bar")

---

Which could be replace with the indexnumber:

Code:

---

Set cbBar = Application.CommandBars(1)

---

Kind regards,
Dennis

Quote:

---

Edit: Hmm... struggling with the VB-Tags a bit! Ok, switched to Code-Tags http://vbaexpress.com/forum/images/smilies/001.gif You really have a great site here.

---

I'm so glad you like it! But that's WE HAVE A GREAT SITE!!

Let us know if we can do anything....

Hi Dreamy ;),

Yes, very nice site, thanks for making it so friendly. :)

Dennis,

Good pickup! I've now edited the Code shown above as well as the Attachment. So now it's Internationally-compatible. Cool :cool

Thanks guys :thumb,
Mike

Indeed.

I hope that...six months from now when we're daily hammered with questions...we all still take the time to welcome new members.

Probably 90+% of the current membership are all people who are more likely to answer questions than to ask. I suspect that six months from now, that percentage will have reversed itself.

:D

Let's hope the site doesn't go...erm...tits up!
(Actually, we used to say that about the airplanes when they went hard down when I was in the Navy working on P3s over there in Rota, Spain. I thought it was a military expression and hadn't heard it in 25 years...)

A few observations:

1. Dennis solution involves hardcoding of the Workbooks>>?? :dunno

2. Mike

Code:

---

"Dim WithEvents m_cbButton As Office.CommandBarButton"

---

will fail miserably for users of older versions: ie 97 :D

3.if the user resets the toolbars after Workbook is opened you will get errors with :

Code:

---

Private Sub Workbook_Activate()
    m_cbButton.Visible = True
End Sub
 
Private Sub Workbook_Deactivate()
    m_cbButton.Visible = False
End Sub

---

(I only say as from experience!).

4. Mike with your code only the creating Workbook can run the code:

Code:

---

If ThisWorkbook Is ActiveWorkbook Then

---

(all subsequent books will fail?)

5. Personally I would make an Addin for the control and check for it's existence in the Workbook_Activate() / Workbook_Deactivate() events,
I would place all the code in the addin to do enable, delete etc.

6. If the user also delete your custom control or move it to another toolbar you can check that with addin code.

Maybe am being a bit pedantic, please feel free to shoot me down in flames:bug:

Dang! That's the icon I forgot! The machine gun, LOL!!

Insomniac,

Why should anyone shoot You down? We don't do that on this board :thumb
(Remember that You're not at ...... now :D )

Anyway, yes they are hardcoded as I didn?t write a generic solution.
(Is that a requirement for posting?)

Feel free to write a generic solutions where the number of workbooks to be checked are changed as well as their names and post it in this thread :)

BTW, welcome to the board ;)
Dennis

Well no one will ever say your not quick with response Anne :love

Quote:

---

Anyway, yes they are hardcoded as I didn?t write a generic solution.
(Is that a requirement for posting?)

---

Just a friendly observation Dennis, :yay :thumb :p :boing

Or you, Insomniac!

:kiss

I always assume that all post are friendly :jack here :)

Anyway, how would You go on to write an add-in for it :bug:

(This it the price You pay when You make friendly observations )
:D

Kind regards,
Dennis

Ha,ha, are you biting Anne :blush

Dennis, I would maybe use Application level event trapping & check for a specific sheet & value in a cell, or just rely on each Workbook Activate event to activate the addin. See what OP requires 1st :D (damn, too many experts here, LOL)

Hi Insomnic :hi

LOL, there are too many Experts! Until a few minutes ago, I thought that I had some pretty good code, Doh! :dunno

Ok, I'll address these points one by one, but it does sound like that to handle this for XL'97 that Application Trapping would be required, as you noted.

(1) "Dim WithEvents m_cbButton As Office.CommandBarButton" will fail miserably for users of older versions: ie 97.

I was blissfully unaware of this! I don't know how you XL'97 get around in life... LOL. Hmm... this seems to necessitate Application Event Trapping. Interesting that XL'97 supports Application Events but not not CommandBar Events, eh?

(2) If the user resets the toolbars after Workbook is opened you will get errors with m_cbButton.Visible = True, etc...

This happens if you edit your code within the Project, forcing your project to "Lose State". Once your code is finalized, I would expect that this would not occur. But since trapping Application Events would completely separate the "Event Trapping" from the Code within the Workbook in question, it would again seem to be a better solution. Two points for the sleepless one...


(3) Mike with your code only the creating Workbook can run the code. If ThisWorkbook Is ActiveWorkbook Then...

This part was 100% intentional. It seemed from the question that the OP intends for his code to fire for the ActiveWorkbook only. With possibly multiple Workbooks all hooking to this Event, we need to see which Workbook is Active and only fire for this one. But yes, yet again, this would be easier for Application Events, where we can simply Trap the Application_WorkbookActivate() Event. Three points... Hmm...

5. Personally I would make an Addin for the control and check for it's existence in the Workbook_Activate() / Workbook_Deactivate() events,
I would place all the code in the addin to do enable, delete etc.

I stayed away from this for one reason and one reason only, that this involves an installation issue to some degree. With the current solution, you could email this Workbook to another User and they could open it like any other Workbook and it will run.

Using an XLA requires that the XLA be placed into the XLStart directory, or perhaps placed within the same folder as your Workbook and the Workbook_Open() event would then have to search out for the XLA, find it, and open it. Not a massive hassle, but, well, I decided to go for a more "self contained" solution.

That said, I had no idea that the code I provided would not work at all in XL'97. Soooo.... hmmmm... I guess we should make a version using Application Event Trapping, eh?

I don't have the time today, but if you want to take a crack at it, be my guest :) Otherwise, I'll take a shot at it tomorrow if no one else has provided a solution by then. Gotta run...

Have a great night all :),
Mike

Mike, thats a pretty good summary of the points I made & as you know I always allow for versions down to xl97. Maybe we overcomplicating the theme of original post but hopefully will stimulate others to consider all the variations on the theme. Me too need to review tommorrow, hopefully more input from many other experts available here.

Hi guys,

Don?t forget that the OP is only running Excel 2002 ;)

Kind regards,
Dennis

Yes Dennis thats why I said "Maybe we overcomplicating the theme of original post" wait for response I think.

I just got home from work, at 11:30 PM :( , and wednesday is conference day, but I will have a go as soon as possible.

Thanks for all the replies, I am truly overwhelmed! :hi

This may be way off in left field... but...

Not knowing the context of the OP and him using XL2002, I wonder if scenarios might be easier to implement on one sheet, or even custom views, and then have a macro assigned to buttons that activate the view/scenario?