Re: Macro not doing?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

to
CommandBars.Item(bars).Enabled = True


"Sandy" wrote:

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("Work*** 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" <Joel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CC0AFF71-9A8A-479B-A8D9-C61A6B3CEF2E@xxxxxxxxxxxxxxxx
Sandy I don't think the OneBar.Visible = False is working. The On Error
statement is bypassing the statement and doing nothing.

"Sandy" wrote:

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

"Barb Reinhardt" <BarbReinhardt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:CCF5F346-8A27-480A-8C7E-A6EBEE76BF82@xxxxxxxxxxxxxxxx
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 work*** and then reactivate the DataInput ***.

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


"Sandy" wrote:

The following doesn,t work and I have no idea why - any suggestions?
I am assuming the code should run when the work*** "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









.

Quantcast