Solved

ActiveWorkbook.Names.Add Name

Posted on 2014-04-08
4
597 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 49

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 49

Expert Comment

by:Rgonzo1971
ID: 39989150
Hi,

Could you send a dummy ?

Regards
0
 
LVL 49

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

863 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

23 Experts available now in Live!

Get 1:1 Help Now