Disable Button when no workbooks are open.
 

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.

Disable Button when no workbooks are open.
 

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.

Disable Button when no workbooks are open.
 

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,

Disable Button when no workbooks are open.
 

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.

Disable Button when no workbooks are open.
 

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,

Disable Button when no workbooks are open.
 

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

Disable Button when no workbooks are open.
 

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.

Disable Button when no workbooks are open.
 

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.

Disable Button when no workbooks are open.
 

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.

Disable Button when no workbooks are open.
 

"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.

Disable Button when no workbooks are open.
 

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.

Disable Button when no workbooks are 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.

Disable Button when no workbooks are open.
 

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.


In article ,
"Aaron Queenan" wrote:

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.

Disable Button when no workbooks are open.
 

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.


In article ,
"Aaron Queenan" wrote:

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.

<snip

"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.

Disable Button when no workbooks are open.
 

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.

In article ,
"Aaron Queenan" wrote:

I never mentioned anything about the second add-in enabling/disabling that
control. I said that "if the other add-in sets the Cancel flag [i.e.
argument to the BeforeClose event] you [i.e. YOUR BeforeClose event code]
will have already disabled the icon".

The attachment contains your code, except displays a message instead of
enabling/disabling the icon. I open it, I see the message "Enabled". I
edit then close it, I see the message "Disabled", then the "Save?" dialogue.
I click cancel. How then, could the icon not be disabled after the user
clicks "Cancel"?

Disable Button when no workbooks are open.
 

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

Disable Button when no workbooks are open.
 

JE McGimpsey wrote:

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