Solved: Deleting toolbars question

[malik641]

Hey Guys,

I have a workbook with a custom toolbar that is created "on the fly" when the workbook opens. When the workbook closes, I delete the custom toolbar.

When I close the workbook and forgot to save, the Toolbar is deleted when the Message box comes up asking to save. If I press Cancel, the toolbar remains deleted. How can I prevent this?

I figured I could use the following:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Delete custom toolbars
If ThisWorkbook.Saved = False Then Exit Sub
Call RemoveToolBar("Problem Log Tools")
End Sub

Which is fine IF I press cancel...because it keeps the toolbar. But if I say "Yes" to save, it does not delete the toolbar.

So after some testing I found that when I close the workbook the _BeforeClose event is triggered first, then the _BeforeSave ONLY if I say "Yes" to the MessageBox. Is there an event after this that is triggered?


I'm not sure how to handle this...what do you guys think?

[malik641]

Ok...The Workbook_Deactivate is the last event before the workbook FULLY closes. But that still may not be the best option...because if I switch from one workbook to another (but not close it), it will delete the toolbar anyway (if I put the code in that event)........

[mvidas]

Joseph,

Why not just make your own version?Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Delete custom toolbars
If ThisWorkbook.Saved = False Then
Select Case MsgBox("Do you want to save the changes you made to '" & _
ThisWorkbook.Name & "'?", vbYesNoCancel + vbInformation, "Microsoft Excel")
Case vbYes: ThisWorkbook.Save
Case vbNo: ThisWorkbook.Saved = True
Case vbCancel: Cancel = True
End Select
End If
If Not Cancel Then Call RemoveToolBar("Problem Log Tools")
End SubMatt

[mvidas]

MyPreviousPostShouldBe = Replace(MyPreviousPost, "vbInformation", "vbExclamation")
Call MyApologies("Joseph")

[lucas]

I don't think I like where this is going.....too funny Matt
we might have to issue pocket protectors.....!

[Tommy]

Hi Joseph,

