Need to run a VB code on selected worksheets only

  oo7juk 10:44 18 Apr 08
Locked

Hi,

I have a workbook containing 5 sheets. Within this wb I have a module with a 'General Auto Open - Auto Close code. This code has a few features, but the main feature is it removes tabs, scroll bars etc.

I only want this code to run on two worksheets (Sheet4 and Sheet5).

The other sheets 1,2 and 3 I want left as default so I can edit as normal.

I right clicked sheets 1,2 and 3 and inserted the following code -

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cBar As CommandBar
ThisWorkbook.Protect Windows:=False
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
.DisplayHeadings = True
End With
With Application
.DisplayFormulaBar = True
.DisplayStatusBar = True
End With
For Each cBar In CommandBars
cBar.Enabled = True
Next cBar
Beep
End Sub

It did work, but when I saved and restarted it just reverted back to the code function on sheets 4 and 5.

Many thanks.

  VoG II 12:34 18 Apr 08

Wow - that is pretty 'expensive' code as it will be triggered everytime you change the selection.

I suggest that you get rid of the sheet code. Then right click the Excel logo just to the left of File on the menu bar and select View Code. Paste in


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim cBar As CommandBar
If Sh.Name = "Sheet4" Or Sh.Name = "Sheet5" Then
ThisWorkbook.Protect Windows:=True
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
.DisplayHeadings = False
End With
With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With
For Each cBar In CommandBars
cBar.Enabled = False
Next cBar
Beep
Else
ThisWorkbook.Protect Windows:=False
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
.DisplayHeadings = True
End With
With Application
.DisplayFormulaBar = True
.DisplayStatusBar = True
End With
For Each cBar In CommandBars
cBar.Enabled = True
Next cBar
Beep
End Sub

then close the code window. This code will only run when you select a worksheet.

  oo7juk 20:08 18 Apr 08

VoG,

removed code from sheets and inserted new code, but generated the following:

Complie Error

Block If Without End If

Regards,

  VoG II 22:02 18 Apr 08

Apologies - I omitted an End If right at the end


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim cBar As CommandBar
If Sh.Name = "Sheet4" Or Sh.Name = "Sheet5" Then
ThisWorkbook.Protect Windows:=True
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
.DisplayHeadings = False
End With
With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With
For Each cBar In CommandBars
cBar.Enabled = False
Next cBar
Beep
Else
ThisWorkbook.Protect Windows:=False
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
.DisplayHeadings = True
End With
With Application
.DisplayFormulaBar = True
.DisplayStatusBar = True
End With
For Each cBar In CommandBars
cBar.Enabled = True
Next cBar
Beep
End If
End Sub

  oo7juk 22:53 20 Apr 08

Generating a run time error '424' object required.

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…