Solved

ActiveWorkbook.Names.Add Name

Posted on 2014-04-08
4
576 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 48

Accepted Solution

by:
Rgonzo1971 earned 500 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 48

Expert Comment

by:Rgonzo1971
ID: 39989150
Hi,

Could you send a dummy ?

Regards
0
 
LVL 48

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now