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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,002
Messages
5,484,127
Members
407,431
Latest member
kalvinswisher

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top