PDA

View Full Version : SOLVED: Disable Custom Buttons when no Worksheet is Loaded



lockma
04-17-2008, 07:39 AM
Hi All,

This is my first post on here so I hope I explain myself well.

What i am looking to do is to disable my custom buttons in my custom toolbar when there is no worksheet open and as soon as ANY worksheet is loaded enable the buttons.

I have the code that enables and disables the buttons but cant find any trigger points to call this code.

This is the code i have for the two actions



For Each Item In Application.CommandBars.Item("NameofToolbar").Controls
Item.Enabled = False
Next Item

&


For Each Item In Application.CommandBars.Item("NameofToolbar").Controls
Item.Enabled = True
Next Item


Any help would be appreciated.

Many thanks

Mark

lucas
04-17-2008, 08:16 AM
I don't understand, how do you not have any worksheet open?

lockma
04-17-2008, 08:22 AM
If you open excel then a blank worksheet is created. if you then go to file>close the blank worksheek is closed but excel is still running.

It's when Excel is running but no sheets are open that I want my buttons to be disabled - Like most of the inbuilt toolbar buttons.

lucas
04-17-2008, 08:40 AM
How about a workbook open event that opens a messagebox that asks if you want to delete the sheets and if so then run your code based on a 0 count of worksheets?

lockma
04-17-2008, 08:51 AM
I'm not sure you understand what i am asking - I dont want any messageboxes etc. All i want to do is for Excel to disable my custom buttons (ie grey them out) when there are no worksheets open.

Thanks for the replies though

tpoynton
04-17-2008, 10:54 AM
I tried to find a way of doing this and never succeeded. Best I could do is as Steve suggested. I'm not sure there is an event you could tie this to...and if there is, I'd love to know it!

tstav
04-17-2008, 11:27 AM
Hi mark,

What i am looking to do is to disable my custom buttons in my custom toolbar when there is no worksheet open and as soon as ANY worksheet is loaded enable the buttons
Replace 'worksheet' with 'workbook' and the sentence will be perfect.


If you open excel then a blank worksheet is created. if you then go to file>close the blank worksheek is closed but excel is still running
File>Close refers to workbooks, not to worksheets. Worksheets are either added or deleted within a workbook, never File>Closed.


I don't understand, how do you not have any worksheet open?
See? The misleading is obvious... You misled us (me too) into thinking Worksheetwise when you actually meant everything Workbookwise...

Anyway, I think I have an answer for what you want.

Since you want your custom buttons to be in the toolbar even when no files are open, then they should be placed there from code within the 'Personal.xls' file (which is in the OFFICExx\XLSTART folder). This file is automatically opened every time you start Excel (and stays open until you quit Excel).

Now. More code must go into this file, in order to check the number of workbooks that are open. When only one workbook is found open (i.e. the Personal.xls) that would mean that the user has closed all other files. In that case you would disable your buttons.

One way to do it is the following.
In the ThisWorkbook code module of the Personal.xls, declare a new variable
Dim WithEvents xlApp As Application
In the Workbook_Open() event of the Personal.xls, you instantiate this variable
Private Sub Workbook_Open()
If xlApp Is Nothing Then
Set xlApp = Application
End If
'Create buttons in toolbar
End Sub

Then you add the code you want in these events:
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
If Application.Workbooks.Count >= 2 then
'enable buttons
End If
End Sub

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
If Application.Workbooks.Count = 2 then
'disable buttons
End If
End Sub
You quit Excel saving the Personal.xls when asked to, and then you reopen it.

What happens is, as you close the open workbooks, the _WorkbookBeforeClose event fires and the code checks how many files are open at the moment.
When the last visible file is being closed, the Workbooks.count will be 2 (Personal.xls is still open in the background plus this file which hasn't closed yet) and after this file is permanently closed there will be no more visible files open and the buttons will be disabled.
Did that answer to your question in any way?

Edit: Added the xlApp_WorkbookOpen event and did some slight rephrasing of the closing sentences.

lockma
04-17-2008, 12:47 PM
Workbook, worksheet - what's in a name eh? :dunno

Probably a heck of a lot so i apologise whole heartily for the confusion - i did say on my first post i hope i explain myself well and i guess i failed miserably!!

Thanks for the comprehensive reply btw and it looks like it will do the job perfectly. When i am in work tomorrow i will give it a go but it looks like what i want - just hope it works :)

Will let you all know tomorrow and mark it as solved if it does the trick.

Have a good night (or afternoon or morning depending on where you all are)

lockma
04-18-2008, 03:35 AM
Thanks tstav - worked a treat apart from i had to add in a another event for a new workbook to trigger the enable function as well or the buttons weren't enables if i created a new blank workbook.

Complete code in the end was:-


Dim WithEvents xlApp As Application

Private Sub Workbook_Open()
If xlApp Is Nothing Then
Set xlApp = Application
End If
'Create buttons in toolbar
End Sub

Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)
If Application.Workbooks.Count >= 2 Then
For Each Item In Application.CommandBars.Item("Pitman").Controls
Item.Enabled = True
Next Item
End If
End Sub

Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
If Application.Workbooks.Count = 2 Then
'disable buttons
For Each Item In Application.CommandBars.Item("Pitman").Controls
Item.Enabled = False
Next Item
End If
End Sub

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
If Application.Workbooks.Count >= 2 Then
For Each Item In Application.CommandBars.Item("Pitman").Controls
Item.Enabled = True
Next Item
End If
End Sub


Thanks again for all of your help :clap:

tstav
04-18-2008, 03:43 AM
Good job, lockma!

I'm glad I helped.

Best regards,
tstav