Application that when closed records where it was

[s_brown10]

I've copied below exactly where I'm at in writing this code. For purposes of discussion I have put line numbers in front each line of code (down to a point that is useful).
What I'm trying to do is to record on a sheet called "Invoice TrackSheet" within the application a line label which I have name "ReturnPoint". There are many possible return points within the code. This line label is the point in the code at which I exited and will need to return to. When reopening a particular set of saved data I want to return to the point in the code at which I exited for that particular set of data which is saved on the "Invoice TrackSheet".
My problem is that line 40 "GoTo ReturnPoint" keeps giving me a undefined label error. I defined it within the project as Dim ReturnPoint As String.
That's all that I can think of to say here...if you need any clarifications I will gladly do so.

Thanks,
Steve




1 Public ImageWidth As String
2 Public ImageHeight As String
3 Public NumberofMats As String
4 Public MatNumber1StockNumber As String
5 Public ThisInvoiceNumber As Long
6 Public ThisWorkOrderNumber As Long
7 Public CustomerName As String
8 Public CustomerPhoneNumber As String
9 Public ContactName As String
10 Public DescriptionofWork As String
11 Public DueDate As String
12 Public WorkOrderNumber As String
13 Public InvoiceNumber As Long
14 Public IsNewInvoice As String

15 Sub Macro1()
16 ' Macro recorded 7/8/2009 by User
'
17 ' Keyboard Shortcut: Ctrl+o
'
18 Dim ReturnPoint As String

19 CustomerName = ""
20 CustomerPhoneNumber = ""
21 ContactName = ""
22 DueDate = ""
23 ImageWidth = ""
24 ImageHeight = ""


25 'Find out if a new invoice is being started or if an invoice is being continued
26 Sheets("BACKGROUND").Select
27 IsNewInvoice = MsgBox("Are you wanting to start a new invoice?", vbYesNo, "New Invoice?")
28 If IsNewInvoice = vbYes Then GoTo BeginNewInvoice:
29 If IsNewInvoice = vbNo Then GoTo ReopenCustomer:

