Help with vba code placement please

[Get_Involved]

I have been trying to get this to work in cell "Q4" all morning.
I have put it every where in thisworkbook, Legend, and in a new module.
Cant get it to work in cell "Q4" of the Legend.
This is the formula I am useing.

 Private Sub Workbook_Open()
Sheets("Sheet1").Range("Q4").Value = CInt(Format(Date, "ww", 1))
End Sub
Can someone help with advise please?
Thanks in advance

 

[shg]

It goes in ThisWorkbook, and you have to save as a macro-enabled file, and then enable macros.

 

[Get_Involved]

shg:
I already have already done that several times, and this is the "Compile error" I receive. Ambiguous name detected Workbook_Open.
So I know I am doing something wrong.
I even created a new micro enabled file and enabled macros.
Thanks for the quick reply

 

[Joe4]

Ambiguous name detected Workbook_Open

That usually implies that you have two procedures named "Workbook_Open" in a single module, which is not allowed.
You cannot have multiple procedures by the same name in a module.
If they are performing two different tasks, then you will need to combine them in a single "Workbook_Open" event procedure.

 

[Get_Involved]

Joe4:
Thanks for the reply.
I figured that is what I am doing, I have done that also, that means I am not putting it in proper way.
This is how it starts

 Private Sub Workbook_Open()
 Dim Ans As String
 Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
 If Ans = vbYes

Then and there is 18 rows of code down.
all of that is working.
Q4 is my problem, with placement of the weeknum.
hope I explained it clear enough

 

[Joe4]

You would just have two "blocks" of code within your Workbook_Open event procedure, i.e.

 Private Sub Workbook_Open()
 
[COLOR=#0000ff] '  Block 1[/COLOR]
[COLOR=#0000ff]    Dim Ans As String[/COLOR]
[COLOR=#0000ff]    Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)[/COLOR]
[COLOR=#0000ff]    If Ans = vbYes Then[/COLOR]
[COLOR=#0000ff]        ...[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
    
[COLOR=#ff0000]'   Block 2[/COLOR]
[COLOR=#ff0000]    Sheets("Sheet1").Range("Q4").Value = CInt(Format(Date, "ww", 1))[/COLOR]
[COLOR=#ff0000]      [/COLOR]
 End Sub

 

[Get_Involved]

Thanks Joe4:
Sorry for the delay of response.
Works fine.