Advice re VBA coding

  oo7juk 10:04 24 Dec 2007
Locked

Hi,

I have a workbook that contains a few macros, but one of them is working, but not properly.

The first macro I have is a module that removes all the sheet tabs, headings, formula bars etc, and the other macro reverts back to a particular sheet. The line of code is the same and I'm thinking that might be causing the problems. When the first macro runs all the sheets run ok apaprt from one which still displays the headings.

Here are the 2 macros:

Sub Auto_Open()
Dim cBar As CommandBar
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
Application.DisplayAlerts = False
End Sub
Sub Auto_Close()
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
Application.DisplayAlerts = True
End Sub

2nd macro is within the ThisWorkbook tab

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.ScrollArea = "a1:L125"
Sheets("Sheet3").Activate
Next ws
End Sub

I think the problem/conflict maybe here

Private Sub CommandButton2_Click()
Sheets("Sheet3").Activate
End Sub

Above is the code for my command button which is the same sheet that is still displaying the headings.

Many thanks,

  VoG II 16:22 24 Dec 2007

One problem is that the Workbook_Open sub is activating Sheet3 within the loop which is unnecessary. This would be better

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.ScrollArea = "A1:L125"
Next ws
Sheets("Sheet3").Activate
End Sub


But the other problem is that you are using Auto_Open and Auto_Close subs which are OK but clunky code (that I think I provided, so guilty as charged). Really you should have all of the Open and Close code in Workbook Events like these - these are accessed by right clicking the Excel logo just to the left of File on the menu bar and selecting View Code:

Private Sub Workbook_Open()
Dim ws As Worksheet, cBar As CommandBar
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
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
ws.ScrollArea = "A1:L125"
Next ws
Sheets("Sheet3").Activate
End Sub

Private Sub Workbook_Close()
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
Application.DisplayAlerts = True
End Sub


then delete the existing Auto_Open and Auto_Close subs.

  oo7juk 10:03 08 Jan 2008

VoG,

Tried your last code, but it generated the following error -

Run time error '424' object required

debug - For Each cBar In CommandBars

  VoG II 10:31 08 Jan 2008

You are right - I think the macro is trying to run before there are any menus/toolbars to disable. However it does work if we go back to the old (Excel 5) approach of using Auto_Open and Auto_Close macros. So delete all of the code from the ThisWorkbook module then enter the following in a regular module:

Private Sub Auto_Open()
Dim ws As Worksheet, cBar As CommandBar
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
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
ws.ScrollArea = "A1:L125"
Next ws
Sheets("Sheet3").Activate
End Sub

Private Sub Auto_Close()
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
Application.DisplayAlerts = True
End Sub

  oo7juk 10:08 10 Jan 2008

VoG,

I inserted your last code and it worked ok, but when I open VB and try to select the macro there is no macro there, unfortunately that means I can't open and update sheets, thanks.

  oo7juk 10:08 10 Jan 2008

VoG,

I inserted your last code and it worked ok, but when I open VB and try to select the macro there is no macro there, unfortunately that means I can't open and update sheets, thanks.

  oo7juk 10:08 10 Jan 2008

VoG,

I inserted your last code and it worked ok, but when I open VB and try to select the macro there is no macro there, unfortunately that means I can't open and update sheets, thanks.

  VoG II 10:24 10 Jan 2008

If you mean that you want to stop the macro from running so that you can make changes to the workbook:

Open a blank workbook, Tools > Macro > Security and set the level to High. Then open the workbook that you want to change. After changing it, set the macro security to Low.

  oo7juk 10:36 10 Jan 2008

VoG,

I inserted your last code and it worked ok, but when I open VB and try to select the macro there is no macro there, unfortunately that means I can't open and update sheets, thanks.

  oo7juk 10:43 10 Jan 2008

Vog,

The first macro would run automatically and when I select alt+f11 it gave me the option to close or open macro. I would select my option then close the macro and select what sheets to update I would then run macro again and save changes.

Thanks. Apologies fir duplicate posts don't know what happened there.

  VoG II 11:00 10 Jan 2008

Ah!

You mean that the macros don't appear in the list of macros? Delete the word Private at the top of each one.

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

Elsewhere on IDG sites

OnePlus 5 review

See the work of famous artists playing with toys

iPad Pro 10.5in (2017) review

Comment faire une capture d’écran sur un Mac ?