30 'ReopenCustomer is for continuing an invoice that has already been started.
31 ReopenCustomer:
32 InvoiceNumber = InputBox("What invoice number would you like to reopen? Enter an ""E"" to exit")
33 If InvoiceNumber = "E" Or InvoiceNumber = "e" Then GoTo EndThisSubRoutine:
34 Range("A1:A9002").Select
35 Range("A1:A9002").Cells.Find(What:=InvoiceNumber, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select
Date = ActiveCell.Offset(0, 1).Range("A1")
36 ReturnPoint = ActiveCell.Offset(0, 2).Range("A1")
37 CustomerName = ActiveCell.Offset(0, 3).Range("A1")
38 ContactName = ActiveCell.Offset(0, 4).Range("A1")
39 DueDate = ActiveCell.Offset(0, 5).Range("A1")
40 GoTo ReturnPoint

41 'BeginNewInvoice is for starting a new invoice
42 BeginNewInvoice:
43 Sheets("INVOICE TRACKSHEET").Select
44 Sheets("INVOICE TRACKSHEET").Unprotect
45 Range("B1").Select
46 Selection.End(xlDown).Select
47 ActiveCell.Offset(1, -1).Range("A1").Select
48 InvoiceNumber = ActiveCell.Value
49 ActiveCell.Offset(0, 1).Range("A1") = Date
50 Sheets("INVOICE TRACKSHEET").Protect
51 CustomerInfo:
52 WorkOrderNumber = 1
53 ThisWorkOrderNumber = InvoiceNumber & WorkOrderNumber
54 CustomerName = InputBox("Please enter the Customer's Name To exit without saving please enter .... E", "Customer's Name?", "E")
55 If CustomerName = "E" Or CustomerName = "e" Then GoTo EndThisSubRoutine
56 If CustomerName = "" Then GoTo CustomerInfo:
57 CustomerPhoneNumber = InputBox("Enter the customer's Phone Number To exit without saving please enter .... E", "Customer's Phone Number")
58 If CustomerPhoneNumber = "E" Or CustomerPhoneNumber = "e" Then GoTo EndThisSubRoutine:
59 ContactName = InputBox("Enter a contact name for this customer if the customer is a business name", "Not Required")
60 If ContactName = "E" Or ContactName = "e" Then GoTo EndThisSubRoutine:
'Save personal info on Work Order TrackSheet so that further info will be "savable".
61 Sheets("WORK ORDER TRACKSHEET").Select
62 Sheets("WORK ORDER TRACKSHEET").Unprotect
63 Range("A1").Select
64 Selection.End(xlDown).Select
65 ActiveCell.Offset(1, 0).Range("A1").Select
66 ActiveCell.Offset(0, 0).Range("A1") = ThisWorkOrderNumber
67ActiveCell.Offset(0, 1).Range("A1") = CustomerName
68 ActiveCell.Offset(0, 2).Range("A1") = Date
69 ActiveCell.Offset(0, 3).Range("A1") = CustomerPhoneNumber
70 ActiveCell.Offset(0, 4).Range("A1") = ContactName
71 Sheets("WORK ORDER TRACKSHEET").Protect
'At this point data has been put on Invoice TrackSheet and the Work Order TrackSheet and it is bes to save this data.
72 Workbook.Save
73GoTo DueDate:
'After the first work order is complete the program returns to the "WorkOrderNumberAssignment:" point if another work order is to be entered.
74WorkOrderNumberAssignment:
75 WorkOrderNumber = WorkOrderNumber + 1
76 If WorkOrderNumber < 26 Then GoTo DueDate:
77 'MsgBox("You have reached the maximum number of work orders that can be invoiced at one time. Hit the enter key and the invoice will be summarized and displayed so that you can finish this invoice....you may open another invoice by just starting a new order","Maximum Number of Work Orders has been reached",VbOK,)
77 If IsNewInvoice = vbYes Then GoTo BeginNewInvoice:
78 If IsNewInvoice = vbNo Then GoTo ReopenCustomer:

79DescriptionofWork:
80 DescriptionofWork = InputBox("Please enter a description of this project", "Short Description Please")
81 If DescriptionofWork = "" Then GoTo DescriptionofWork:
82 If DescriptionofWork = "E" Or DescriptionofWork = "e" Then GoTo EndThisSubRoutine:
83 ReturnPoint = "DescriptionofWork:"
84 If DescriptionofWork = "S" Or DescriptionofWork = "s" Then GoTo SaveWithoutFinishing:
85Quantity:
86 Quantity = InputBox("What Quantity of projects are the same size and framed the same?", "Please Enter the Quantity")
87 If Quantity = "" Then GoTo Quantity:
88 If Quantity = "E" Or Quantity = "e" Then GoTo EndThisSubRoutine:
89 ReturnPoint = "Quantity:"
90 If Quantity = "S" Or Quantity = "s" Then GoTo SaveWithoutFinishing:
91DueDate:
92SpecialDueDate = InputBox("Is there a special due date required Y = Yes N = No E = Exit this customer's order without saving S = Exit and save this customer's order", "Is this a rush order?")
93 If SpecialDueDate = "E" Or SpecialDueDate = "e" Then GoTo EndThisSubRoutine:
94 If SpecialDueDate = "Y" Or SpecialDueDate = "y" Then GoTo EnterSpecialDueDate:
95 ReturnPoint = "DueDate:"
96 If SpecialDueDate = "S" Or SpecialDueDate = "s" Then GoTo SaveWithoutFinishing:
97 If SpecailDueDate = "N" Or SpecialDueDate = "n" Then GoTo ImageWidth:
98 DueDate = Date + 10
99EnterSpecialDueDate:
100 DueDate = InputBox("What is the special date that this order needs to be complete by?", "Enter a date")
101 If DueDate = "E" Or DueDate = "e" Then GoTo EndThisSubRoutine:
102 ReturnPoint = "SpecialDueDate:"
103 If DueDate = "S" Or DueDate = "s" Then GoTo SaveWithoutFinishing:
104ImageWidth:
105 ImageWidth = InputBox("What is the image width?", "Image Width?")
106 If ImageWidth = "E" Or ImageWidth = "e" Then GoTo EndThisSubRoutine:
107 ReturnPoint = "ImageWidth:"
108 If ImageWidth = "S" Then GoTo SaveWithoutFinishing:
109ImageHeight:
110 ImageHeight = InputBox("What is the image height?", "Is this a rush order?")
111 If ImageHeight = "E" Or ImageHeight = "e" Then GoTo EndThisSubRoutine:
112 ReturnPoint = "ImageHeight:"
113 If ImageHeight = "S" Or ImageHeight = "s" Then GoTo SaveWithoutFinishing:
114NumberofMats:
115 NumberofMats = InputBox("How many mats are there? Enter 1 thru 6", "Enter Quantity of mats?")
116 If NumberofMats = 1 Or NumberofMats = 2 Or NumberofMats = 3 Or NumberofMats = 4 Or NumberofMats = 5 Or NumberofMats = 6 Then GoTo MatNumber1StockNumber:
117 MsgBox ("There is a limit of 6 mats...to add another mat please do another workorder for just a mat")
118GoTo NumberofMats:
119 If NumberofMats = "E" Or NumberofMats = "e" Then GoTo EndThisSubRoutine:
120 ReturnPoint = "NumberofMats:"
121 If DueDate = "S" Then GoTo SaveWithoutFinishing:

122MatNumber1StockNumber:
123 MatNumber1StockNumber = InputBox("What is TOP mat number (number 1 mat)", "Input stock number of mat")
124 If MatNumber1StockNumber = "E" Or MatNumber1StockNumber = "e" Then GoTo EndThisSubRoutine:
125 ReturnPoint = "MatNumber1StockNumber:"
126 If MatNumber1StockNumber = "S" Or MatNumber1StockNumber = "s" Then GoTo SaveWithoutFinishing:

127SaveWithoutFinishing:
128 Sheets("INVOICE TRACKSHEET").Select
129 Sheets("INVOICE TRACKSHEET").Unprotect
130 Range("A1:A9002").Select
131 Range("A1:A9002").Cells.Find(What:=InvoiceNumber, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select
132 ActiveCell.Offset(0, 2).Range("A1") = ReturnPoint
133 ActiveCell.Offset(0, 3).Range("A1") = CustomerName
134 ActiveCell.Offset(0, 4).Range("A1") = CustomerPhoneNumber
135 If WorkOrderNumber = 1 Then GoTo 136RecordWorkOrderNumber1DataOnInvoiceTrackSheet:
If WorkOrderNumber = 2 Then GoTo RecordWorkOrderNumber2DataOnInvoiceTrackSheet:
RecordWorkOrderNumber1DataOnInvoiceTrackSheet:
ActiveCell.Offset(0, 5).Range("A1") = DescriptionofWork
GoTo RecordWorkOrderData:

RecordWorkOrderNumber2DataOnInvoiceTrackSheet:
ActiveCell.Offset(0, 9).Range("A1") = DescriptionofWork
GoTo RecordWorkOrderData:


'Continue with the reasoning above for each work order (future programming)
Sheets("INVOICE TRACKSHEET").Protect
RecordWorkOrderData:
Sheets("WORK ORDER TRACKSHEET").Select
Sheets("WORK ORDER TRACKSHEET").Unprotect
Range("A1:A9002").Select
Range("A1:A9002").Cells.Find(What:=ThisWorkOrderNumber, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select
ActiveCell.Offset(0, 5).Range("A1") = DescriptionofWork
ActiveCell.Offset(0, 6).Range("A1") = Quantity
ActiveCell.Offset(0, 7).Range("A1") = DueDate


Sheets("WORK ORDER TRACKSHEET").Protect
Workbook.Save

EndThisSubRoutine:
SaveData = MsgBox("Do you want to save this customers info?", vbYesNo, "Save data?")
If SaveData = vbYes Then GoTo SaveWithoutFinishing:

Sheets("Command Page").Select
End Sub

 

[pbornemeier]

Welcome to the board!

The posted code is very difficult to read since you either did not indent it, or did not use the code tags (see my sig line) when you posted it.

The line numbers are not helping with the readability factor.

On a quick review of your code, you should modify it to use structured control statements, and possibly break it into multiple subroutines, allow each section to be called as required an example (with code tags) follows:

Sub SaveWithoutFinishing()
    Sheets("INVOICE TRACKSHEET").Select
    Sheets("INVOICE TRACKSHEET").Unprotect
    Range("A1:A9002").Select
    Range("A1:A9002").Cells.Find(What:=InvoiceNumber, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Select
    ActiveCell.Offset(0, 2).Range("A1") = ReturnPoint
    ActiveCell.Offset(0, 3).Range("A1") = CustomerName
    ActiveCell.Offset(0, 4).Range("A1") = CustomerPhoneNumber
    If WorkOrderNumber = 1 Then RecordWorkOrderNumber1DataOnInvoiceTrackSheet
    If WorkOrderNumber = 2 Then RecordWorkOrderNumber2DataOnInvoiceTrackSheet
End Sub

You cannot use a variable as the target of a GoTo statement. From Excel help:

GoTo Statement
Branches unconditionally to a specified line within a procedure.
Syntax
GoTo line
The required line argument can be any line label or line number.
Remarks
GoTo can branch only to lines within the procedure where it appears.
Note Too many GoTo statements can make code difficult to read and debug. Use structured control statements (Do...Loop, For...Next, If...Then...Else, Select Case) whenever possible.

 

[s_brown10]

Thank you Pbornemeir.
As you can tell I'm a neophyte here and I appreciate your hearty welcome.
I copied my code into Word and added the line numbers....not a good technique at all....I see that now. I just read the BB coding sheet that should help.

I know how to break up my code into subroutines...no problem. What I don't know how to do is to use the "Call" method...of course I'll look it up in Help. Can you provide a quick example of how I can save a variable which references a subroutine so that I can then "Call" that sub when I reopen the code? Does the "Call" method allow the use of a variable? Can I use a cell reference in a "Call" so that I can get the name of the sub from the sheet on which I saved it? Your experience with this would save me some grinding I think.

Thanks,
Steve

 

[pbornemeier]

One way to do it would be to store text in a "reference" cell that a main subroutine would check when it starts.

Something like:

Option Explicit

Public ImageWidth As String
Public ImageHeight As String
Public NumberofMats As String
Public MatNumber1StockNumber As String
Public ThisInvoiceNumber As Long
Public ThisWorkOrderNumber As Long
Public CustomerName As String
Public CustomerPhoneNumber As String
Public ContactName As String
Public DescriptionofWork As String
Public DueDate As String
Public WorkOrderNumber As String
Public InvoiceNumber As Long
Public IsNewInvoice As String
Sub Main()

    Select Case Worksheets("Sheet1").Range("A1").Value
    Case "A"
        SubroutineA
    Case "B"
        SubroutineB
    Case "C"
        SubroutineC
    Case "D"
        SubroutineD
    Case ""
        WhatToDoIfA1IsBlank
    Case Else
        MsgBox "Invalid code in cell A1"
    End Select

End Sub

Sub SubroutineA()
    'Code
End Sub

Sub SubroutineB()
    'Code
End Sub

Sub SubroutineC()
    'Code
End Sub

Sub SubroutineD()
    'Code
End Sub

Sub WhatToDoIfA1IsBlank()
    'Code
End Sub

Perhaps is would be a better procedure if you have multiple command buttons on a worksheet (or on a new toolbar) that could be usd to Start an Invoice, Add a line, Finish an Invoice, etc.

 

[s_brown10]

Your solutions are very knowledgable. I had gone into my code and started to break it down into subroutines and then my stubborn streak kicked in. I felt there had to be a simple way that I could understand that would use the GoTo command and use the label saved on my data sheet. This is what I did and it worked.

After retrieving "ReturnPoint" from my data sheet I simply wrote the following lines.

If ReturnPoint = "DescriptionofWork" Then GoTo DescriptionofWork:
If ReturnPoint = "Quantity" Then GoTo Quantity:
If ReturnPoint = "DueDate" Then GoTo DueDate:
If ReturnPoint = "SpecialDueDate" Then GoTo SpecialDueDate:
If ReturnPoint = "ImageWidth" Then GoTo ImageWidth:
If ReturnPoint = "ImageHeight" Then GoTo ImageHeight:
If ReturnPoint = "NumberofMats" Then GoTo NumberofMats:
If ReturnPoint = "MatNumber1StockNumber" Then GoTo MatNumber1StockNumber:


It really did help a lot to have your input into this....onward with the rest of the application...I'm sure I'll have many other problems.

Thank you so much,
Steve