Permanently prevent User to see Formula Bar

[OMER]

Hola,
I wonder if there is a way to prevent (permanently) a user to activate the
formula bar while a specific worbook is open. I'm able to hide it inside the
openning macro with the following command:

Application.DisplayFormulaBar = False

However, the user can still go to the ribon menu and activate it back.
I want it top remain hidden until the workbook is closed.

Thank you for your help.
OMER

 

[Kevin]

You could hide the View CommandBarControl in the Worksheet Main Menu. Use
the following code and set CBnum = 1 and CBctrl = 30004. Use the second set
of code to unhide it.

Hope this helps.

Sub HideCBcontrols()
Dim CB As CommandBar, CBctrl As CommandBarControl
Dim CBnum As Integer, CBname As String
Dim CBctrlNum As Integer
CBnum = InputBox("Enter CommandBar Index No.")
CBctrlNum = InputBox("Enter CommandBarCtrl ID No.")
On Error Resume Next
For Each CBctrl In Application.CommandBars.Item(CBnum).Controls
If CBctrl.ID = CBctrlNum Then
CBctrl.Visible = False
End If
Next
End Sub

Sub ShowCBcontrols()
Dim CB As CommandBar, CBctrl As CommandBarControl
Dim CBnum As Integer, CBname As String
Dim CBctrlNum As Integer
CBnum = InputBox("Enter CommandBar Index No.")
CBctrlNum = InputBox("Enter CommandBarCtrl ID No.")
On Error Resume Next
For Each CBctrl In Application.CommandBars.Item(CBnum).Controls
If CBctrl.ID = CBctrlNum Then
CBctrl.Visible = True
End If
Next
End Sub

 

[OMER]

Hola Kevin, thanks for your prompt response.

I tried your solution and it didn't hide any of the options in the View menu.
It did hide options in the Home, Insert, and Data menu options. The other
options remain open for the user to modify them.

This is what I entered:

Sub HideCBcontrols()
'
' Hide Formula Bar preventing user to activate it back
'
Dim CB As CommandBar, CBctrl As CommandBarControl
Dim CBnum As Integer, CBname As String
Dim CBctrlNum As Integer
CBnum = 1
CBctrlNum = 30004
On Error Resume Next
For Each CBctrl In Application.CommandBars.Item(CBnum).Controls
If CBctrl.ID = CBctrlNum Then
CBctrl.Visible = False
End If
Next
End Sub

Am I missing something?
OMER

 

[Kevin]

Could it be different versions. I'm using Excel 2003.

I got the following:

Col A Col B Col C
Worksheet Menu Bar 30002 &File
30003 &Edit
30004 &View
30005 &Insert
30006 F&ormat
30007 &Tools
30011 &Data
30083 A&ction
1 Show&Case
30009 &Window
30010 &Help
1 Ado&be PDF

From the code:

Sub ListCBcontrols()
Dim CBctrl As CommandBarControl, RowNum As Integer
RowNum = 1
For Each CBctrl In Application.CommandBars.Item(1).Controls
Cells(1, 1) = Application.CommandBars.Item(1).Name
Cells(RowNum, 2) = CBctrl.ID
Cells(RowNum, 3) = CBctrl.Caption
RowNum = RowNum + 1
Next
End Sub

 

[OMER]

That is it.

I'm using Excel 2007, any ideas for that version?

Regards,
OMER

 

[Kevin]

Try this. Use the following code to determine the ID numbers associated with
the View and FormulaBar controls.

Sub List_cbCtlCtl()
Dim cbCtlCtl As CommandBarControl
Dim cbCtl As CommandBarControl
Dim cbBar As CommandBar
Dim i As Integer
i = 1
For Each cbBar In CommandBars
If cbBar.Name = "Worksheet Menu Bar" Then
For Each cbCtl In cbBar.Controls
If cbCtl.Type = 10 Then 'msoControlPopup control type
For Each cbCtlCtl In cbCtl.Controls
Cells(i, 1) = cbBar.Name
Cells(i, 2) = cbCtl.Caption
Cells(i, 3) = cbCtl.ID
Cells(i, 4) = cbCtlCtl.Caption
Cells(i, 5) = cbCtlCtl.ID
i = i + 1
Next
End If
Next cbCtl
End If
Next cbBar
Range("A:E").EntireColumn.AutoFit
End Sub

Find the View ID number in column C. Find the FormulaBar ID number in
column E. Substitute these numbers in the following code.

Sub Disable_cbCtlCtl()
Dim cbCtlCtl As CommandBarControl
Dim cbCtl As CommandBarControl
Dim cbBar As CommandBar
Application.DisplayFormulaBar = False
For Each cbBar In CommandBars
If cbBar.Name = "Worksheet Menu Bar" Then
For Each cbCtl In cbBar.Controls
If cbCtl.ID = <place View ID number here> Then
For Each cbCtlCtl In cbCtl.Controls
If cbCtlCtl.ID = <place FormulaBar ID number here>
Then
cbCtlCtl.Enabled = False
cbCtlCtl.Visible = False
End If
Next
End If
Next cbCtl
End If
Next cbBar
End Sub

 

[Kevin]

Better yet, just run the following. No need to determine control ID numbers.

Sub DisableFormulaBar()
Dim cbCtlCtl As CommandBarControl
Dim cbCtl As CommandBarControl
Dim cbBar As CommandBar
Application.DisplayFormulaBar = False
For Each cbBar In CommandBars
If cbBar.Name = "Worksheet Menu Bar" Then
For Each cbCtl In cbBar.Controls
If cbCtl.Caption = "&View" Then
For Each cbCtlCtl In cbCtl.Controls
If cbCtlCtl.Caption = "&Formula Bar" Then
cbCtlCtl.Visible = False
Next
End If
Next cbCtl
End If
Next cbBar
End Sub

 

[OMER]

Thnak you Kevin.
I included this code and it actually hides the formula bar.
There is a big BUT though. The user can still go to the View menu and
activate it back, overrinding the command.

There should be a way to actually prevent the user to override this setting
while the workbook is open, but can't find it yet.

Regards,
Oscar

PS. This command has a similar effect on the formula Bar, but again, the
user can still modify it.

Application.DisplayFormulaBar = False

 

[Kevin]

Could be another difference between 2003 and 2007. After running this code
in 03 the Formula Bar option is not visible in the View menu. Have your
confirmed that the caption is indeed "&Formula Bar"