Removing Toolbar etc

[bopo]

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

[jammer6_9]

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


[vba]
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

[/vba]

[jammer6_9]

Might be like this for a dropdown menu?

[vba]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


[/vba]

[Bob Phillips]

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


[vba]
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

[/vba]

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]

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.

[vba]


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
[/vba]

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]

well "xld" was the expert i was talking about

[bopo]

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


[vba]
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

[/vba]

[Bob Phillips]

bopo,

It uses Workbook events, so it should be placed in the Thisworkbook code module.

[bopo]

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.

[VBA]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 [/VBA]

Cheers

[Bob Phillips]

Don't know what happened thert, but

[vba]
Public Formula Bar
[/vba]

should be

[vba]
Public mFormulaBar
[/vba]
On the other bit

[vba]

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
[/vba]

[jammer6_9]

I was using this since "xld" gave this code to me and no problem at all.
[vba]
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[/vba]

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.

[vba]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 [/vba]

Cheers

[bopo]

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]

try Alt+F11

[bopo]

It worked

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]

Might help... set the position of your Toolbar
[vba]
Set ComBar = CommandBars.Add(Name:="My Menu", Position:= _
msoBarTop, Temporary:=True)
'set position "msoBarTop" ---> right click ---> List Properties/Methods then choose whatever you desire
[/vba]

It worked

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]

Also I have deleted the code, however the toolbars are not re-appearing, even in a new Excel document?

[lucas]

so you have deleted all of your standard excel toolbars?

[bopo]

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]

You can try the code that jammer offered in post 2 to try to recover them:
[VBA]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
[/VBA]

[bopo]

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:
[vba]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
[/vba]