I prefer to use Workbook_Deactivate to delete my toolbars and Workbook_Activate to add them. This way the toolbar is not there for another workbook to use.(user not happy, :( means phone calls, no like "hmmm not correct workbook" answer)

[malik641]

Steve, Tommy, long time no see :hi: what's up?


Matt, thanks for the code. I know this is picky, but the only thing I don't like about it, is that it doens't have XP button styles....but like I said, it's being picky.


Tommy, the only thing I don't like about adding/removing toolbars based on Workbook_Activate/_Deactivate is the fact that I'd have to reposition it exactly where I want to every time I switch through workbooks. And with my procedures that are launched by custom toolbar buttons I always use "ThisWorkbook" to make sure things act right. And for the user being on another workbook I'll also use:

If ThisWorkbook.Name <> ActiveWorkbook.Name Then Exit Sub

As a preliminary check so nothing happens without you knowing about it.

[Prasad_Joshi]

Try this one


Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo ErrorHandler
If ThisWorkbook.ReadOnly = True Then ThisWorkbook.Saved = True
Application.CommandBars("My toolbar").delete
ErrorHandler:
Err.Clear
End Sub

Prasad

[malik641]

Hey Prasad, welcome to VBAX :hi:

Thanks for the code, but it works almost just like my original code. :think:

Here's the order of events as I see it when close is pressed:


Workbook_BeforeClose is fired
If Thisworkbook.Saved = False then (UI is displayed)
If user presses Yes Then
Workbook_BeforeSave event fires
Workbook_Deactivate event fires
ElseIf user presses No Then
Workbook_Deactivate event fires
ElseIf user presses Cancel Then
....No event as far as I've seen (I wish there was / is).
I need to know if there's an event here or not...cause I have an idea of what will work.
End If
Else
Workbook_Deactivate event fires
End If
I would like to use Global boolean values to determine whether or not to delete the toolbar. I think it would work great...but I can't figure out the right way to follow the events :think:

[malik641]

BTW, here's what I'm using for now..which works pretty well except for one situation: If a user presses close without saving, then presses cancel, doesn't select anything, then switches to another workbook...the toolbar is deleted.

Here's what's in my ThisWorkbook class module:
Option Explicit
Public blnIsClosing As Boolean

Private Sub Workbook_Open()
' Call CreateToolBar to add custom toolbars
Call CreateToolBar
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
blnIsClosing = True
End Sub

Private Sub Workbook_Deactivate()
' Delete custom toolbars
If blnIsClosing = True Then Call RemoveToolBar("Problem Log Tools")
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
blnIsClosing = False
End Sub

[johnske]

...Here's the order of events as I see it when close is pressed:


...
ElseIf user presses Cancel Then
....No event as far as I've seen (I wish there was / is).
I need to know if there's an event here or not...cause I have an idea of what will work...

I would like to use Global boolean values to determine whether or not to delete the toolbar. I think it would work great...but I can't figure out the right way to follow the events :think:The only 'Event' that happens there is what you want to happen when Cancel = True and/or, what you want to happen when Cancel = False for example... Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Cancel = True Then
Call AddToolBar("Problem Log Tools")
Else
Call RemoveToolBar("Problem Log Tools")
End If
End Sub

[malik641]

Hey John,

The only 'Event' that happens there is what you want to happen when Cancel = True and/or, what you want to happen when Cancel = False for example...I'm not sure I understand what you mean here...:dunno

But Cancel is never True unless you Call the event like:
Sub CancelTrue()
Call Workbook_BeforeClose(True)
End Sub
Without that, "Cancel True" will never be displayed. And even if I were to call the event manually, how do I know when?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Cancel = True Then
MsgBox "Cancel True"
Else
MsgBox "Cancel False"
End If
End Sub

EDIT: BTW, I don't want to delete the toolbar at all unless the workbook is closing without a doubt. So I would rather not see the toolbar deleted when I press close, then when I press cancel the toolbar reappears in a spot I don't want it in.

[johnske]

It's usually used in this sense Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Close Book?", vbYesNo) = vbNo Then
Cancel = True
Else
Call RemoveToolBar("Problem Log Tools")
End If
End Sub (The Cancel = True cancels the Workbook_BeforeClose event).

But I only ever use this form... Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call RemoveToolBar("Problem Log Tools")
End Sub

Edited to change vbYes to vbNo - Hey, it's nearly 3AM here! :)

[malik641]

I'm going to take your advice John for removing/adding the toolbar upon the Activate/Deactivate events (workbook level). Although what I would really like is to have the toolbar disabled on deactivate but deleted when the workbook fully closes...but I can't figure that one out yet.

Here's what I ended up using (it also keeps track of where the toolbar was last):

ThisWorkbook Class Module:Option Explicit
Private Sub Workbook_Deactivate()
' Calls the PreservePosition procedure to save the position of the toolbar
Call PreserveToolbarPosition("MyToolbar", tbarpos)
Call RemoveToolbar("MyToolbar")
End Sub
Private Sub Workbook_Activate()
' Readds the toolbar
If ToolBarExists("MyToolbar") = False Then Call CreateToolbar("MyToolbar", tbarpos)
End Sub

Standard Module:Option Explicit
Option Private Module
' For use of remembering where the custom toolbar was placed
Type ToolbarPosition
Top As Long
Left As Long
Position As Variant
RowIndex As Variant
End Type

Public tbarpos As ToolbarPosition

Public Sub PreserveToolbarPosition(ByVal tBarName As String, ByRef tbarpos As ToolbarPosition)
' Remembers the place of a toolbar
If ToolBarExists(tBarName) Then
With Application.CommandBars(tBarName)
tbarpos.Top = .Top
tbarpos.Left = .Left
tbarpos.Position = .Position
tbarpos.RowIndex = .RowIndex
End With
End If
End Sub

Public Sub CreateToolbar(ByVal tBarName As String, ByRef tbarpos As ToolbarPosition)
'This adds the custom toolbar with a button that will open
'the Pricing userform
Application.ScreenUpdating = False
Dim tBar As CommandBar
Dim tButton As CommandBarButton
If ToolBarExists(tBarName) Then GoTo ExitHere

'Create the New Sheet/Workbook button
'************************************************************************** ********************
Set tBar = Application.CommandBars.Add
With tBar
.Name = tBarName
If tbarpos.Top = 0 And tbarpos.Left = 0 Then
.Position = msoBarTop
Else
.Position = tbarpos.Position
.Top = tbarpos.Top
.Left = tbarpos.Left
.RowIndex = tbarpos.RowIndex
End If
.Protection = msoBarNoCustomize
.Visible = True
End With

' Add all the buttons
Call CreateToolBarButton(tBarName, "HLink", "Hyperlinks")
Call CreateToolBarButton(tBarName, "ChngStatus", "OpenForm", True)
Call CreateToolBarButton(tBarName, "OpenFldr", "OpenFolder", True)
Call CreateToolBarButton(tBarName, "Q-Change", "QChange", True)
Call CreateToolBarButton(tBarName, "OpenPDF", "OpenPDF", True)
Call CreateToolBarButton(tBarName, "PDF_EMAIL", "CreatePDF_Email", True)
Call CreateToolBarButton(tBarName, "CheckItem", "CheckItem", True)
Call CreateToolBarButton(tBarName, "ChngFileName", "ChangeFileName", True)
Call CreateToolBarButton(tBarName, "ListFiles", "ListFilesPattern", True)
Call CreateToolBarButton(tBarName, "AutoFilter", "AutoFilter", True)
Call CreateToolBarButton(tBarName, "EmailSali", "EmailSali", True)
Call CreateToolBarButton(tBarName, "VBAX", "OpenVBAExpress", True)
Call CreateToolBarButton(tBarName, "Gmail", "OpenGmail", True)
'************************************************************************** ********************
ExitHere:
Application.ScreenUpdating = True
End Sub

Public Sub RemoveToolbar(ByVal tBarName As String)
'This will delete the custom toolbar
On Error Resume Next
Application.CommandBars(tBarName).Delete
End Sub

Public Function ToolBarExists(ByVal tBarName As String) As Boolean
' Determines if a toolbar exists in the application
On Error Resume Next
Dim tBar As CommandBar
Set tBar = Application.CommandBars(tBarName)
ToolBarExists = Not tBar Is Nothing
End Function

Public Function ToolBarButtonExists(ByVal tBarName As String, ByVal tButtonName As String) As Boolean
' Determines if a toolbar button exists in the application
On Error Resume Next
Dim tButton As CommandBarButton
If ToolBarExists(tBarName) = False Then ToolBarButtonExists = False: Exit Function
Set tButton = Application.CommandBars(tBarName).Controls(tButtonName)
ToolBarButtonExists = Not tButton Is Nothing
End Function

Public Sub CreateToolBarButton(ByVal tBarName As String, ByVal tButtonName As String, _
ByVal strOnAction As String, Optional blnSeparator As Boolean = False)
' Creates toolbar buttons to a given toolbar
Dim tBar As CommandBar
Dim tButton As CommandBarButton

If ToolBarExists(tBarName) = False Then Exit Sub

Set tBar = Application.CommandBars(tBarName)
Set tButton = Application.CommandBars(tBar.Name).Controls.Add(Type:=msoControlButton)

With tButton
.Caption = tButtonName
.OnAction = strOnAction
.Style = msoButtonCaption
If blnSeparator = True Then .BeginGroup = True
End With
End Sub
If anyone knows how to tell when the workbook is definitely closing, please let me know. Otherwise thanks to all of you for all your help! :hi:

[lucas]

Steve, Tommy, long time no see :hi: what's up?
Hi Joseph,
Pretty calm here lately. How are things in sunny Fla?
FYI-Ben(my son the engineering student)is moving along in his degree program..he's taking his calc III, Diff E, and Statics right now..still doing well. Did your job situation settle down?

[malik641]

Hi Joseph,
Pretty calm here lately. How are things in sunny Fla?
FYI-Ben(my son the engineering student)is moving along in his degree program..he's taking his calc III, Diff E, and Statics right now..still doing well. Did your job situation settle down? Still sunny, still warm :cool: I love being here :)

That's great about your son. Those are tough classes. Diff E is an interesting one...for some reason I liked that class better than Calc I, II and III....but my friends don't feel that way. If you ever have any questions/problems with those (especially Statics cause I'm taking Mechanics of Materials right now), feel free to PM me...I always love a challenge!

My job situation did settle down, and it turned out for the better! They pay me more than I was making at the Pool Company, and I do a lot more different types of tasks than my last job. At this job I work with CorelDraw, Excel, Access, and AutoCAD. The place engineers/manufactures safety harness for aircrafts (and some other vehicles). I mostly work with the drawings and data entry. They also have me automating excel (and I've been playing with coreldraw VBA :)). I like my job...and what I like most is that they seem to have this attitude with me, like "Let's see what else he can do." which I love because it gives me a great chance to prove myself and my skills. My last job didn't even care I could automate excel (and some AutoCAD) and help them be better organized...at least until I was about to leave. When I showed them what I could do (cause I had a production sheet with all types of functions), they had a realization on what they missed out on.

[lucas]

Florida is such a beautiful place, I miss it.
Questions.....I have lots of them as Ben comes home each evening and wants to discuss what's going on in his classes just like I am also keeping up.....he has a lot of patience to slow down enough to catch me up so we can talk about the concepts. He's doing great though. 3.9 GPA(he got a B in an English class early on. Can't tell you how much that gripes him now). He's been invited to China for an engineering lauriate program so we are all excited for him.

Too bad jobs are the way they are here in the good old US of A. They never seem to learn until its too late. You have to look out for yourself though Joseph and hope for the best. Sounds like your doing fine now as I predicted if you recall...excellent news.

[malik641]

Wow! That's really nice for Ben! And what a GPA! Tell him to keep it up (and if he's REALLY concerned about that english course, he should be able to retake it and replace the grade...as long as it's a 1st or 2nd level course). I'm trying to have the discipline your son has...but my mind is everywhere....even though I'm taking one class right now (out of state tuition sucks), I have 40 hours of work a week, my excel consulting project, and I live on my own...so there's plenty to do with the home (thank God I have my girlfriend to help out!). I end up procrastinating until a few days before the quizzes/tests...which I end up having to cram into my head and pray for a good grade. Don't get me wrong, I think I will end up with a B...but I need to be more focused. I commend Ben on his level of focus, I wish the best for him :)
Sounds like he'll do just fine :yes

I remember when I was in those classes in Jersey I used to tell me friend about everything...and it helped me remember and understand what was going on in the courses. I gotta find a friend in FL to tell this stuff to now :think: I'm sure that would help me.


Too bad jobs are the way they are here in the good old US of A. They never seem to learn until its too late.I couldn't agree more.


You have to look out for yourself though Joseph and hope for the best. Sounds like your doing fine now as I predicted if you recall...excellent news.I am, and I do. And of course I remember, and I truly appreciate that confidence in me. It's people like you who keep me moving and progressing :)

I think I've said this before, but if I didn't...If you guys are ever in FL, PM me before you come, we should meet up. Is your son old enough to drink yet?

[lucas]

I think I've said this before, but if I didn't...If you guys are ever in FL, PM me before you come, we should meet up. Is your son old enough to drink yet?

Ben will be 22 in March....funny that most of his drinking(what little there is)is usually right here at the cabin. We have some of his friends over often to play music and have a drink or two. No Mojito's yet though. One day we will venture to Fla and look you up. His ambition is to work in the aerospace field, his major is electrical engineering.

I grew up in Orlando in the 60's.......doh. Can't believe I said that. I loved it there. I've spent some time in the caribbean...Aruba, etc. some work, some just snorkeling.....