?
Solved

ActiveWorkbook.Names.Add Name

Posted on 2014-04-08
4
Medium Priority
?
755 Views
Last Modified: 2014-04-09
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
0
Comment
Question by:cowboywm
  • 3
4 Comments
 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 1000 total points
ID: 39987939
Hi,

maybe you have to give the Form reference

myForm.SBINumberTextBox.Text

Regards
0
 

Author Comment

by:cowboywm
ID: 39989096
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
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 39989150
Hi,

Could you send a dummy ?

Regards
0
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 39989189
Hi,
Maybe you have to reference the sheet where the textbox is

Sheets("YourSheet").SBINumberTextBox.Text

Open in new window

Regards
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question