Userform hiding fields, "Method or data member not found"

srentiln

New Member
Joined
Dec 10, 2017
Messages
14
Hello,

Still learning VBA, trying to make a UserForm's fields show/hide based on an initial selection.

What it should do:

In the useform, the first Combobox selection hides and clears the third combobox and two checkboxes. These elements are held within a flat frame that is shown or hidden.

When I tested just the show/hide functionality, it worked fine. However, when I added field clearing so that unneeded information is not passed to the worksheet it gives me the error "Method or data member not found". The help page says this 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.

Code:
Private Sub aShift_Change()
'set variables
Dim in1 As Integer, in2 As Integer
Dim sheet As Worksheet
Dim cPerson As Range
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 If
End Sub
Private Sub aType_Change()
Dim in1 As Integer
in1 = Me.aType.ListIndex
Me.aPerson.Clear
Me.addDownstream.Clear
Me.addUpstream.Clear
'if a shift audit, lock person drop down and block bypassing
If in1 = 0 Then
  Me.Frame1.Visible = False
  
Else
  Me.Frame1.Visible = True
  
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
Unload Me
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
  .Frame1.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
I am unsure what I am missing, as the error occurs at the line "Private Sub aType_Change()".
 

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,816
Is aType a textbox (or other control,ie combo box)? If so, aType_Change is the method.
(controls would be nouns, to methods the verbs)

it says upon a change in textbox aType , run the change code.
 
Last edited:

Forum statistics

Threads
1,085,586
Messages
5,384,598
Members
401,913
Latest member
chethan av

Some videos you may like

This Week's Hot Topics

Top