SOLVED: Disable Custom Buttons when no Worksheet is Loaded

[lockma]

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]

I don't understand, how do you not have any worksheet open?

[lockma]

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]

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]

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]

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]

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
[vba]Dim WithEvents xlApp As Application[/vba]
In the Workbook_Open() event of the Personal.xls, you instantiate this variable
[vba]Private Sub Workbook_Open()
If xlApp Is Nothing Then
Set xlApp = Application
End If
'Create buttons in toolbar
End Sub
[/vba]
Then you add the code you want in these events:
[vba]Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
If Application.Workbooks.Count >= 2 then
'enable buttons
End If
End Sub[/vba]

[vba]Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
If Application.Workbooks.Count = 2 then
'disable buttons
End If
End Sub[/vba]
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]

Workbook, worksheet - what's in a name eh?

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]

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

[vba]
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
[/vba]

Thanks again for all of your help

[tstav]

Good job, lockma!

I'm glad I helped.

Best regards,
tstav