MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Ryan - Application Level Event?


Posted by JAF on July 05, 2000 4:57 AM

Ryan

You responded to my query about "ghosting" toolbar icons and advised that I had to set up an Application Level Event to do this.

I have the following code in a module:
Sub enable_button()
Application.Toolbars("jaftest").ToolbarButtons(1).Enabled = True
End Sub

Sub disable_button()
Application.Toolbars("jaftest").ToolbarButtons(1).Enabled = False
End Sub

Sub count_open_workbooks()
Dim count_workbooks
Dim x As Workbook
On Error Resume Next
Set x = Workbooks("PERSONAL.XLS")
If Err = 0 Then count_workbooks = Workbooks.Count - 1 Else count_workbooks = Workbooks.Count
If count_workbooks < 1 Then Call disable_button Else Call enable_button
End Sub

and the following code in a Class Module:
Public WithEvents AppEvents As Application

Private Sub AppEvents_NewWorkbook(ByVal Wb As Excel.Workbook)
Dim count_workbooks
Dim x As Workbook
On Error Resume Next
Set x = Workbooks("PERSONAL.XLS")
If Err = 0 Then count_workbooks = Workbooks.Count - 1 Else count_workbooks = Workbooks.Count
If count_workbooks < 1 Then Call disable_button Else Call enable_button
End Sub

Private Sub AppEvents_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, Cancel As Boolean)
Dim count_workbooks
Dim x As Workbook
On Error Resume Next
Set x = Workbooks("PERSONAL.XLS")
If Err = 0 Then count_workbooks = Workbooks.Count - 1 Else count_workbooks = Workbooks.Count
If count_workbooks < 1 Then Call disable_button Else Call enable_button
End Sub

Private Sub AppEvents_WorkbookOpen(ByVal Wb As Excel.Workbook)
Dim count_workbooks
Dim x As Workbook
On Error Resume Next
Set x = Workbooks("PERSONAL.XLS")
If Err = 0 Then count_workbooks = Workbooks.Count - 1 Else count_workbooks = Workbooks.Count
If count_workbooks < 1 Then Call disable_button Else Call enable_button
End Sub


Any suggestions as to what I'm doing wrong?

JAF


Posted by Ryan on July 05, 0100 8:37 AM

Hi JAF,

I got your email and sent you the solution. If you didn't get it let me know and I will send it again. Let me know!

Ryan

Posted by jaf on July 05, 0100 10:50 AM

Ryan

Didn't receive your email. Can send it again to either my new email address shown above or to realmofconfusion@yahoo.co.uk (my common web based email address).

Thanks.

JAF