Run on Opening Workbook Q

[Sean]

I have the following procedure run when I open up a particular file,
which will display for me the user name within Excel. I have the sheet
protected etc so it can't be tampered with. But I'm hitting a Compile,
I just don't understand these With, End With's

Private Sub Workbook_Open()
For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Unprotect Password = "1234"
Cells(6, 3).Value = Application.UserName
sh.Protect Password = "1234"
End Sub

 

[Chip Pearson]

Sean,

You're missing the Next Sh statement that is required by the For Each loop.
Moreover, the line of code

Cells(6, 3).Value = Application.UserName

always points to the ActiveSheet, not the SH sheet. A For Each loop does NOT
activate the sheets as it iterates through them. It simply sets its
reference variable to each sheet.

You will also find life easier if you properly indent your code. It makes it
MUCH easier to read and spot problems. You can use Stephen Bullen's Smart
Indenter if you want to format existing code
(http://www.oaltd.co.uk/Indenter/Default.htm). With properly indented code,
you'll immediately be able to spot a missing Next or End With.



Private Sub Workbook_Open()
Dim SH As Worksheet
On Error Resume Next
For Each SH In ActiveWorkbook.Worksheets
SH.Unprotect Password = "1234"
SH.Cells(6, 3).Value = Application.UserName
SH.Protect Password = "1234"
Next SH
End Sub

Or, using a With statement:

Private Sub Workbook_Open()
Dim SH As Worksheet
On Error Resume Next
For Each SH In ActiveWorkbook.Worksheets
With SH
.Unprotect Password = "1234"
.Cells(6, 3).Value = Application.UserName
.Protect Password = "1234"
End With
Next SH
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Gord Dibben]

Sean

Need a Next when you have a For

Private Sub Workbook_open()
For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
With sh
.Unprotect Password = "1234"
.Cells(6, 3).Value = Application.UserName
.Protect Password = "1234"
End With
Next sh
End Sub


Gord Dibben MS Excel MVP

 

[Sean]

Chip I'm trying to 'manually' unprotect the sheet and it doesn't
recognise the Password, yet its the exact same one thats in the
ThisWorkbook to unprotect - is there something funny happening here? I
only have one sheet in my whole workbook. I've triued caps lock on /
off and still I can't access

Thanks for the Tip on the indent