VBA/Macro problems!

[Antonio12]

Hello everybody. I'm as new to this forum as I am to excel VBA/Macros and I really need help from a pro.
I use Excel 2007 and run Windows XP.

The file has several sheets and works a lot with hyperlinks. The file needs to be protected because I cant have people making changes to it.

I have 2 problems that I would very much appreciate if someone could help me with.

I need for the file to open in the same sheet every time someone opens it regardless of if when they closed it they saved in another sheet. I created a sheet called HOME and I need the file to always open to that sheet. I tried the following but it doesn't work.

Private Sub Workbook_Open()
Worksheets("HOME").Activate
End Sub

I don't know where I have to place this code in the VBA project. Module/The Sheet/ThisWorkbook. Can anybody tell me why this is not working please.

The other problem is that since the file is protected, I can't group and Ungroup. I tried the following code:

Private Sub Workbook()
With Sheet2
.Protect Password:="Antonio123", UserInterfaceOnly:=True
.EnableOutlining = True
.EnableAutoFilter = True
.EnableCalculation = True
.EnableFormatConditionsCalculation = True
.EnableOutlining = True
.EnableSelection = xlLockedCells
End With
End Sub

Again, I don't know if I have to paste this in Module/The Sheet/ThisWorkbook.

The interesting thing is that this code sort of works. When I open the file it doesn't work even if I press enable macros, but if after I go to the VBA and on the sheet where the code is I press the play Icon on the toolbar above the code, it works.


Can one of you Excel geniuses help me out please!!!

Thank you and sorry for the long text, but don't know how else to explain this.

 

[Andrew Poulsom]

Welcome to MrExcel.

Put all your code in the Workbook_Open event procedure and put that procedure in the ThisWorkbook module.

 

[dimexi]

I can help with the first bit.
To ensure that the workbook ALWAYS opens in home, you have to paste that sub in "ThisWorkbook". Any Macros placed in that area automatically execute when you open the workbook

Edit: oops, someone already posted that with more detail

 

[ZAX]

Hi Antonio12,

I can solve your problems in one code ,All you need to do is to enter the following code in "This Workbook".

Private Sub Workbook_Open()
With Sheet2
.Protect Password:="Antonio123", UserInterfaceOnly:=True
.EnableOutlining = True
.EnableAutoFilter = True
.EnableCalculation = True
.EnableFormatConditionsCalculation = True
.EnableOutlining = True
.EnableSelection = xlLockedCells
End With
Sheets("HOME").Select
End Sub

ZAX

 

[Antonio12]

Welcome to MrExcel.

Put all your code in the Workbook_Open event procedure and put that procedure in the ThisWorkbook module.

Thank you for the quick response. For the first part, I placed the code in ThisWorkbook and it worked fine. The file opened in another sheet, but once I enabled the macros, it went to the home sheet as I wanted.

For the second part, I'm pretty knew (since yesterday actually) to VBA so I dont really understand what you mean by "Put all your code in the Workbook_Open event procedure". Can you please be more especific. Thank you

 

[Antonio12]

Hi Antonio12,

I can solve your problems in one code ,All you need to do is to enter the following code in "This Workbook".

Private Sub Workbook_Open()
With Sheet2
.Protect Password:="Antonio123", UserInterfaceOnly:=True
.EnableOutlining = True
.EnableAutoFilter = True
.EnableCalculation = True
.EnableFormatConditionsCalculation = True
.EnableOutlining = True
.EnableSelection = xlLockedCells
End With
Sheets("HOME").Select
End Sub

ZAX

Thank you so much. It works. But just 1 more question. I need the group and ungroup to work for several sheets. Should I just re enter the code below this one and change the "Sheet2" for "Sheet3" and so on?..

Thank you

 

[dimexi]

Just for reference: I think what Andrew means by workbook_open procedure is:
In the VBE, double click "ThisWorkbook"
in the editor window, you see two dropdown bars, (General) and (Declaration)

Change (General) to (Workbook)
and
(Declaration) to (Open)

Then you automatically get your

Private Sub WorkBook_Open()

End Sub

added for you.

Then you can paste your code in here.

I think this is what he means

 

[Antonio12]

Thank you very much. You are all extremely cool (and super smart)!

Cheers for the help.