Workbook_open event on multiple sheets

[xL_challenged]

I'm attempting a workbook_open event that runs on two sheets. When I push the "run" button in VBA the code works fine, but I can't get it to initiate when I open the workbook. And I'm not smart. Can you help? Here's what Ive got in "thisworkbook" module:

Private Sub workbook_open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
If [A1] = "" Then
.Unprotect Password:="secret"
.[A1] = DateValue(Date)
.Protect Password:="secret"
End If
End With
Next ws
End Sub

 

[dk]

Hi
You have not qualified the cell in your If statement so that would probably cause problems i.e.

If .[A1] = "" Then

rather than this

If [A1] = "" Then

But that would cause problems regardless of whether you're running it manually or running automatically via the Workbook_Open. Anyway, let us know if this fixes your problem.

HTH
DK

 

[xL_challenged]

That didn't do it. Could it have anything to do with what I have in other modules?

Thanks for the reply.

 

[dk]

Hmm, that's odd - there is no matching End If for one of the If statements but it seems to be compiling....anyway, can you try this instead:

Private Sub workbook_open()
    Dim ws As Worksheet
        
    MsgBox "Workbook_Open"
    
    For Each ws In ThisWorkbook.Worksheets
        With ws
            If .[A1] = "" Then
                .Unprotect Password:="secret"
                .[A1] = DateValue(Date)
                .Protect Password:="secret"
            End If
        End With
    Next ws
End Sub

 

[xL_challenged]

That didn't run when I opened the workbook. when I ran it manually it gave me a run-time 1004 error.

Where do I need another End If statement?

 

[dk]

Don't worry - I had put in another If statement myself - your Ifs are fine!

What is strange is that your Workbook_Open procedure is not opening. I know that Excel 2000 had a problem with these sometimes - are you on that version? What I'd suggest next is to move your Workbook_Open procedure into the Autpen procedure in a standard module i.e. delete the Workbook_Open procedure and add in this to a standard module - does that work?

Private Sub Auto_Open()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        With ws
            If .[A1] = "" Then
                .Unprotect Password:="secret"
                .[A1] = DateValue(Date)
                .Protect Password:="secret"
            End If
        End With
    Next ws
End Sub

 

[xL_challenged]

I had been trying auto_open as well, but I didn't have that period (what'd you call it) before the cell number. Looks like that did it.

Thanks a lot. I had to take a break before I did something regrettable.