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

srentiln

New Member
Joined
Dec 10, 2017
Messages
15
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()".
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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:
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top