Re: error in code

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

With ThisWorkbook.Sheets("3") (what could be wrong here?)

There is one of two things wrong with that statement (as long as it is the statement generating your error). When you use quote marks around the argument, the text between the quote marks has to be the text on the worksheet's tab. So, if you worksheet is really named Sheet3 (the default name given it by Excel), then your statement should read....

With ThisWorkbook.Sheets("Sheet3")

On the other hand, if you are actually trying to reference the 3rd tab from the left's work***, then you do not use the quote marks, so your statement would be this...

With ThisWorkbook.Sheets(3)

One thing to point out with this second version... you users can slide the tabs around (assuming no protection is in place) so ***(3) will be whatever is currently in the 3rd tab from the left when the statement is executed (which could theoretically be different than what you are expecting it to be).

Rick


"des-sa" <dessa@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:0A847576-8F7C-40AB-9C50-16A5EBBF4336@xxxxxxxxxxxxxxxx
hi,
could anyone tell me what could possibly be wrong in my code - see remark
below? i keep on getting an error message
thanks

Private Sub Workbook_Open()
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "QUOTE1"
Const sKEY As String = "QUOTE1_key"
Const nDEFAULT As Long = 1&
Dim nNumber As Long

With ThisWorkbook.Sheets("3") (what could be wrong here?)
With .Range("F9")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
With .Range("K2")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY,
nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "0000")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&
End If
End With
End With
End Sub

.

Quantcast