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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

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

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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,013
Messages
5,508,788
Members
408,694
Latest member
LightBright

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top