PDA

View Full Version : Solved: Make enable-disable a special toolbar depended a condition?



Erdin? E. Ka
12-08-2006, 03:31 PM
Hi everyone, :hi:

I wrote these codes for make disable mytoolbar (ToolBarName). When Excel application opening; this codes running from PERSONAL.XLS via Workbook_AddinInstall and it's ok.

I want that, if one or more workbook is open then make mytoolbar (ToolBarName) is enable.

But the preblem is i didn't found a solution for this. What i must do?


Private Sub Workbook_AddinInstall()
Dim i As Byte
Dim Adet As Byte
Adet = Application.CommandBars(ToolBarName).Controls.Count
If Workbooks.Count = 0 Then
For i = 1 To Adet
Application.CommandBars(ToolBarName).Controls(i).Enabled = 0
Next i
Else
For i = 1 To Adet
Application.CommandBars(ToolBarName).Controls(i).Enabled = 1
Next i
End If
End Sub


Thanks in advance.:friends:

JimmyTheHand
12-09-2006, 09:09 AM
Hi.

You need to use the WorkBookOpen event of the Application.
See Excel Help for this topic: Using Events with the Application Object
It speaks about a new class module. Create that class module in personal.xls. (All modules, procedures, etc. I speak about further on should be created in personal.xls.)

It also speaks about a procedure that connects "the declared object in the class module with the Application object".
The best way to do it is using this code in the Workbook sheet module.
Dim AppClassMod As New EventClassModule
Private Sub Workbook_Open()
Set AppClassMod.App = Application
End Sub
Where, of course, name of the object variable (here: AppClassMod) can be anything. So, the code above will run each time personal.xls is opened, that is, each time Excel is started.

Create a new subroutine in any standard code module. It should contain the code you posted. Name the Sub as you wish, I will refer to it as "EnableToolBar" further on.
E.g
Sub EnableToolBar()
Dim i As Byte
Dim Adet As Byte
Adet = Application.CommandBars(ToolBarName).Controls.Count
If Workbooks.Count = 0 Then
For i = 1 To Adet
Application.CommandBars(ToolBarName).Controls(i).Enabled = 0
Next i
Else
For i = 1 To Adet
Application.CommandBars(ToolBarName).Controls(i).Enabled = 1
Next i
End If
End Sub


Finally, on the new classmodule, create this event handler Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
EnableToolBar
End Sub

If all is OK, then EnableToolBar will run each time a workbook is opened, as long as personal.xls is opened.

Also, you should be aware of the fact that personal.xls is a workbook, so Workbooks.Count will never return zero as long as personal.xls is opened. Your code should be changed.

mdmackillop
12-09-2006, 09:27 AM
Hi Jimmy,
Welcome to VBAX
I'm getting a compile error "User Defined type not defined" on this line.
Dim AppClassMod As New EventClassModule


Am I missing a reference?
Regards
MD

Erdin? E. Ka
12-09-2006, 09:54 AM
Hi, JimmyTheHand thank you to kindly help.

Probably i didn't do step by step your suggestions. :(

I wrote Personal.xls's Sheet1 (Sheet1) Code Page:

Dim AppClassMod As New EventClassModule
Private Sub Workbook_Open()
Set AppClassMod.App = Application
End Sub


And to Module1:
Sub EnableToolBar()
Dim i As Byte
Dim Adet As Byte
Adet = Application.CommandBars(ToolBarName).Controls.Count
If Workbooks.Count = 1 Then
For i = 1 To Adet
Application.CommandBars(ToolBarName).Controls(i).Enabled = 0
Next i
ElseIf Workbooks.Count > 1 Then
For i = 1 To Adet
Application.CommandBars(ToolBarName).Controls(i).Enabled = 1
Next i
End If
End Sub

I tried as:
If Workbooks.Count = 0 Then
And
If Workbooks.Count = 1 Then
But nothing's changed.

Finally, to Class1:

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
EnableToolBar
End Sub


But it's not ok with this method. :dunno

Should i doing wrong something:doh:?!?!

mdmackillop
12-09-2006, 09:59 AM
Hi Erdinc,
I believe this

