VBA Userforms - method or data member not found

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
Hiya,

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

Rich (BB 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 :/
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.
 
Upvote 0
Hiya,

The same code does work for a different userform however... combobox2_change will force the event to run
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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:

Rich (BB 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!
 
Upvote 0
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:
Upvote 0
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)
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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