Macro not doing?

[Sandy]

The following doesn,t work and I have no idea why - any suggestions?
I am assuming the code should run when the worksheet "Data Input" is
selected.

'Code in Sheet1 "Data Input"
Private Sub Worksheet_Activate()

Sheets("Data Input").Unprotect Password:="********"

ClearApplicationControls

Sheets("Data Input").Protect Password:="********"

End Sub

'Code in Module2
Sub ClearApplicationControls()

Application.ScreenUpdating = False

With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
.DisplayHeadings = False
.WindowState = xlMaximized
End With

Dim OneBar As CommandBar
On Error Resume Next
For Each OneBar In CommandBars
OneBar.Visible = False
Next
On Error GoTo 0

With Application
.DisplayFullScreen = False
.DisplayFormulaBar = False
End With

Application.ScreenUpdating = True

End Sub

 

[Barb Reinhardt]

Is it running at all? If not, you may have somehow set

Application.EnableEvents = FALSE

and not reset it back to TRUE.

In the Immediate window, thpe
Application.EnableEvents = TRUE

Go to another worksheet and then reactivate the DataInput sheet.

If this

Private Sub Worksheet_Activate()

Sheets("Data Input").Unprotect Password:="********"

ClearApplicationControls

Sheets("Data Input").Protect Password:="********"

End Sub


Is in the Data INput worksheet code, you don't need the Sheets("Data
Input"). part

 

[Joel]

You can't change visible to false it is read only, but you can disable. here
is the modified code. Had to make additional changes to prevent errors. I
also commented out on error so i could debug the problem

Dim OneBar As CommandBar
barcount = CommandBars.Count
' On Error Resume Next
For bars = 1 To barcount
CommandBars.Item(bars).Enabled = False
Next bars
' On Error GoTo 0

 

[Sandy]

Hi Barb
I can't explain this but it suddenly decided to do???
I'm unaware of having made any other change - Strange.
I'll take your point on board about not needing the Sheets("Data Input")
part though - thank you
Sandy

 

[Joel]

Sandy I don't think the OneBar.Visible = False is working. The On Error
statement is bypassing the statement and doing nothing.

 

[Sandy]

Hi Joel
You are right it isn't. Your solution does though - thank you.
I now have another minor problem - how do I then enable all that was
disabled. Otherwise all of these Toolbars remain out of commission in other
workbooks. The final code I used is as follows:

Sub ClearApplicationControls()

Application.ScreenUpdating = False

With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
.DisplayHeadings = False
.WindowState = xlMaximized
End With

Dim OneBar As CommandBar
barcount = CommandBars.Count
On Error Resume Next
For bars = 1 To barcount
CommandBars.Item(bars).Enabled = False
Next bars
On Error GoTo 0

With Application
.DisplayFullScreen = False
.DisplayFormulaBar = False
.CommandBars("Worksheet Menu Bar").Enabled = True
End With

Application.ScreenUpdating = True

End Sub

If I just set the "CommandBars.Item(bars).Enabled = True" then it fails. I'm
guessing something would be required within a "before_close" event?
Sorry to be a pest.
Sandy

 

[Joel]

It only change the toolbars on one worksheet. to reverse change
from
CommandBars.Item(bars).Enabled = False

to
CommandBars.Item(bars).Enabled = True