UserForm Object error

[srentiln]

Hi there,

I tried reading a few different threads with similar error messages to try to resolve this, but I'm evidently missing something. I should preface this by saying I'm new to macros and VBA (using various examples and tutorials to Frankenstein my way to something functional).

What it should do:

When the UserForm is called (either upon opening the document or pressing a button to restart the process), a form with 3 comboboxes and two checboxes appears. Comboboxes 1 and 2 are statically populated from a reference data sheet in the workbook. If Combobox 1's index is the second value, then combobox 3 uses the value of combobox 2 to populate from one of three tables in the reference data sheet. This is where the error is occurring. The checkboxes set cell values in the reference data sheet to tell it to bypass restrictions set on matching data for the workbook's function.

What I have put together:

Private Sub aShift_Change()
'set variables
Dim in1 As Integer
Dim in2 As Integer
Dim sheet As Worksheet
Dim cPerson As Range
Set in1 = Me.aType.ListIndex
Set in2 = Me.aShift.ListIndex
Set sheet = Worksheets("Reference Data")
'check audit type
If in1 = 1 Then
  Me.aPerson.Clear
  
  'select shift to populate with
  Select Case in2
    Case Is = 0
      For Each cPerson In sheet.Range("First")
        With Me.aPerson
          .AddItem cPerson.Value
        End With
      Next cPerson
    Case Is = 1
      For Each cPerson In sheet.Range("Second")
        With Me.aPerson
          .AddItem cPerson.Value
        End With
      Next cPerson
    Case Is = 2
      For Each cPerson In sheet.Range("Third")
        With Me.aPerson
          .AddItem cPerson.Value
        End With
      Next cPerson
  End Select
Else
  Me.aPerson.Clear
  
End If
End Sub
Private Sub runAudit_Click()
'set variables
Dim sheet As Worksheet
Set sheet = Worksheets("Reference Data")
'Set selections cells
sheet.Range("B11").Value = aType.Value
sheet.Range("B12").Value = aShift.Value
If Me.aType.ListIndex = 0 Then
  sheet.Range("B13").Value = "N/A"
Else
  sheet.Range("B13").Value = aPerson.Value
End If
End Sub
Private Sub UserForm_Initialize()
With Me
  .addDownstream.Enabled = True
  .addUpstream.Enabled = True
  .aType.Enabled = True
  .aPerson.Enabled = True
  .aShift.Enabled = True
  .runAudit.Enabled = True
  .Label1.Enabled = True
  .Label2.Enabled = True
  .Label3.Enabled = True
  .Label4.Enabled = True
  .Enabled = True
End With
'set variables
Dim cType As Range
Dim cShift As Range
Dim sheet As Worksheet
Set sheet = Worksheets("Reference Data")
'populate type
For Each cType In sheet.Range("Type")
  With Me.aType
    .AddItem cType.Value
  End With
Next cType
'populate shift
For Each cShift In sheet.Range("Shift")
  With Me.aShift
    .AddItem cShift.Value
  End With
Next cShift
End Sub

The UserForm initializes fine and comes up with a temporary button I put in for testing. The error occurs when I change the value of the aShift combobox (combobox 2 from the above explanation). I've used F8 to step through, and it gets to the line:

Private Sub aShift_Change()

before giving the error message:

Compile error:

Object required.

from my currently limited understanding of VBA, it *should* be working. Please tell me it's not the VBA equivalent of forgetting ";" or "}" in PHP scripting.

 

[dmt32]

Hi,
welcome to forum

Looking t your code you have Declared variables in1 & in2 as Integer data types but you are trying to assign them using Set Statement.

Set is used to assign an object reference to a variable or property consisting of the name of an object, another declared variable of the same object type, or a function or method that returns an object of the same object type.

try changing these lines:

Set in1 = Me.aType.ListIndex
Set in2 = Me.aShift.ListIndex

to this

in1 = Me.aType.ListIndex
in2 = Me.aShift.ListIndex

and see if this resolves your problem

Dave

 

[srentiln]

That does indeed seem to have been the issue. Do you by chance know why stepping through with F8 did not point me to these lines? I like to familiarize myself with limitations in built-in debuggers like this in case I run into related issues while learning a scripting language.

 

