custom toolbar options

ishi

Board Regular
Joined
Apr 3, 2003
Messages
126
I have created my own toolbar and just placed it at the top. What i would like to do is have this hidden or disabled on open of workbook. Also i have a login form that loads on open of the workbook and when the users enter their logins (not set up yet) depening who they are can have certain buttons enabled on my toolbar. After the login process have the toolbar unhidden.

Any help much appreciated.

Thanks,

ishi
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

TommyGun

MrExcel MVP
Joined
Dec 9, 2002
Messages
4,202
Pretty simple to do. You will just need info as to what controls are going to be locked for which users, then you can loop thru that and enable/disable controls.

Hiding/Unhiding the toolbar is pretty easy too. Assuming that your toolbar is named "CustomBar"...
Code:
Application.CommandBars("CustomBar").Visible = True 'Show
Application.CommandBars("CustomBar").Visible = False 'Hide
 

ishi

Board Regular
Joined
Apr 3, 2003
Messages
126
Thanks TommyGun for that. Just one thing i am not entirely sure on looping through the controls on my toolbar.

I have tried this
For i = 1 To Application.CommandBars("CustomBar").Controls.Count
MsgBox s.Name
Next i

But keeping getting error "object or with block variable not set."

any ideas what's wrong.

Thanks,

ishi
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What's your variable s?

Try:

Code:
Dim Ctrl As CommandBarControl
For Each Ctrl In Application.CommandBars("CustomBar").Controls
MsgBox Ctrl.Name 
Next Ctrl
 

ishi

Board Regular
Joined
Apr 3, 2003
Messages
126

ADVERTISEMENT

my variable was dim s as control. Which probably isn't right but with your example i get run-time error 438 object doesn't support the property or method.

ishi
 

TommyGun

MrExcel MVP
Joined
Dec 9, 2002
Messages
4,202
Instead of
Code:
Ctrl.Name
use
Code:
Ctrl.Caption

CommandBarControls do not have a NAME property, they are referred to by their CAPTION.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Sorry, it's:

MsgBox Ctrl.Caption
 

ishi

Board Regular
Joined
Apr 3, 2003
Messages
126
thanks that works. But i am surprised that the caption is the name that i have given to the buttons on my toolbar. A little confusing.

ishi
 

TommyGun

MrExcel MVP
Joined
Dec 9, 2002
Messages
4,202
Nobody said this would be easy. :devilish:

ME said:
Pretty simple to do.

Actually, if you want to talk about confusing Object models, then try to code some things in Word. :banghead:
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
ishi said:
... A little confusing.

ishi

That's why I got the code wrong. :oops: I was mostly concerned with converting your For...Next loop into a For Each...Next loop.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,854
Messages
5,525,225
Members
409,637
Latest member
LT TASL

This Week's Hot Topics

Top