Dim AppClassMod As New EventClassModule
Private Sub Workbook_Open()
Set AppClassMod.App = Application
End Sub
has to go into ThisWorkbook module

Bob Phillips
12-09-2006, 10:05 AM
Jimmy has made a few errors in the code, and he also has addressed other events that you need to check NewWorkbook, and Close.

Also, what about non-visible workbooks, like thosu in XLStart?

And finally, what makes you think that Personal.xls does an AddinInstall?

Anyway, your coide can all go in the ThisWorkbook code module, you don't need a separate class module



Option Explicit

Public WithEvents App As Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
EnableToolBar
End Sub

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
EnableToolBar
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
EnableToolBar
End Sub

Private Sub Workbook_Open()
Set App = Application
EnableToolBar
End Sub

Private Sub EnableToolBar()
Dim i As Long
Dim Adet As Long
Adet = Application.CommandBars(Toolbarname).Controls.Count
If Workbooks.Count = 0 Then
For i = 1 To Adet
Application.CommandBars(Toolbarname).Controls(i).Enabled = 0
Next i
Else
For i = 1 To Adet
Application.CommandBars(Toolbarname).Controls(i).Enabled = 1
Next i
End If
End Sub



Oh yes, I haven't created the menus in this code, I assume you are doing that elsewhere.

JimmyTheHand
12-09-2006, 10:46 AM
Hi Everyone,

Yes, I might have made errors, as this was my very first try with personal.xls and with using events of the application object. But it works for me, I don't know why you get errors. Maybe my explanation was unclear?:dunno

Following Excel's Help I adjusted my personal.xls so that each time I open a workbook, the number of opened workbooks are displayed via MsgBox. I upload the file so that you can see it for yourself.

xld is right on the account that I haven't dealt with the case of new and closing workbooks. I was lazy, and wanted to leave some work to Erdin? E. Ka :yes

On the other hand, he was wrong on the account that the same EnableToolBar procedure is working properly with the BeforeClose event. Here we deal with the number of opened workbooks, and when BeforeClose occurs, that workbook is still open. If there was an AfterClose event, now that would be perfect. But there isn't such event, so I suggest to modify the code according to this:
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
EnableToolBar (False)
End Sub

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
EnableToolBar (True)

End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
EnableToolBar (False)
End Sub

Private Sub EnableToolBar(Before_Close As Boolean)
Dim i As Long
Dim Adet As Long
Adet = Application.CommandBars(Toolbarname).Controls.Count
If Workbooks.Count + Before_Close = 0 Then
For i = 1 To Adet
Application.CommandBars(Toolbarname).Controls(i).Enabled = 0
Next i
Else
For i = 1 To Adet
Application.CommandBars(Toolbarname).Controls(i).Enabled = 1
Next i
End If
End Sub

JimmyTheHand
12-09-2006, 10:51 AM
Hi Jimmy,
Welcome to VBAX
I'm getting a compile error "User Defined type not defined" on this line.
Dim AppClassMod As New EventClassModule

Am I missing a reference?
Regards
MD
You have to create a Class module, and change it's name from the default "Class1" to "EventClassModule". Or else, change the code to
Dim AppClassMod As New Class1

Edit:

And thanks for the greeting :hi:

JimmyTheHand
12-09-2006, 11:01 AM
Oh, and there's something else. I don't know why it is so, but by trial and error I learned that if I make any changes in any module of personal.xls, the code won't work until I restart Excel. Probably this is when personal.xls gets recompiled and the macro program loaded into memory.

Bob Phillips
12-09-2006, 11:42 AM
... this was my very first try with personal.xls and with using events of the application object.

Nothing to do with Personal.xls, in fact I still don't understand how that comes it the problem at all, but functional errors, such as the fact that you didn't declare WitheEvents the App variable, which is a bit fundamental if it is to work (and I notice that you cut it out again in the re-post).

