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,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

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)

"Beto" wrote in message
...
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,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

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


In article ,
"Bob Phillips" wrote:

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

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


In article ,
Beto wrote:

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,

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

In article ,
JE McGimpsey wrote:

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


In article ,
Beto wrote:

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,

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,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

JE McGimpsey wrote:
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

In article ,
JE McGimpsey wrote:


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 wrote:

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
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

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.

Aaron Queenan wrote:

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,
--
Beto
Reply: Erase between the dot (inclusive) and the @.
Responder: Borra la frase obvia y el punto previo.

"Beto" wrote in message
...
Aaron Queenan wrote:

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.

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.

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.



In article ,
"Aaron Queenan" wrote:

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.

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" wrote in message
...
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.



In article ,
"Aaron Queenan" wrote:

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.