I am writing a program that takes information a user enters (string) and
uses it to create a sheet with that name. So far it has been extremely
unreliable, working one minute and then not the next. The variable is
marked as a string, so it "shouldn't" be looking for the worksheet by
number. Oh, the information in the variable is a number. Ok, so it
looks for the worksheet by name using the variable. If it can't find
it, the error causes it to create a new worksheet with that name. But,
the darn program keeps making "SheetX" with X representing the next
sheet number (1,2,3,4,5 etc). It has actually worked a couple of times,
but apparently is unstable. The variable is a public string since it is
used in two or three routines. Would making it a private variable and
passing it just between these routines help? My other worksheet
function that activates a worksheet and unhides it works just fine
using a private string.
Craig Robson


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Craig,

Post the code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Calypsoblur" wrote in message
...

I am writing a program that takes information a user enters (string) and
uses it to create a sheet with that name. So far it has been extremely
unreliable, working one minute and then not the next. The variable is
marked as a string, so it "shouldn't" be looking for the worksheet by
number. Oh, the information in the variable is a number. Ok, so it
looks for the worksheet by name using the variable. If it can't find
it, the error causes it to create a new worksheet with that name. But,
the darn program keeps making "SheetX" with X representing the next
sheet number (1,2,3,4,5 etc). It has actually worked a couple of times,
but apparently is unstable. The variable is a public string since it is
used in two or three routines. Would making it a private variable and
passing it just between these routines help? My other worksheet
function that activates a worksheet and unhides it works just fine
using a private string.
Craig Robson


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

PumpNumber is the Public string variable. The purpose of the routine is
just to activate the sheet so that I can enter information about the
pump into it.



'Checks for sheet PumpSheet and creates it if it does
'not exist.

Public Sub NewSheet()

'If error is created then continues
On Error Resume Next

'Tries to activate the sheet PumpSheet
Sheets(PumpNumber).Activate

'Creates the sheet PumpSheet
If Err < 0 Then
Sheets.Add
ActiveSheets.Name = PumpNumber
'Enters header information
Range("A1") = "Number"
Range("B1") = "Hours"
Range("C1") = "Date"
Range("D1") = "Lugs"

End If

'Restores normal error handling
On Error GoTo 0

End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Try it like this:

'PumpNumber is the Public string variable. The purpose of the routine is
'just to activate the sheet so that I can enter information about the
'pump into it.

Dim PumpNumber As Long

Sub test()
For i = 5 To 10
PumpNumber = i
NewSheet
Next 'i
End Sub

'Checks for sheet PumpSheet and creates it if it does
'not exist.

Public Sub NewSheet()

'If error is created then continues
On Error Resume Next

'Tries to activate the sheet PumpSheet
Sheets(PumpNumber).Activate

'Creates the sheet PumpSheet
If Err < 0 Then
Set xSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
With xSheet
.Name = PumpNumber
'Enters header information
.Range("A1") = "Number"
.Range("B1") = "Hours"
.Range("C1") = "Date"
.Range("D1") = "Lugs"
End With

End If

'Restores normal error handling
On Error GoTo 0

End Sub

Regards

Trevor


"Calypsoblur" wrote in message
...

PumpNumber is the Public string variable. The purpose of the routine is
just to activate the sheet so that I can enter information about the
pump into it.



'Checks for sheet PumpSheet and creates it if it does
'not exist.

Public Sub NewSheet()

'If error is created then continues
On Error Resume Next

'Tries to activate the sheet PumpSheet
Sheets(PumpNumber).Activate

'Creates the sheet PumpSheet
If Err < 0 Then
Sheets.Add
ActiveSheets.Name = PumpNumber
'Enters header information
Range("A1") = "Number"
Range("B1") = "Hours"
Range("C1") = "Date"
Range("D1") = "Lugs"

End If

'Restores normal error handling
On Error GoTo 0

End Sub


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/