Apparently this happens when a variable is not defined, but all mine are, as far as I can tell.
Also, when debugging, the error appears on the End Sub line. That is, the macro (seems to) exit correctly, but as it exits I get the error msg. If the error box could be forced to not appear it would be ok lol. (joking)
Any hints or solutions would be greatly appreciated.
To make explanations simpler:
Psuedocode
Inputbox takes user input
If user input invalid, call Close Subroutine
Else run macro (runs correctly)
.
.
.
V
Close Sub()
Select "menu" worksheet
Unload Me 'exit macro.
End Sub <-----error while debugging happens here.
The actual Code:
Also, when debugging, the error appears on the End Sub line. That is, the macro (seems to) exit correctly, but as it exits I get the error msg. If the error box could be forced to not appear it would be ok lol. (joking)
Any hints or solutions would be greatly appreciated.
To make explanations simpler:
Psuedocode
Inputbox takes user input
If user input invalid, call Close Subroutine
Else run macro (runs correctly)
.
.
.
V
Close Sub()
Select "menu" worksheet
Unload Me 'exit macro.
End Sub <-----error while debugging happens here.
The actual Code:
Code:
Option Explicit
Public ProdSht As String
'This code creates a new worksheet, for a new Product
Public Sub UserForm_Initialize()
Dim sheetName As String
Dim ws As Worksheet, WshSrc As Worksheet, WshTrg As Worksheet
Dim InputDemand As Integer, StartPeriod As Integer, StartYear As Integer
Dim i As Long, A As Long
'open inputbox for user to type Productline code. This will also be the name of the worksheet
ProdSht = InputBox(Prompt:="Enter New Product Code:", Title:="Create New Product Sheet", Default:="I.e. A1")
On Error GoTo 0
If ProdSht = "I.e. A1" Or ProdSht = vbNullString Then 'ensure Programs worksheet is selected.
MsgBox "Invalid Name."
[B] Call CmdDone_Click <-----Call the problem sub from here.[/B]
Exit Sub
End If
Set ws = Sheets.Add(After:=Sheets(Worksheets.Count)) 'create sheet
ws.Name = ProdSht
'Populate with formatting and headings from the Template sheet.
Set WshSrc = ThisWorkbook.Worksheets("Template")
Set WshTrg = ThisWorkbook.Worksheets(ProdSht)
WshSrc.Cells.Copy
With WshTrg.Cells
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
Application.CutCopyMode = False
End With
Sheets("Template").UsedRange.Copy
Sheets(ProdSht).Cells.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone
'-----------------------------------------------------------
'Create Years and Quarters
StartYear = InputBox(Prompt:="In what year does your data start?", _
Title:="Enter Year", Default:="I.e. 2012") 'Ask for the year you you wish to start recording data from.
Set ws = ActiveSheet
A = 9 'this is where the first year is placed
For i = 0 To 100 'place 100 years on the sheet starting from the first year...
Cells(A, 1).Value = StartYear
A = A + 4 'space them out every 4 lines
StartYear = StartYear + 1
Next i
'Create quarterly periods
A = 7
For i = 0 To 400
A = A + 1
Cells(A, 2).Value = StartPeriod
StartPeriod = StartPeriod + 1
If StartPeriod = 5 Then
StartPeriod = 1
End If
Next i
End Sub
Public Sub CmDNext_Click()
Dim i As Integer
Dim AddData As Variant
Dim sht As Worksheet
Dim Ans As String
Dim Y As Double, YRow As Double
Set sht = ThisWorkbook.Worksheets(ProdSht)
If Trim(TextBoxAddYear.Value & vbNullString) = vbNullString Then 'if user clicks next but forgets to add a tear, remind them.
MsgBox "No Year detected"
Exit Sub
Else
For i = 1 To 4
AddData = 0
If Not Trim(("TextBoxAddQtr" & i) & vbNullString) = vbNullString Then 'for each qtr
AddData = AddDataForm.Controls("TextBoxAddQtr" & i)
YRow = TextBoxAddYear.Value + (i / 10)
On Error Resume Next
Y = Application.WorksheetFunction.Match(YRow, ActiveSheet.Range("C1:C1000"), 0) 'find year and qtr to add data to
If Y = 0 Then 'if can't find it
MsgBox "Couldn't find that year." 'tell user if they have made an error
Exit Sub 'return to the data entry form
End If
On Error GoTo 0
If Cells(Y, 4) > 0 Then 'does cell already have data?
Ans = MsgBox("Cell has data. Overwrite?", vbQuestion + vbYesNo) 'does user want to overwrite
If Ans = vbYes Then 'if yes, do so
Cells(Y, 4) = AddData
ElseIf Ans = vbNo Then 'if not
Exit Sub 'return to the data entry form
End If
End If
Cells(Y, 4) = AddData 'if none of the above issues arise, just add data.
End If
Next i 'repeat for each qtr
End If
MsgBox "New Data Added" 'notify user data has been added
TextBoxAddYear.Value = "" 'clear all textboxes for next data entry.
TextBoxAddQtr1.Value = ""
TextBoxAddQtr2.Value = ""
TextBoxAddQtr3.Value = ""
TextBoxAddQtr4.Value = ""
End Sub
Private Sub CmdClear_Click() 'Clears all textboxes.
TextBoxAddYear.Value = ""
TextBoxAddQtr1.Value = ""
TextBoxAddQtr2.Value = ""
TextBoxAddQtr3.Value = ""
TextBoxAddQtr4.Value = ""
End Sub
Private Sub CmdDone_Click()
ThisWorkbook.Sheets("Program").Select 'ensure Programs worksheet is selected.
Unload Me 'exit macro.
End Sub [B]<----- Error happens here[/B]
