Show/Hide toolbars [solved]

Hi guys,

I am preparing my own toolbar in Excel using VBE. My intention is to prepare small macro which show/hide already visible toolbars and them make visible only my custom toolbar. I have the same problem with menus.
How can I determine or make list of visible menus or toolbars and then make them hidden or visible.

Thanks

Boris

U can do like this:

VB Code:

CommandBars("Some cmd bar").Visible = False
CommandBars("Some cmd bar").Visible = True

etc. etc.

Quote:

---

Originally Posted by Pradeep1210

U can do like this:

VB Code:

CommandBars("Some cmd bar").Visible = False
CommandBars("Some cmd bar").Visible = True

etc. etc.

---

I would like to make macro wich determine the name of visible menus or toolbars.

Boris

Quote:

---

Originally Posted by bolcskei

I would like to make macro wich determine the name of visible menus or toolbars.

Boris

---

VB Code:

For Each cb in CommandBars
    If cb.Visible Then
        'do ur stuff here
        ' like storing it somewhere or whatever...
    End If
Next

Quote:

---

Originally Posted by Pradeep1210

VB Code:

For Each cb in CommandBars
    If cb.Visible Then
        'do ur stuff here
        ' like storing it somewhere or whatever...
    End If
Next

---

Thank you for your clue. I did the following code. The problem is, it showing me only excel build-in and my toolbars only. It do not showing me ADX Toys2 and Business Functions toolbars. Do you have any idea?

Boris

VB Code:

Sub show_commandbars()
Dim bar As CommandBar
Dim i, j
i = 15
j = 10
For Each bar In Application.CommandBars
    If bar.Visible Then Sheets("menu").Cells(i, j + 1).Value = bar.Name
    i = i + 1
Next bar
End Sub

Quote:

---

Originally Posted by bolcskei

Thank you for your clue. I did the following code. The problem is, it showing me only excel build-in and my toolbars only. It do not showing me ADX Toys2 and Business Functions toolbars. Do you have any idea?

Boris

---

I havn't come across any such command bars. If these are some custom commandbars, ensure that they exist. Right click any empty area on any commandbar, al ist of comandbars appears. Ensure that these are in that list.

Quote:

---

Originally Posted by Pradeep1210

I havn't come across any such command bars. If these are some custom commandbars, ensure that they exist. Right click any empty area on any commandbar, al ist of comandbars appears. Ensure that these are in that list.

---

I have checked the toolbars and they are in the list. I have forgotten to write these toolbars are freeware add-ins.

Quote:

---

Originally Posted by bolcskei

I have checked the toolbars and they are in the list. I have forgotten to write these toolbars are freeware add-ins.

---

What does this show in the debug window?

VB Code:

Print commandbars("Business Functions").Visible

Quote:

---

Originally Posted by Pradeep1210

What does this show in the debug window?

VB Code:

Print commandbars("Business Functions").Visible

---

method not valid without suitable object

Quote:

---

Originally Posted by bolcskei

method not valid without suitable object

---

Are u sure these are Commandbars only? I think these might be something else (like a window etc.)

Can u post the link to URL where u got these?

Quote:

---

Originally Posted by Pradeep1210

Are u sure these are Commandbars only? I think these might be something else (like a window etc.)

Can u post the link to URL where u got these?

---

As I wrote before it is a freeware add-ins, downloaded from

www.businessfunctions.co.uk

Quote:

---

Originally Posted by bolcskei

As I wrote before it is a freeware add-ins, downloaded from

www.businessfunctions.co.uk

---

I downloaded on my machine and installed it. It works fine. No problems. Maybe it's corrupted on ur system. Pl. try installing it again and check again.

BTW it's a great tool :p

Quote:

---

Originally Posted by Pradeep1210

I downloaded on my machine and installed it. It works fine. No problems. Maybe it's corrupted on ur system. Pl. try installing it again and check again.

BTW it's a great tool :p

---

Was you able to list this add-in? I will try your suggestion.

Here is some other interesing links:

www.asap-utilities.com
www.ablebits.com
www.add-in-express.com

Quote:

---

Originally Posted by bolcskei

Was you able to list this add-in? I will try your suggestion.

Here is some other interesing links:

www.asap-utilities.com
www.ablebits.com
www.add-in-express.com

---

Yes it lists in the commandbars collection.
Try this: If it is functioning correctly on ur system, u should see the entry in ur Debug window.

VB Code:

For Each cb in CommandBars
    Debug.Print cb.Name
Next

Quote:

---

Originally Posted by Pradeep1210

Yes it lists in the commandbars collection.
Try this: If it is functioning correctly on ur system, u should see the entry in ur Debug window.

VB Code:

For Each cb in CommandBars
    Debug.Print cb.Name
Next

---

Thank you verry much! Sorry for your time. The problem was verry stupid.
I had to i=i+1 give before end if. Without this the program listing me commandbars, but when the bar was invisible it leaving empty cell. The business function and ADX toys was last in the list and was listed approximately at 150th row and I was not scrolling so far and did not see it.
Many thanks

Boris

VB Code:

Sub show_commandbars()
Dim bar As CommandBar
Dim i, j
i = 15
j = 10
For Each bar In Application.CommandBars
    If bar.Visible Then
    Sheets("menu").Cells(i, j + 1).Value = bar.Name
    i = i + 1
    End If
Next bar
End Sub

Ya, bolcskei is on right way