0

I am trying to create a new instance of a user form within a module via the following:

'Prompt for chart type
Dim chForm As ChartForm
Set chForm = New ChartForm
chForm.Show

However at runtime the code fails at the line where I try to set chForm as a new instance of ChartForm, giving Runtime Error 91 for a missing/undefined object. But I know that ChartForm exists, do I need to do something else to enable a module to see UserForm modules? I assume the issue is something of this sort and not an issue with the initialization of the UserForm but just in case the code within the UserForm module is:

Private Sub UserForm_Initialize()

'Get range of possible types
Dim typeRng As Range
Dim stopCell As Range
Dim c As Range
typeRng = chartSheet.Range(Cells(4, 1), Cells(Rows.Count, 1).End(xlUp))

For Each c In typeRng
    If InStr(c.Value, "stDev") Then
        GoTo endL
    Else
        TypeBox.AddItem c.Value 'Add regular fields to dropdown box
    End If
Next

endL:

End Sub

Private Sub CancelButton_Click()

Unload Me

End Sub

Private Sub OKButton_Click()

chartType = TypeBox.Value

End Sub

Which uses a global sheets variable to access a range of cells and populate the combobox in the form.

Eric
  • 49
  • 6
  • 1
    `typeRng = chartSheet.Range(Cells(4, 1), Cells(Rows.Count, 1).End(xlUp))` is missing a `Set`. – BigBen Mar 17 '22 at 19:06
  • Also that line is problematic since it has [unqualified Cells calls](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells). – BigBen Mar 17 '22 at 19:08
  • Aha! So it was an issue in the initialization. I assumed the debugger would go into the UserForm module if the error was there but apparently not, good catch. Are unqualified cell calls an issue if you're only ever working with one sheets object? – Eric Mar 17 '22 at 19:10
  • 2
    Rule of thumb is to qualify *all* `Cells`, `Range`, `Rows`, and `Columns` calls with the workbook/worksheet. – BigBen Mar 17 '22 at 19:11
  • 1
    In Tools > Options > General, change Error Trapping to "Break in Class Module", and this problematic line would be flagged. – BigBen Mar 17 '22 at 19:14

0 Answers0