Help rewriting VB Code:

[Get_Involved]

How do I get this code to work?

Private Sub Workbook_Open()
 Dim Ans As String
 Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
 If Ans = vbYes Then
 
 With Sheets("Expense Report").Range("H4")
 .Value = .Value + 1
 
 With Sheets("Legend").Range("Q4")
 .Value = .Value + 1
 
 With Sheets("Legend").Range("R4")
 .Value = . Value + MAX('Bid Calculator'!C29:G29)

Range R4 is the one I am trying to get to work.

I have the code in "ThisWorkbook"

 

[shg]

Try

 .Value = . Value + [MAX('Bid Calculator'!C29:G29)]

 

[Get_Involved]

Thanks shg
That is starting to work the way it is writ it brings up a FALSE then when I take the + Value out it brings a TRUE.
But what it does on the "Bid Calculator" it bring a blank C29:G29.
Getting close except for the blank.

 

[shg]

Sorry, I don't follow that.

 

[Michael M]

you are missing 2 End Withs and 1 End If in your code, so it shouldn't produce any result !!
Try

Private Sub Workbook_Open()
 Dim Ans As String
 Ans = MsgBox("Update Invoice Number (???)", vbYesNo + vbInformation)
If Ans = vbYes Then
     With Sheets("Expense Report").Range("H4")
        .Value = .Value + 1
     End With
         With Sheets("Legend").Range("Q4")
            .Value = .Value + 1
         End With
     With Sheets("Legend").Range("R4")
        .Value = .Value + WorksheetFunction.Max(Sheets("Bid Calculator").Range("C29:G29"))
    End With
End If
End Sub

 

[Get_Involved]

Thanks Michael M

I redone the code it works.
Except when I update the work it doubles number to be placed in R4. Example 8 when I close workbook then open and click yes to update it doubles to 16.

 

[Michael M]

Maybe you should revisit this part....

With Sheets("Legend").Range("R4")
        .Value = .Value + WorksheetFunction.Max(Sheets("Bid Calculator").Range("C29:G29"))
    End With

without seeing the data, it might need something like

With Sheets("Legend").Range("R4")
        .Value = WorksheetFunction.Max(Sheets("Bid Calculator").Range("C29:G29"))+1
End With

OR

With Sheets("Legend").Range("R4")
        .Value = .value+1
End With

 

[Get_Involved]

The Bid Calculator has 5 columns C29:G29 C29 starts with the number in the legend at R4. D29:G29 increases by 1.
I am trying to get G29 to be placed back into R4 of the Legend after updating.
Hope this makes sense.
Thanks

 

[Michael M]

This then ???

With Sheets("Legend").Range("R4")
        .Value = WorksheetFunction.Max(Sheets("Bid Calculator").Range("C29:G29"))
End With

 

[Get_Involved]

Michael M
I went back to the original code.
Had it working but it was only was giving me the Lesser rather than the max number.
There has to be a code to do it. I'll find it one day
Thanks for your help