Solved

ActiveWorkbook.Names.Add Name

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB6 - Convert HH:MM into Decimal 8 56
how to know if my Checkbox is True in VB6.0? 9 55
Advice in Xamarin 21 79
IF ELSE Statement in Excel Macro VBA 16 64
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.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

809 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