[dmt32]

That does indeed seem to have been the issue. Do you by chance know why stepping through with F8 did not point me to these lines? I like to familiarize myself with limitations in built-in debuggers like this in case I run into related issues while learning a scripting language.

I am sure there is an explanation but does not come to mind at moment.
Have a look at your Error trapping options settings in VBA editor.
Try changing it to break on all errors Tools > Options > General > Break on All Errors
and see if that helps.

Also,
you maybe able to shorten your code and avoid looping

Private Sub aShift_Change()
'set variables
    Dim in1 As Integer, in2 As Integer
    Dim sheet As Worksheet
    
    in1 = Me.aType.ListIndex
    in2 = Me.aShift.ListIndex
    
    Set sheet = Worksheets("Reference Data")


    Me.aPerson.Clear
'check audit type
    If in1 = 1 Then
'select shift to populate with
        If in2 >= 0 And in2 < 3 Then Me.aPerson.List = sheet.Range(Choose(in2 + 1, "First", "Second", "Third")).Value
    End If
End Sub

Updated code is untested but hopefully give you ideas for another approach.

Dave

 

[srentiln]

Thank you for the suggestion. There was a missing End If, but with that in place it works just as well while being more concise.

 

[dmt32]

Thank you for the suggestion. There was a missing End If, but with that in place it works just as well while being more concise.

Hi,
if you copied my code as published then there is no "missing" Endif

I suspect you altered the code as follows

        If in2 >= 0 And in2 < 3 Then
            Me.aPerson.List = sheet.Range(Choose(in2 + 1, "First", "Second", "Third")).Value
        End If

if so, try it as I published it (just a single line) you should find it compiles ok.

Dave

 

[srentiln]

Hi,
if you copied my code as published then there is no "missing" Endif

I suspect you altered the code as follows

        If in2 >= 0 And in2 < 3 Then
            Me.aPerson.List = sheet.Range(Choose(in2 + 1, "First", "Second", "Third")).Value
        End If

if so, try it as I published it (just a single line) you should find it compiles ok.

Dave

In your posted code:

Sub...
    If in1 = 1 Then
'select shift to populate with
        If in2 >= 0 And in2 < 3 Then Me.aPerson.List = sheet.Range(Choose(in2 + 1, "First", "Second", "Third")).Value
    End If
End Sub

Does a single End If close all nested If statements?

 

[srentiln]

(sorry for the double post, wanted to edit this question in, but did not see an option to edit the existing post) Also, after reading a bit of other functionalities of UserForms, I decided to add a frame in order to hide the aPerson combobox, the checkboxes, and their respective labels in the case that aType's index was 0 as those fields are irrelevant in that case. It is returning an error of "Method or data member not found", which the help page claims is caused by an invalid member name or collection index. I double-checked all field label spellings, and changing the index number doesn't have an impact either.

My added section:

Private Sub aType_Change()
Dim in1 As Integer
in1 = Me.aType.ListIndex

'clear hide/unhide fields
Me.aPerson.Clear
Me.addDownstream.Clear
Me.addUpstream.Clear

'if a shift audit, hide person drop down and block bypassing
If in1 = 0 Then
  Me.Frame1.Visible = False
  
Else
  Me.Frame1.Visible = True
  
End If
End Sub

What am I overlooking?

 

[dmt32]

In your posted code:

Sub...
    If in1 = 1 Then
'select shift to populate with
        If in2 >= 0 And in2 < 3 Then Me.aPerson.List = sheet.Range(Choose(in2 + 1, "First", "Second", "Third")).Value
    End If
End Sub

Does a single End If close all nested If statements?

Hi,
Simple answer NO
You use End If for each nested multi-line formatting IF statements:

If OneThing then
    If something Then
    'do something
     End If (required)
Else
   ‘do another thing
End If (required)

What I did is is called a one line IF statement

If OneThing then
  If something Then 'do something (no End If required)
End If (required)

Dave

 

[dmt32]

Hi,
this a new question & probably better to start a new thread which should attract more responses from others here.

Dave