error in code

[des-sa]

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

 

[FSt1]

hi
what error are you getting????
is the name of the sheet 3 or sheet3?

regards
FSt1

 

[Rick Rothstein \(MVP - VB\)]

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 worksheet, 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 Sheet(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]

hi,
thanks for reply. its says "Run-time error 9 Subscribt out of range

 

[Rick Rothstein \(MVP - VB\)]

It sounds like your response was meant for FSt1's reply to you, not mine. I
didn't ask you what your error message was because, based on the line you
highlighted in your original post, that is exactly the error message I would
have guessed you had gotten. Did you try changing your code in one of the
ways I outlined to you?

Rick

 

[des-sa]

Rick,
i cant thank you enough, works perfect. thanks again.

i have another question: the template i use is now made up of a few sheets
that i developed on a stand alone work book basis, then copied the sheets
into the template. now when the template opens a message saying my workbook
contains links to other sources do i want to update etc, etc. those links
are now not necessary anymore, but how do i find them to destroy them
thanks again
disri