ActiveWorkbook.Names.Add Name

Why does this statement fail at the first ActiveWokrbook.Names.Add?  These statements worked flawlessly until I broke them out into a sub routine.

    Range("A3").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "ProjectName", RefersToR1C1:="=Sheet1!R3C1"
    ActiveCell.Offset(18, 6).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Apr", RefersToR1C1:="=Sheet1!R21C7"
    ActiveCell.Offset(0, 5).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "May", RefersToR1C1:="=Sheet1!R21C12"
    ActiveCell.Offset(0, 6).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Jun", RefersToR1C1:="=Sheet1!R21C18"
    ActiveCell.Offset(0, 5).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Jul", RefersToR1C1:="=Sheet1!R21C23"
    ActiveCell.Offset(0, 5).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Aug", RefersToR1C1:="=Sheet1!R21C28"
    ActiveCell.Offset(0, 6).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Sep", RefersToR1C1:="=Sheet1!R21C34"
    ActiveCell.Offset(0, 5).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Oct", RefersToR1C1:="=Sheet1!R21C39"
    ActiveCell.Offset(0, 5).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Nov", RefersToR1C1:="=Sheet1!R21C44"
    ActiveCell.Offset(0, 6).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Dec", RefersToR1C1:="=Sheet1!R21C50"
    ActiveCell.Offset(0, -49).Range("A1").Select

I appreciate the second pair of eyes.

Bill
cowboywmAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

maybe you have to give the Form reference

myForm.SBINumberTextBox.Text

Regards
0
 
cowboywmAuthor Commented:
Not sure what the form reference does or where to place it.  Here are the macros that run based upon if statements.  If I dispense with individual macros and place all lines of code between the  appropriate if statements, then the procedure executes fine.  The drawback is that the procedure seems rather lengthy.

Private Sub EnterButton_Click()
    Add_New_Sheet
    If ActiveSheet.Name = "Sheet1" Then
        Template_Copy_1
        Create_Name_References_1
    ElseIf ActiveSheet.Name = "Sheet2" Then
        Template_Copy_2
        Create_Name_References_2
    ElseIf ActiveSheet.Name = "Sheet3" Then
        Template_Copy_3
        Create_Name_References_3
    ElseIf ActiveSheet.Name = "Sheet4" Then
        Template_Copy_4
        Create_Name_References_4
    ElseIf ActiveSheet.Name = "Sheet5" Then
        Template_Copy_5
        Create_Name_References_5
    End If
    Add_Project_Name
    Control_Scrolling
    If ActiveSheet.Name = "Sheet1" Then
        New_Name_For_WS1
    ElseIf ActiveSheet.Name = "Sheet2" Then
        New_Name_For_WS2
    ElseIf ActiveSheet.Name = "Sheet3" Then
        New_Name_For_WS3
    ElseIf ActiveSheet.Name = "Sheet4" Then
        New_Name_For_WS4
    ElseIf ActiveSheet.Name = "Sheet5" Then
        New_Name_For_WS5
    End If
    Prepare_Summary_Sheet
    Unload ProjectNameForm
End Sub

Sub Add_Next_Project()
    ProjectNameForm.Show
End Sub

Sub Add_New_Sheet()
    Sheets("NewEntryTemplate").Select
    Sheets.Add
End Sub

Sub Template_Copy_1()
    Sheets("NewEntryTemplate").Select
    Cells.Select
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveSheet.Paste
    Sheets("NewEntryTemplate").Select
    Application.CutCopyMode = False
    Range("A1").Select
    Sheets("Sheet1").Select
    Range("A1").Select
End Sub

Sub Create_Name_References_1()
    Range("A3").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "ProjectName", RefersToR1C1:="=Sheet1!R3C1"
    ActiveCell.Offset(18, 6).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Apr", RefersToR1C1:="=Sheet1!R21C7"
    ActiveCell.Offset(0, 5).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "May", RefersToR1C1:="=Sheet1!R21C12"
    ActiveCell.Offset(0, 6).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Jun", RefersToR1C1:="=Sheet1!R21C18"
    ActiveCell.Offset(0, 5).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Jul", RefersToR1C1:="=Sheet1!R21C23"
    ActiveCell.Offset(0, 5).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Aug", RefersToR1C1:="=Sheet1!R21C28"
    ActiveCell.Offset(0, 6).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Sep", RefersToR1C1:="=Sheet1!R21C34"
    ActiveCell.Offset(0, 5).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Oct", RefersToR1C1:="=Sheet1!R21C39"
    ActiveCell.Offset(0, 5).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Nov", RefersToR1C1:="=Sheet1!R21C44"
    ActiveCell.Offset(0, 6).Range("A1").Select
    ActiveWorkbook.Names.Add Name:="_" & SBINumberTextBox.Text & "Dec", RefersToR1C1:="=Sheet1!R21C50"
    ActiveCell.Offset(0, -49).Range("A1").Select
End Sub

Sub Add_Project_Name()
    Application.Goto Reference:="_" & SBINumberTextBox.Text & "ProjectName"
    ActiveCell.FormulaR1C1 = ProjectNameTextBox.Text
End Sub

Sub Control_Scrolling()
    Range("A1").Select
    ActiveCell.Offset(2, 2).Range("A1").Select
    ActiveWindow.FreezePanes = True
End Sub

Sub New_Name_For_WS1()
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "SBI " & SBINumberTextBox.Text
End Sub

Sub Prepare_Summary_Sheet()
    Sheets("Summary").Select
    Application.Goto Reference:="NextProjectSummaryPage"
    ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Range("A1:J1").Select
    ActiveCell.FormulaR1C1 = "=_" & SBINumberTextBox.Text & "ProjectName"
    ActiveCell.Offset(0, 1).Range("A1").Activate
    ActiveCell.FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Apr"
    ActiveCell.Offset(0, 1).Range("A1").Activate
    ActiveCell.FormulaR1C1 = "=_" & SBINumberTextBox.Text & "May"
    ActiveCell.Offset(0, 1).Range("A1").Activate
    ActiveCell.FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Jun"
    ActiveCell.Offset(0, 1).Range("A1").Activate
    ActiveCell.FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Jul"
    ActiveCell.Offset(0, 1).Range("A1").Activate
    ActiveCell.FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Aug"
    ActiveCell.Offset(0, 1).Range("A1").Activate
    ActiveCell.FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Sep"
    ActiveCell.Offset(0, 1).Range("A1").Activate
    ActiveCell.FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Oct"
    ActiveCell.Offset(0, 1).Range("A1").Activate
    ActiveCell.FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Nov"
    ActiveCell.Offset(0, 1).Range("A1").Activate
    ActiveCell.FormulaR1C1 = "=_" & SBINumberTextBox.Text & "Dec"
    ActiveCell.Offset(0, -9).Range("A1").Select
End Sub
0
 
Rgonzo1971Commented:
Hi,

Could you send a dummy ?

Regards
0
 
Rgonzo1971Commented:
Hi,
Maybe you have to reference the sheet where the textbox is

Sheets("YourSheet").SBINumberTextBox.Text

Open in new window

Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.