PDA

View Full Version : Removing Toolbar etc


bopo
05-12-2007, 05:15 AM
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:
bopo
05-12-2007, 08:34 AM
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.
bopo
05-16-2007, 06:11 AM
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
bopo
05-19-2007, 06:35 AM
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
bopo
05-19-2007, 06:49 AM
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.
bopo
05-19-2007, 07:40 AM
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?
bopo
05-19-2007, 12:11 PM
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
bopo
05-19-2007, 01:27 PM
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
bopo
05-20-2007, 03:24 AM
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?
bopo
05-20-2007, 05:10 AM
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.