Disable Button when no workbooks are open.

[Beto]

Hi, I'd like to know how can I disable a button in my custom toolbar
when there is no workbook open. Just like, for example, the print button
is disabled when there is no workbook open. This toolbar is from an
addin I'm developing. Thanks to anyone who can answer!

Regards,

 

[Bob Phillips]

Beto,

Difficult. You could add code in each workbook close event that checks the
workbooks collection count, and when down to 1, disable the control.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JE McGimpsey]

You can put this code in your add-in (assume the control is captioned
"MyControl"):

In the ThisWorkbook Module:

Dim clsDimButton as New DimButtonClass

Private Sub Workbook_Open()
Set clsDimButton.DBApp = Application
End Sub


In a class module that you name DimButtonClass:


Public WithEvents DBApp As Application


Private Sub DBApp_WorkbookBeforeClose( _
ByVal Wb As Excel.Workbook, Cancel As Boolean)
CommandBars.FindControl("MyControl").Enabled = _
(Workbooks.Count > 1)
End Sub

Private Sub DBApp_WorkbookOpen(ByVal Wb As Excel.Workbook)
CommandBars.FindControl("MyControl").Enabled = True
End Sub

 

[JE McGimpsey]

Better to do it in the add-in...

 

[JE McGimpsey]

Actually, since the user presumably can delete the control or the
commandbar, the

CommandBars.FindControl(...

lines should be error-handled:

On Error Resume Next
CommandBars.FindControl(...
On Error GoTo 0

 

[Beto]

Thanks, I'll give that a try. Right now I'm just issuing an error when
the button is pressed, but it would be nicer if it can be disabled.

Regards,

 

[Aaron Queenan]

There is no workbook close event, only a workbook _before_ close event. If
the workbook is modified, the user will see a dialogue, and can click
cancel. This means that there is no way to disable an icon in a timely
manner based on the workbook being closed.

What you could do, is set a timer to update a few times per second, which
checks the active workbook - it will be null if none is open. If the user
clicks the button immediately after closing the last workbook, just ignore
the click - just as if it had been disabled.

Regards,
Aaron.

 

[Beto]

You can put this code in your add-in (assume the control is captioned
"MyControl"):

In the ThisWorkbook Module:

Dim clsDimButton as New DimButtonClass

Private Sub Workbook_Open()
Set clsDimButton.DBApp = Application
End Sub

In a class module that you name DimButtonClass:

Public WithEvents DBApp As Application

Private Sub DBApp_WorkbookBeforeClose( _
ByVal Wb As Excel.Workbook, Cancel As Boolean)
CommandBars.FindControl("MyControl").Enabled = _
(Workbooks.Count > 1)
End Sub

Private Sub DBApp_WorkbookOpen(ByVal Wb As Excel.Workbook)
CommandBars.FindControl("MyControl").Enabled = True
End Sub

Worked like a charm! I did have to do some tweaking though, because It
was used in a button which was inside a menu kind of control
(msoControlPopup) in a bar, so FindControl wasn't working and I did a
direct reference to the 2 buttons I was disabling.

And in the Class Module I also added the same code of DBApp_WorkbookOpen
to the DBApp_NewWorkbook event, otherwise the buttons were disabled when
you closed all the books and then started a new one.

Lot of thanks!

 

[Beto]

There is no workbook close event, only a workbook _before_ close event. If
the workbook is modified, the user will see a dialogue, and can click
cancel. This means that there is no way to disable an icon in a timely
manner based on the workbook being closed.

What you could do, is set a timer to update a few times per second, which
checks the active workbook - it will be null if none is open. If the user
clicks the button immediately after closing the last workbook, just ignore
the click - just as if it had been disabled.

Thanks for the reply, but it'll look like something is not working as it
should and I won't get the gray looking of disabled buttons (maybe I'm
being picky, but I like nice results). Actually, JE's code got the jobe
done.

Regards,

 

[Aaron Queenan]

Thanks for the reply, but it'll look like something is not working as it
should and I won't get the gray looking of disabled buttons (maybe I'm
being picky, but I like nice results). Actually, JE's code got the jobe
done.

No, the control would be disabled, so it would look the same as a disabled
button. The point of ignoring the click if the user manages to press the
button before the timer routine kicks in is so that the button always acts
disabled if no workbooks are open.

Try this with JE's code: Edit a workbook, click the close icon. You're
icon will disable. Click cancel. Your icon will be disabled even though
there is still a workbook open.

