trouble adding a name...

[Mark Kubicki]

When I open a workbook, I have code to check all of the worksheets to see if
they have already been "formatted", if not I run code to do so... (new
worksheets are added to the workbook by an external program, and need to be
reformatted into a legible worksheet)
A range name is added to each worksheet to flag it as having been
reformatted (ReformattingComplete = True...) The new sheets do not have the
name; it is added by code (which is where i might be having my problem....

On a new sheet, with no range named (ReformattingComplete) it gets stuck in
an endless loop at the error handler...

any suggestions would be much appreciated,
mark


Private Sub Workbook_Open()
For Each Sheet In Worksheets
If Sheet.Name <> "QuickBooks Export Tips" _
And Sheet.Name <> "Billing Rates" _
And Sheet.Name <> "Project Upset" Then
Worksheets(Sheet.Name).Activate
ReformatWorksheet 'call subroutine to verify formatting
status
End If
Next Sheet
End Sub

------------------------------------------------
Sub ReformatWorksheet()
On Error GoTo errNoNameFound ' new sheets will not have a named range
"ReformatingCompleted"
If ActiveWorkbook.ActiveSheet.Names("ReformatingCompleted").Value =
"=TRUE" Then
Exit Sub
End If
FormatandAddFormulas 'subrouting to reformat sheet is called
....
errNoNameFound: ' Error-handling routine
ActiveWorkbook.ActiveSheet.Names.Add Name:="ReformatingCompleted",
RefersToR1C1:=False 'add named reange and assign false value

 

[Peter T]

Have a go with the following -

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case "QuickBooks Export Tips", "Billing Rates", "Project Upset"
Case Else
ReformatWorksheet ws
End Select
Next ws
End Sub

Sub ReformatWorksheet(ws As Worksheet)
Dim nm As Name
On Error Resume Next
Set nm = ws.Names("ReformatingCompleted")
If nm Is Nothing Then
Set nm = ws.Names.Add("ReformatingCompleted", False)

ElseIf UCase$(nm.Value) = "=TRUE" Then
Exit Sub
End If
On Error GoTo errNoNameFound

FormatandAddFormulas ws ' pass the worksheet object
nm.Value = True

Exit Sub

errNoNameFound: ' Error-handling routine
If Not nm Is Nothing Then
nm.Value = False
Else
' shouldn't get this
MsgBox Err.Description
End If

End Sub

Sub FormatandAddFormulas(ws As Worksheet)
ws.Range("A1").Interior.ColorIndex = 6

End Sub

I'd suggest you put ReformatWorksheet & FormatandAddFormulas in a normal
module then you can use the same routines, don't forget to pass a reference
to the worksheet, eg ws or activesheet or worksheets("sheetname").

Regards,
Peter T