JimmyTheHand
12-09-2006, 12:12 PM
Nothing to do with Personal.xls, in fact I still don't understand how that comes it the problem at all, but functional errors, such as the fact that you didn't declare WitheEvents the App variable, which is a bit fundamental if it is to work (and I notice that you cut it out again in the re-post).
The original problem was to display or hide a toolbar depending on the number of opened workbooks. I might be wrong but I think, to achieve the final goal, you need something running in the background that can sense when a workbook is opened, closed, etc. and execute the proper code. Somehow you must define an application object, so that events of the application can be used. For this purpose personal.xls had been chosen. Before today I didn't know what personal.xls was, what it was used for. I started down the trail that Erdin? E. Ka has set. Maybe it's all wrong, but, hey, it works! :yes

As for me omitting some neccessary details, yes, that's true. However, I had referred to the Excel Help, which describes just that. I merely mentioned steps that are different or not described in the Help.

Nevertheless, In the future I'll be more careful and explicit. :bow: No more misunderstandings :)

Erdin? E. Ka
12-09-2006, 03:12 PM
Hi guys!!:hi:

I say yield because still i'm not to be able to understand it.:doh: Many times i tried tried tried ... but as long as i'm getting crazy per trying... :banghead: :banghead: :banghead:

Anyway, thank you all for kindly helps.

With love... :hi:

Bob Phillips
12-09-2006, 03:33 PM
Are you trying to do it from an addin or Personal.xls.

mdmackillop
12-09-2006, 03:34 PM
Hi Erdinc,
Can you post your code for creating the toolbars so I can try all the bits together.

Erdin? E. Ka
12-09-2006, 05:00 PM
Hi mdmackillop and xld, it's me again. :hi:

I attached the file for creating a toolbar as a xls file. I used this file in my Excel app. as xla add-in file.

(Xld, i tried for make MyToolbar is enable-disable in Personal.xls but i didn't solved yet... :( )

Thank you.

Bob Phillips
12-09-2006, 05:45 PM
Personally, I would hide the toolbar, not disable the commands

Erdin? E. Ka
12-10-2006, 06:49 AM
Hi xld :hi:, i tried your file but doing nothing... :whyme:

Bob Phillips
12-10-2006, 10:11 AM
You must be joking, it does lots, I tried with opening books, new books, closing them etc.

I think you must have other workbooks open that are being counted, so you never get to 0 workbooks.

lucas
12-10-2006, 10:48 AM
works for me also.....

Erdin? E. Ka
12-10-2006, 01:20 PM
Hi xld :hi:,

So, there i must be something wrong.:think:

I edited your VBAX - Erdinc - archleo35.xls file as a add-in, then is wasn't ok, this time i tried the file as a personal.xls. Then it wasn't ok again.

I added the picture.

What is my mistake? Excuse me. I am really confused. :help

Erdin? E. Ka
12-10-2006, 03:31 PM
:eek: Wooww!!! It's ok now!... :ole:

Finally i did it!!.

Xld, i found my mistake.

At the first time i saved your "VBAX - Erdinc - archleo35.xls" file as personal.xls. But i wasn't ok.

Now i tried personal.xls's add-in property is true. And it's ok. Now.

Your codes working perfect.

Sorry for torment you all. : pray2:

It was my fault. Anyway thanks a lot. I will use this codes for my friend now. Also i think i will change some of my projects with your perfect help.

Thank you very much again... :thumb

Bob Phillips
12-10-2006, 04:02 PM
Dang! I was updating the file to an add-in myself. You've made me redundant now :doh:.

Erdin? E. Ka
12-10-2006, 04:29 PM
Dang! I was updating the file to an add-in myself. You've made me redundant now :doh:.

Excuse me Xld, as you know; my English is fifty-fifty :yes So, i don't understood it. :dunno

redundant?

mdmackillop
12-10-2006, 04:33 PM
Unemployed!

Erdin? E. Ka
12-10-2006, 05:14 PM
Unemployed!

Thank you Malcolm. Probably now i understood it. :whistle:





Dang! I was updating the file to an add-in myself. You've made me redundant now :doh:.

Hi Xld,
In fact, i am an unemployed person and i am looking for a job for myself but please feel free my friend, i don't want to get your job!!! :*)

With love... :friends:

Bob Phillips
12-10-2006, 05:33 PM
Unemployed!

Worse than that, surplus to requirements :(