Regards,
Aaron Queenan.

 

[JE McGimpsey]

Aaron-

Did you try my solution before you posted? If so, what version of XL?

For me (XL98, XL01, XLv.X, XL03), the icon is *not* disabled before the
user is asked to save, and if the user cancels, the control is still
enabled.

 

[Aaron Queenan]

I've tested in XL 2000 and XL 2002, using a C++ COM add-in. I've tested in
XL 2002 using your VBA code. The Workbook_BeforeClose event handler is
always called _before_ the dialogue box asking the user to save. Therefore
the icon would be disabled before the user pressed the cancel button.

Even if the BeforeClose event came after the dialogue box, there would still
be another bug waiting to rear it's head. Consider the following scenario:

- A user has two add-ins installed on the computer, both which monitor the
BeforeClose event in the same way.
- One add-in sometimes sets the Cancel flag. The other add-in uses your
code.
- If the BeforeClose event is called in the other add-in first, there is no
problem.
- If the BeforeClose event is called in your add-in first, if the other
add-in sets the Cancel flag you will have already disabled the icon.

Regards,
Aaron Queenan.

 

[JE McGimpsey]

Since the OP asked about a control on a toolbar created in an add-in he
was developing, I think the likelihood of a second add-in
enabling/disablin that control is not worth considering.

 

[JE McGimpsey]

Aaron -

I owe you an apology.

I had written the class module using the App_WorkbookDeactivate() event,
but in this thread, I wrote that I was using the App_WorkbookClose()
event.

Using the Workbook_Close() or the App_WorkbookClose() events will, of
course, exhibit exactly the behavior you describe.

The App_WorkbookDeactivate event is the last event fired when closing a
workbook - it happens after the file is saved, if in fact it is saved.
If the user cancels the save, the event isn't fired at all, so the
control is never dimmed.

When I tested it, I naturally didn't see the control dimmed if I
cancelled the save, so I couldn't understand why you were seeing what
you did. Obviously I locked onto the wrong piece of information.

In any case, here's the correct code in my DimButtonClass module:

Public WithEvents DBApp As Application

Private Sub DBApp_WorkbookDeactivate(ByVal Wb As Excel.Workbook)
On Error Resume Next
CommandBars.FindControl(Tag:=csCTRLTAG).Enabled = _
(Workbooks.Count > 1)
On Error GoTo 0
End Sub

Private Sub DBApp_WorkbookOpen(ByVal Wb As Excel.Workbook)
On Error Resume Next
CommandBars.FindControl(Tag:=csCTRLTAG).Enabled = True
On Error GoTo 0
End Sub

Again, sorry for my mix-up.

 

[JE McGimpsey]

Please see my reply to your previous post - you're absolutely correct -
I was using the App_WorkbookDeactivate event rathern than the
App_WorkbookClose event.

 

[Aaron Queenan]

Aaron -

I owe you an apology.

Graciously accepted.

I had written the class module using the App_WorkbookDeactivate() event,
but in this thread, I wrote that I was using the App_WorkbookClose()
event.

Using the Workbook_Close() or the App_WorkbookClose() events will, of
course, exhibit exactly the behavior you describe.

The App_WorkbookDeactivate event is the last event fired when closing a
workbook - it happens after the file is saved, if in fact it is saved.
If the user cancels the save, the event isn't fired at all, so the
control is never dimmed.

Now that's an interesting idea. I'll have a look at using it myself.

Excel seems to work around the theory that events don't exist for anything
useful (other than the App_WorkbookOpen() event), but if you watch the other
events you can get some sort of an idea about what's going on. :-( Oh,
well, another tool for the bag of strange but useful Excel code.

Thanks,
Aaron.

 

[Beto]

In any case, here's the correct code in my DimButtonClass module:

Public WithEvents DBApp As Application

Private Sub DBApp_WorkbookDeactivate(ByVal Wb As Excel.Workbook)
On Error Resume Next
CommandBars.FindControl(Tag:=csCTRLTAG).Enabled = _
(Workbooks.Count > 1)
On Error GoTo 0
End Sub

Private Sub DBApp_WorkbookOpen(ByVal Wb As Excel.Workbook)
On Error Resume Next
CommandBars.FindControl(Tag:=csCTRLTAG).Enabled = True
On Error GoTo 0
End Sub

Again, sorry for my mix-up.

Now it's working great!

Thanks!