ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Macro not doing? (https://www.excelbanter.com/excel-programming/390071-macro-not-doing.html)

Sandy
Macro not doing?
 
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
Macro not doing?
 
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


"Sandy" wrote:

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




joel
Macro not doing?
 
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" wrote:

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




Sandy
Macro not doing?
 
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" wrote in message
...
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


"Sandy" wrote:

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

joel
Macro not doing?
 
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" wrote in message
...
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


"Sandy" wrote:

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

Sandy
Macro not doing?
 
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" wrote in message
...
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" wrote in
message
...
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


"Sandy" wrote:

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

joel
Macro not doing?
 
It only change the toolbars on one worksheet. 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("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" wrote in message
...
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" wrote in
message
...
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


"Sandy" wrote:

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


All times are GMT +1. The time now is 04:58 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com