View Full Version : Removing Toolbar etc
Hi Everyone :)
Well I have built my own little toolbar which I want people to use, however I have two simple questions that you experts will probably be able to easily answer.
1. How to a remove Excel's default toolbar, as I want people to only use my toolbar?
2. Can I create drop down menus on my custom toolbar via vba or somthing?
Also using vba, is there any way to high a row via just clicking on one cell?
example, I click on A8 for example, everything from A8 to F8 gets highlighted.
Thank you all :bow:
jammer6_9
05-12-2007, 05:57 AM
This was shared to me by the expert here so I am sharing it to you also. it works & I enjoy it and feels like you are in control of evrything...
Public Formula Bar
Sub ToolbarRemover()
On Error Resume Next
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
If oCB.Name <> "Cell" Then
oCB.Enabled = False
End If
Next oCB
mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
AddNewToolBar
End Sub
Sub ToolBarRecover()
On Error Resume Next
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB
Application.DisplayFormulaBar = mFormulaBar
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
DeleteToolbar
End Sub
Private Sub Workbook_Activate()
ToolbarRemover
End Sub
Private Sub Workbook_Deactivate()
ToolBarRecover
End Sub
jammer6_9
05-12-2007, 06:03 AM
Might be like this for a dropdown menu?
Sub AddNewToolBar()
Dim ComBar As CommandBar, ComBarContrl As CommandBarControl
On Error GoTo ErrorHandler
On Error Resume Next
CommandBars("My Toolbar").Delete
Set ComBar = CommandBars.Add(Name:="OFS Analysis", Position:= _
msoBarTop, Temporary:=True)
ComBar.Visible = True
Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlButton)
With ComBarContrl
.BeginGroup = True
.Caption = " &File "
.Style = msoButtonIconAndCaption
.FaceId = 627
.OnAction = "Macro22"
End With
Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlPopup)
With ComBarContrl
.BeginGroup = True
.Caption = "&Central "
.TooltipText = "Riyadh"
.OnAction = "Macro1"
End With
Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlPopup)
With ComBarContrl
.BeginGroup = True
.Caption = "&Western "
.TooltipText = "Jeddah, Makkah, Madinah"
.OnAction = "Macro2"
End With
Set ComBarContrl = ComBar.Controls.Add(Type:=msoControlPopup)
With ComBarContrl
.BeginGroup = True
.Caption = "&Eastern "
.TooltipText = "Al-Khobar,Dammam, Jubail"
.OnAction = "Macro3"
End With
With ComBar
.Width = 650
.Visible = True
End With
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCr & Err.Description
Exit Sub
End Sub
Sub KSA()
On Error Resume Next
Application.CommandBars("KSA").ShowPopup
CommandBars("KSA").Delete
On Error GoTo 0
With CommandBars.Add(Name:="KSA", Position:=msoBarPopup)
With .Controls.Add(Type:=msoControlPopup)
.Caption = "Riyadh"
With .Controls.Add(Type:=msoControlButton)
.Caption = "HES"
.OnAction = "Macro10"
.Style = msoButtonIconAndCaption
'.FaceId = 5
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "IMF"
.OnAction = "Macro11"
.Style = msoButtonIconAndCaption
'.FaceId = 6
End With
End With
End With
End Sub
Sub UAE()
On Error Resume Next
Application.CommandBars("UAE").ShowPopup
CommandBars("UAE").Delete
On Error GoTo 0
With CommandBars.Add(Name:="UAE", Position:=msoBarPopup)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Macro13"
.Caption = "BHH"
.Style = msoButtonIconAndCaption
'.FaceId = 11
End With
End With
End Sub
Sub Egypt()
On Error Resume Next
Application.CommandBars("Egypt").ShowPopup
CommandBars("Egypt").Delete
On Error GoTo 0
With CommandBars.Add(Name:="Egypt", Position:=msoBarPopup)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Macro17"
.Caption = "WHT"
.Style = msoButtonIconAndCaption
'.FaceId = 15
End With
End With
End Sub
Sub File()
On Error Resume Next
Application.CommandBars("jcr").ShowPopup
CommandBars("jcr").Delete
On Error GoTo 0
With CommandBars.Add(Name:="jcr", Position:=msoBarPopup)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Macro14"
.Caption = "&Save"
.Style = msoButtonIconAndCaption
.FaceId = 3
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Macro15"
.Caption = "&Print"
.Style = msoButtonIconAndCaption
.FaceId = 4
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Macro16"
.Caption = "&Print Preview"
.Style = msoButtonIconAndCaption
.FaceId = 109
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Macro18"
.Caption = "&Save As"
.Style = msoButtonIconAndCaption
.FaceId = 53
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Macro19"
.Caption = "&Exit"
.Style = msoButtonIconAndCaption
.FaceId = 1088
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Macro20"
.Caption = "&Help"
.Style = msoButtonIconAndCaption
.FaceId = 49
End With
End With
End Sub
Sub DeleteToolbar()
On Error Resume Next
CommandBars("OFS Analysis").Delete
End Sub
Bob Phillips
05-12-2007, 06:22 AM
This was shared to me by the expert here so I am sharing it to you also. it works & I enjoy it and feels like you are in control of evrything...
Public Formula Bar
Sub ToolbarRemover()
On Error Resume Next
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
If oCB.Name <> "Cell" Then
oCB.Enabled = False
End If
Next oCB
mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
AddNewToolBar
End Sub
Sub ToolBarRecover()
On Error Resume Next
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB
Application.DisplayFormulaBar = mFormulaBar
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
DeleteToolbar
End Sub
Private Sub Workbook_Activate()
ToolbarRemover
End Sub
Private Sub Workbook_Deactivate()
ToolBarRecover
End Sub
It should be pointed out that this code does leave the right-click context menu in place. Maybe this is a srequired, but best to be explicit.
Bob Phillips
05-12-2007, 06:24 AM
Also using vba, is there any way to high a row via just clicking on one cell?
example, I click on A8 for example, everything from A8 to F8 gets highlighted.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A8" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Resize(, 8).Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.
jammer6_9
05-12-2007, 06:56 AM
well "xld" was the expert i was talking about :clap:
Hi,
Where do I put this coding? in a module? also how do I ensure it boots on the startup of the application?
And thanks for your help everyone.
This was shared to me by the expert here so I am sharing it to you also. it works & I enjoy it and feels like you are in control of evrything...
Public Formula Bar
Sub ToolbarRemover()
On Error Resume Next
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
If oCB.Name <> "Cell" Then
oCB.Enabled = False
End If
Next oCB
mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
AddNewToolBar
End Sub
Sub ToolBarRecover()
On Error Resume Next
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB
Application.DisplayFormulaBar = mFormulaBar
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
DeleteToolbar
End Sub
Private Sub Workbook_Activate()
ToolbarRemover
End Sub
Private Sub Workbook_Deactivate()
ToolBarRecover
End Sub
Bob Phillips
05-12-2007, 03:40 PM
bopo,
It uses Workbook events, so it should be placed in the Thisworkbook code module.
Regarding the code to disable the toolbars
'Public Formula Bar', keeps coming up saying
Complier Error
Expected: End of Statement
I have copied to code exactly, any idea on the problem?
Thanks
Also, i have been trying to change the coding below to work with any cell in column A, such as A1, A2, A3,A4 etc etc, however do I change the coding so its works on all the column A cells.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A8" '<== change to suit
On Error Goto ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Resize(, 8).Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
Cheers
Bob Phillips
05-16-2007, 11:53 AM
Don't know what happened thert, but
Public Formula Bar
should be
Public mFormulaBar
On the other bit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit
On Error Goto ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Resize(, 8).Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
jammer6_9
05-16-2007, 11:57 PM
I was using this since "xld" gave this code to me and no problem at all.
Option Explicit
Public mFormulaBar
Private Sub Workbook_Activate()
On Error Resume Next
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
If oCB.Name <> "Cell" Then
oCB.Enabled = False
End If
Next oCB
mFormulaBar = Application.DisplayFormulaBar
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
End Sub
Private Sub Workbook_Deactivate()
On Error Resume Next
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB
Application.DisplayFormulaBar = mFormulaBar
Application.DisplayStatusBar = True
End Sub
Regarding the code to disable the toolbars
'Public Formula Bar', keeps coming up saying
Complier Error
Expected: End of Statement
I have copied to code exactly, any idea on the problem?
Thanks
Also, i have been trying to change the coding below to work with any cell in column A, such as A1, A2, A3,A4 etc etc, however do I change the coding so its works on all the column A cells.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A8" '<== change to suit
On Error Goto ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Resize(, 8).Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
Cheers
Kind of got a problem now,
I need to remove the code, however as all the toolbars are remove/disabled, I cant get to the visual basic toolbar?
Any ideas as I am really stuck now :(
Simon Lloyd
05-19-2007, 06:39 AM
try Alt+F11
It worked :thumb
Also what piece of code would I need to move to allow float toolbars to appear, like the visual basic one and my custom one.
try Alt+F11
jammer6_9
05-19-2007, 07:38 AM
Might help... set the position of your Toolbar
Set ComBar = CommandBars.Add(Name:="My Menu", Position:= _
msoBarTop, Temporary:=True)
'set position "msoBarTop" ---> right click ---> List Properties/Methods then choose whatever you desire
It worked :thumb
Also what piece of code would I need to move to allow float toolbars to appear, like the visual basic one and my custom one.
Also I have deleted the code, however the toolbars are not re-appearing, even in a new Excel document?
lucas
05-19-2007, 07:46 AM
so you have deleted all of your standard excel toolbars?
Well everything, I used that code, removed it, and now I cant get them to re-appear, even on new excel documents :(
so you have deleted all of your standard excel toolbars?
lucas
05-19-2007, 12:53 PM
You can try the code that jammer offered in post 2 to try to recover them:
Sub ToolBarRecover()
On Error Resume Next
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB
Application.DisplayFormulaBar = mFormulaBar
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
DeleteToolbar
End Sub
It worked :)
Just two more question, although I asked if drop down menus could be created, and some code was supplied, I was wondering if there is a GUI or method of using the interface to do this.
And could someone give me a demonstration on how to call a procedure?
Cheers
You can try the code that jammer offered in post 2 to try to r
ecover them:
Sub ToolBarRecover()
On Error Resume Next
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB
Application.DisplayFormulaBar = mFormulaBar
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
DeleteToolbar
End Sub
lucas
05-19-2007, 02:07 PM
Hey bopo,
check this thread out and see if it helps....start with post #7 in the thread.....
http://www.vbaexpress.com/forum/showthread.php?t=12670
Kind of, but I would like to copy an existing drop down menu into my custom toolbar, such as the 'File' menu or something?
jammer6_9
05-20-2007, 04:46 AM
Could you check attache sample file if you are referring to something like this?
Yes, thats it :yes
Could you check attache sample file if you are referring to something like this?
jammer6_9
05-20-2007, 05:30 AM
:guitar2: Let's get rock then... Open the WorkBook then click "Alt+F11" to go to VBA Editor and check out the codes. The codes gives you
Disable all ToolBars
Add your Custom ToolBar
Totally in control of the File :rotlaugh:Regards & I am glad this answers your question. I was just sharing you what I have learned from the Expert "xld" has given the codes. Everything is there in the workbook bopo. Just open the workbook. Go to VBA Editor and check out the codes. You can change captions & sub menu in there as you want. If you need more clarification, just post the issue in this thread then a lot of people will help you for sure. My knowledge with VBA has limitations. I have just shared you what I have learned rather taken from the experts. A lot of people is here to help people like us who is just new to VBA.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.