Hi. I'm making a project that involves loading and unloading several user forms when I press the okay button. This is the code for the button:
Private Sub cmdOkay_Click()
Dim mychart As ChartObject
Dim counterX As Long
Dim k As Long
Dim a As Long
Dim i As Long
Dim flag As Long
Dim sizeX As Long
Dim ItemX As String
Dim ColumnIndexX As Long
Dim n_values As Variant
Dim x_values As Variant
Dim UCL() As Double
Dim LCL() As Double
Dim flag_control_limits As Long
Dim mean_series() As Double
Dim Xarray As Variant
Dim pvalues As Variant
Dim npvalues As Variant
Dim mean As Double
Dim z_stdev() As Variant
Dim z_stdev_known As Double
flag = 0
flag_control_limits = 0
i = 1
Call select_data(n_values, x_values) 'store n and x data in arrays
ReDim UCL(1 To UBound(n_values))
ReDim LCL(1 To UBound(n_values))
ReDim mean_series(1 To UBound(n_values))
ReDim z_stdev(1 To UBound(n_values))
' If (Not IsNull(SelectedItemsX.List(0))) Then
' For counterX = 1 To N_Columns 'Goes through all columns to find X column
' ItemX = SelectedItemsX.List(0)
' ActiveSheet.Cells(1, counterX).Activate
' If (ItemX = CStr(ActiveCell.value)) Then
' ColumnIndexX = counterX
' End If
' Next counterX
'ActiveSheet.Cells(2, ColumnIndexX).Select
' sizeX = WorksheetFunction.CountA(Columns(ColumnIndexX)) 'finds size of a column
' Xarray = Range(ActiveCell.address(), ActiveCell.Offset(sizeX - 2, 0).address()) 'sets range as the column
' flag = 1
' End If
If (variable_CL.Value = True) Then 'if variable control limits are chosen
flag_control_limits = 1
End If
Call CreateChart
'if the advanced form is not loaded
If (IsUserFormLoaded("frmAdvanced") = False) Then
'calculates values to be graphed (p or np)
'adds that series to the graph
'creates new arrays with values to be 'excluded from the calculations
'calculates z-score and mean from the new values
If (pchart_button.Value = True) Then
pvalues = pvalues_function(n_values, x_values)
Call AddSeries(pvalues, "p values")
'Call exclude(n_values, x_values)
z_stdev = p_stdevs(txtMultiple.Text, n_values, x_values, flag_control_limits)
mean = SumArray(x_values) / SumArray(n_values)
Else
npvalues = npvalues_function(n_values, x_values)
Call AddSeries(npvalues, "np values")
'Call 'exclude(n_values, x_values)
z_stdev = np_stdevs(txtMultiple.Text, n_values, x_values, flag_control_limits)
mean = SumArray(x_values) / CountArray(x_values)
End If
'if advanced form is loaded and parameters are known
ElseIf (frmAdvanced.optParametersKnown.Value = True) Then
mean = CLng(frmAdvanced.txtMean.Text)
z_stdev_known = CLng(txtMultiple.Text) * CLng(frmAdvanced.txtStDev.Text)
'if advanced form is loaded and parameters are estimated from selected data
Else
Dim advNi As Variant
Dim advXi As Variant
If (pchart_button.Value = True) Then
pvalues = pvalues_function(n_values, x_values)
Call AddSeries(pvalues, "p values")
'Call 'exclude(n_values, x_values)
advNi = Range(frmAdvanced.rfeNi.Value)
advXi = Range(frmAdvanced.rfeNi.Value)
z_stdev = p_stdevs(txtMultiple.Text, advNi, advXi, flag_control_limits)
mean = SumArray(advXi) / SumArray(advNi)
Else
npvalues = npvalues_function(n_values, x_values)
Call AddSeries(npvalues, "np values")
'Call 'exclude(n_values, x_values)
advNi = Range(frmAdvanced.rfeNi.Value)
advXi = Range(frmAdvanced.rfeNi.Value)
z_stdev = np_stdevs(txtMultiple.Text, advNi, advXi, flag_control_limits)
mean = SumArray(advXi) / SumArray(advNi)
End If
End If
'updates the mean and the control limits
'if lower control limit is less than 0, it is rounded up to 0
'if upper control limit is greater than 1 (for p chart) it is rounded down to 1
For i = 1 To UBound(UCL)
If (IsUserFormLoaded("frmAdvanced") = True And frmAdvanced.optParametersKnown.Value = True) Then
z_stdev(i) = z_stdev_known
End If
UCL(i) = mean + z_stdev(i)
If (UCL(i) > 1 And pchart_button.Value = True) Then
UCL(i) = 1
End If
LCL(i) = mean - z_stdev(i)
If (LCL(i) < 0) Then
LCL(i) = 0
End If
mean_series(i) = mean
Next
'adds the mean series
Call AddSeries(mean_series, "mean", True)
If (flag = 1) Then
ActiveChart.SeriesCollection.Xvalues = Xarray
End If
'if a multiple is specified by the user, add the upper and lower control limits
If (Len(Trim(txtMultiple.Value)) > 0) Then
Call AddSeries(UCL, "UCL", True)
Call AddSeries(LCL, "LCL", True)
End If
ActiveChart.ChartArea.Select
Unload (frmCustomData)
Unload (frmAdvanced)
Unload (Me)
frmChartName.Show
End Sub
It gives me an error at the line in bold saying it can't load or unload object. I have tried to switch around the order of the forms but it doesn't work. I have also checked the names of all the forms and they are as written in the code. Not sure what to do. Help would be appreciated.
Private Sub cmdOkay_Click()
Dim mychart As ChartObject
Dim counterX As Long
Dim k As Long
Dim a As Long
Dim i As Long
Dim flag As Long
Dim sizeX As Long
Dim ItemX As String
Dim ColumnIndexX As Long
Dim n_values As Variant
Dim x_values As Variant
Dim UCL() As Double
Dim LCL() As Double
Dim flag_control_limits As Long
Dim mean_series() As Double
Dim Xarray As Variant
Dim pvalues As Variant
Dim npvalues As Variant
Dim mean As Double
Dim z_stdev() As Variant
Dim z_stdev_known As Double
flag = 0
flag_control_limits = 0
i = 1
Call select_data(n_values, x_values) 'store n and x data in arrays
ReDim UCL(1 To UBound(n_values))
ReDim LCL(1 To UBound(n_values))
ReDim mean_series(1 To UBound(n_values))
ReDim z_stdev(1 To UBound(n_values))
' If (Not IsNull(SelectedItemsX.List(0))) Then
' For counterX = 1 To N_Columns 'Goes through all columns to find X column
' ItemX = SelectedItemsX.List(0)
' ActiveSheet.Cells(1, counterX).Activate
' If (ItemX = CStr(ActiveCell.value)) Then
' ColumnIndexX = counterX
' End If
' Next counterX
'ActiveSheet.Cells(2, ColumnIndexX).Select
' sizeX = WorksheetFunction.CountA(Columns(ColumnIndexX)) 'finds size of a column
' Xarray = Range(ActiveCell.address(), ActiveCell.Offset(sizeX - 2, 0).address()) 'sets range as the column
' flag = 1
' End If
If (variable_CL.Value = True) Then 'if variable control limits are chosen
flag_control_limits = 1
End If
Call CreateChart
'if the advanced form is not loaded
If (IsUserFormLoaded("frmAdvanced") = False) Then
'calculates values to be graphed (p or np)
'adds that series to the graph
'creates new arrays with values to be 'excluded from the calculations
'calculates z-score and mean from the new values
If (pchart_button.Value = True) Then
pvalues = pvalues_function(n_values, x_values)
Call AddSeries(pvalues, "p values")
'Call exclude(n_values, x_values)
z_stdev = p_stdevs(txtMultiple.Text, n_values, x_values, flag_control_limits)
mean = SumArray(x_values) / SumArray(n_values)
Else
npvalues = npvalues_function(n_values, x_values)
Call AddSeries(npvalues, "np values")
'Call 'exclude(n_values, x_values)
z_stdev = np_stdevs(txtMultiple.Text, n_values, x_values, flag_control_limits)
mean = SumArray(x_values) / CountArray(x_values)
End If
'if advanced form is loaded and parameters are known
ElseIf (frmAdvanced.optParametersKnown.Value = True) Then
mean = CLng(frmAdvanced.txtMean.Text)
z_stdev_known = CLng(txtMultiple.Text) * CLng(frmAdvanced.txtStDev.Text)
'if advanced form is loaded and parameters are estimated from selected data
Else
Dim advNi As Variant
Dim advXi As Variant
If (pchart_button.Value = True) Then
pvalues = pvalues_function(n_values, x_values)
Call AddSeries(pvalues, "p values")
'Call 'exclude(n_values, x_values)
advNi = Range(frmAdvanced.rfeNi.Value)
advXi = Range(frmAdvanced.rfeNi.Value)
z_stdev = p_stdevs(txtMultiple.Text, advNi, advXi, flag_control_limits)
mean = SumArray(advXi) / SumArray(advNi)
Else
npvalues = npvalues_function(n_values, x_values)
Call AddSeries(npvalues, "np values")
'Call 'exclude(n_values, x_values)
advNi = Range(frmAdvanced.rfeNi.Value)
advXi = Range(frmAdvanced.rfeNi.Value)
z_stdev = np_stdevs(txtMultiple.Text, advNi, advXi, flag_control_limits)
mean = SumArray(advXi) / SumArray(advNi)
End If
End If
'updates the mean and the control limits
'if lower control limit is less than 0, it is rounded up to 0
'if upper control limit is greater than 1 (for p chart) it is rounded down to 1
For i = 1 To UBound(UCL)
If (IsUserFormLoaded("frmAdvanced") = True And frmAdvanced.optParametersKnown.Value = True) Then
z_stdev(i) = z_stdev_known
End If
UCL(i) = mean + z_stdev(i)
If (UCL(i) > 1 And pchart_button.Value = True) Then
UCL(i) = 1
End If
LCL(i) = mean - z_stdev(i)
If (LCL(i) < 0) Then
LCL(i) = 0
End If
mean_series(i) = mean
Next
'adds the mean series
Call AddSeries(mean_series, "mean", True)
If (flag = 1) Then
ActiveChart.SeriesCollection.Xvalues = Xarray
End If
'if a multiple is specified by the user, add the upper and lower control limits
If (Len(Trim(txtMultiple.Value)) > 0) Then
Call AddSeries(UCL, "UCL", True)
Call AddSeries(LCL, "LCL", True)
End If
ActiveChart.ChartArea.Select
Unload (frmCustomData)
Unload (frmAdvanced)
Unload (Me)
frmChartName.Show
End Sub
It gives me an error at the line in bold saying it can't load or unload object. I have tried to switch around the order of the forms but it doesn't work. I have also checked the names of all the forms and they are as written in the code. Not sure what to do. Help would be appreciated.