Hiding Formula Bar

[lapta301]

Dear All

I have a workbook with dozens of worksheets and need to hide the formula bar on about 6 of them - this will change from time to time.

Could some kind person provide a bit of code that would allow me to hard code the sheet names that need turning off with the bar being visible on all others.

As every my sincere thanks for any help that you can provide.

 

[Trevor G]

Place this on each worksheet code screen for when the sheet has been activated.

Application.DisplayFormulaBar = False

 

[lapta301]

Trevor

Thank you for your response
I was aware of that option but I was hoping for a bit of code in which I could add the sheet names to hide the formula bar once rather than place a bit of code on every sheet.

 

[Trevor G]

You can't hide the formula bar on specific sheets, and show on other sheets, the formula bar acts like a Boolean event, it is either on or off, therefore I would consider once activating a sheet then it either shows or hides.

 

[lapta301]

Trevor

Many thanks for your explanation. I had a worry that this would be the case.

Again my sincere thanks for taking the time to have a look at my question.

 

[GlennUK]

You can put the code in once place, in the ThisWorkBook code area, and it would look something like this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name = "Sheet11" Or _
        Sh.Name = "Sheet12" Or _
        Sh.Name = "Sheet9" Then
        Application.DisplayFormulaBar = False
    Else
        Application.DisplayFormulaBar = True
    End If
End Sub

 

[lapta301]

GlennUK

Great. Thats just what I was hoping for, makes it a lot more manageable

A minor issue that I can live without if its a problem, can the code refer to the internal sheet name (Sheet1) etc. rather than the text name (Budget) as occassionally other people and I suppose me as well change the tab name without realising the implications.

Whatever, my sincere thanks for your help

 

[GlennUK]

That's easy, just do it like this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name = Sheet11.Name Or _
        Sh.Name = Sheet12.Name Or _
        Sh.Name = Sheet9.Name Then
        Application.DisplayFormulaBar = False
    Else
        Application.DisplayFormulaBar = True
    End If
End Sub

 

[Trevor G]

It looks as though I forgot about the workbook.Activate sheet option.

You can use the real sheet names like this, event the tab name maybe different it uses the proper name for the sheet

Sub msg()
MsgBox "What name " & Sheets(1).Name
End Sub

 

[lapta301]

Glenn

That's great, should cover every possible situation from a central piece of code.

As always my sincere thanks

Steve