Results 1 to 9 of 9

VBA Userforms - method or data member not found

This is a discussion on VBA Userforms - method or data member not found within the Excel Questions forums, part of the Question Forums category; Hiya, I have a userform (userform3) which i am using to launch 2 other userforms, and change a value. Code: ...

  1. #1
    Board Regular thorpyuk's Avatar
    Join Date
    Mar 2006
    Location
    Birmingham, UK
    Posts
    1,408

    Default VBA Userforms - method or data member not found

    Hiya,

    I have a userform (userform3) which i am using to launch 2 other userforms, and change a value.

    Code:
    UserForm3.Hide
    UserForm5.Show
    UserForm5.ComboBox2.Value = S: UserForm5.ComboBox2_Change
    End Sub
    This works fine for my first userform, but i get an error launching this one on the bold type above - method or data member not found

    The userform is modal - if this makes any difference. Yes, combobox2 does exist :D

    Thanks :/
    Any bug is a feature, and any feature is most likely bugged...

  2. #2
    Board Regular hilyete's Avatar
    Join Date
    Aug 2009
    Location
    Nashville, TN
    Posts
    292

    Default Re: VBA Userforms - method or data member not found

    The Combox1_Change routine is an event assigned to the combo box. It happens automatically when a change is made to the selections of the combo box. You can't call the routine the way you are attempting to do (hence the "method" error). Whatever code you have in the combo box_Change routine will occur when you select something from the combo box. If you are assigning a value to your combo box with the "S" variable, then the Change code should be automatically activated, but not until you close the box, or make some other selection change.

  3. #3
    Board Regular thorpyuk's Avatar
    Join Date
    Mar 2006
    Location
    Birmingham, UK
    Posts
    1,408

    Default Re: VBA Userforms - method or data member not found

    Hiya,

    The same code does work for a different userform however... combobox2_change will force the event to run
    Any bug is a feature, and any feature is most likely bugged...

  4. #4
    Board Regular hilyete's Avatar
    Join Date
    Aug 2009
    Location
    Nashville, TN
    Posts
    292

    Default Re: VBA Userforms - method or data member not found

    A lot of this depends on where you are putting the code, etc. It's hard to tell what you are trying to make happen with this, that wouldn't happen anyway with the normal change event (without having to call the event). But, if you put code in ComboBox1's Change event that calls Comobox2's change code, then yes, it will work (I'm not why you would want to do that)

    For example: the code below calls a userform with 2 comboboxes, and puts the number 10 in the window of the 2nd combobox

    That change, triggers combobox2 to call the Change code for combobox1 and puts "hello" in the cell A1. Those codes are within the userform code module. But I can't just call the change event from the first routine which is on my sheet module. But still, it's a very round about way to accomplish this. Can you explain why you need it to do it this way? I'm having a hard time imagining the use of "combo boxes" for this, but of course I don't have the whole picture.

    Code:
     
    Private Sub CommandButton1_Click()
    S = 10
    UserForm1.ComboBox2 = S
    
    UserForm1.Show
    End Sub
     
    Private Sub ComboBox1_Change()
    Range("A1") = "hello"
    End Sub
     
    Private Sub ComboBox2_Change()
    Call ComboBox1_Change
    End Sub

  5. #5
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    17,272

    Default Re: VBA Userforms - method or data member not found

    Code:
    UserForm5.Show
    UserForm5.ComboBox2.Value = S: UserForm5.ComboBox2_Change
    End Sub
    If Userform5 is modal. The last (double) line of code will not run until UF5 is dismissed by the user.

    The sequence will be

    UserForm5.Show
    '[ user dismisses UF ]

    UserForm5.ComboBox2.Value = S
    '[ a new instance of UF5 is loaded and the combobox value is changed]
    '[ note this new instance of UF5 is loaded but NOT shown, therefore invisible]

    UserForm5.ComboBox2_Change
    '[ the event code is run]

    End Sub
    '[ the (invisible) userform is loaded, if exicutuion passes back to some other VB routine, it will remain loaded, but not visible]
    Last edited by mikerickson; Mar 10th, 2010 at 11:08 AM.

  6. #6
    Board Regular thorpyuk's Avatar
    Join Date
    Mar 2006
    Location
    Birmingham, UK
    Posts
    1,408

    Default Re: VBA Userforms - method or data member not found

    Hiya,

    Thanks guys, but still not there :s

    hilyete - i'll put a bit more background together.

    The code is triggered on doubleclick of a listbox within a userform called userform3:

    Code:
    Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    If ListBox1.ListIndex = -1 Then Exit Sub
    If ListBox1.ListCount = 1 Then Exit Sub
    Dim S As Integer
    S = ListBox1.List(ListBox1.ListIndex, 0)
    If ComboBox1.Value = "Projects" Then GoTo 20
    UserForm3.Hide
    Load UserForm4
    UserForm4.ComboBox3.Value = S: UserForm4.ComboBox3_Change
    UserForm4.Show
    Exit Sub
    20
    UserForm3.Hide
    Load UserForm5
    UserForm5.Show
    UserForm5.ComboBox2.Value = S
    UserForm5.ComboBox2_Change
    
    End Sub
    That's my complete code. Mike - i've swapped the code lines around but VBA still errors out giving me a "Method or data member not found" error message on the bold code above.

    As i say, the only difference (and the code above for userform4 works perfectly) seems to be that userform5 is modal, but why would it generate this error at debug stage? :/

    Thanks guys!
    Any bug is a feature, and any feature is most likely bugged...

  7. #7
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    17,272

    Default Re: VBA Userforms - method or data member not found

    Is UserForm5.Combobox2_Change a Private sub?

    if so, you could add another sub to userform5

    Code:
    Public Sub pseudoCombobox2_Change
       ComboBox2_Change
    End Sub
    And change the line in the calling event to
    Code:
    UserForm5.pseudoComboBox2_Change
    Last edited by mikerickson; Mar 11th, 2010 at 09:53 AM.

  8. #8
    Board Regular thorpyuk's Avatar
    Join Date
    Mar 2006
    Location
    Birmingham, UK
    Posts
    1,408

    Default Re: VBA Userforms - method or data member not found

    Hi Mike,

    Ok, think i'm getting maybe closer because the code no longer errors, but nothing happens:

    Code:
    Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    If ListBox1.ListIndex = -1 Then Exit Sub
    If ListBox1.ListCount = 1 Then Exit Sub
    Dim S As Integer
    S = ListBox1.List(ListBox1.ListIndex, 0)
    If ComboBox1.Value = "Projects" Then GoTo 20
    UserForm3.Hide
    Load UserForm4
    UserForm4.ComboBox3.Value = S: UserForm4.ComboBox3_Change
    UserForm4.Show
    Exit Sub
    20
    UserForm3.Hide
    Load UserForm5
    UserForm5.Show
    UserForm5.ComboBox2.Value = S
    UserForm5.pseudoCombobox2_Change
    End Sub
    So my userform3 stays put and refuses to budge! (again, if combobox2.value isn't on "Projects" userform4 fires up ok)
    Any bug is a feature, and any feature is most likely bugged...

  9. #9
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    17,272

    Default Re: VBA Userforms - method or data member not found

    The UserForm 5 that you are talking to is invisible. I don't know whether what UF5.CB2 Change is doing depends on UF5 being visible. Or if it tests for .ListIndex = -1. (setting the Value of a ComboBox may not set the ListIndex to the appropriate value)

    As a workaround you might try (in UF5)

    Code:
    Dim Fire5Change as Boolean
    
    Sub pseuoComboBox2_Change(arg As Integer)
        Fire5Change = True
        With Me
            .ComboBox2.Value = arg
            .Show
         End With
    End Sub
    
    Private Sub UserForm_Activate()
         If Fire5Change Then ComboBox5_Change
         Fire5Change = False
    End Sub
    With the calling code
    Code:
    '...
    UserForm3.Hide
    UserForm5.pseudoCombobox2_Change S
    
    Rem remove Load UserForm5
    Rem remove UserForm5.Show
    Rem remove UserForm5.ComboBox2.Value = S
    Rem remove UserForm5.pseudoCombobox2_Change
    
    End Sub
    Depending on how UF5 is structured, it might need more tweaking to exit that form gracefully.
    When a Userform is shown from an instruction inside its own code module, the Terminate event can throw unexpected errors.
    Last edited by mikerickson; Mar 11th, 2010 at 10:38 AM.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com