0

When writing a VBA code, I tried to call a Combobox_Change Sub (not Private Sub), which is located in 'Sheet2' of another workbook called 'wb2'.

    Call wb2.Sheet2.ComboBox_Change

However, the program returns the runtime error 438. Am I missing anything or can someone proovide a fix? Thanks!

Run-time error 438: Object doesn't support this property or method

Fammerjammer
  • 3
  • 3
  • 2
    `wb2` is presumably a `Workbook` object, which does not have a `sheet2` property. You'd have to use the sheet tab name: `Call wb2.Sheets("Sheet2").ComboBox_Change` for example. – Rory Jun 12 '18 at 18:54
  • Take a look at the following link for use on WithEvents: http://www.cpearson.com/excel/AppEvent.aspx – Cyril Jun 12 '18 at 18:56
  • @Rory thank you! it works now. I should have tried that before asking the question – Fammerjammer Jun 12 '18 at 19:05
  • 1
    Big Flying Red Flag: you don't want to invoke event handlers explicitly. You want to toggle the combobox state, which has the side-effect of invoking the handler for it. If you want the side-effects without the control state toggle, then have the handler invoke a public procedure, and invoke that same procedure from the other book. Don't make handlers public, write better code instead. – Mathieu Guindon Jun 12 '18 at 19:21

1 Answers1

1

Event handlers do one thing: they handle events. That's why they're Private: they never need to be explicitly invoked by any other code - they're invoked by their respective event source, period.

If it's bad practice in every language I could be bothered to find a SO post about this for, then why would it be any different in VBA? Hint: it's not, the reasons why explicitly invoking an event handler is a bad idea, are completely language-agnostic (provided your language has a concept of "event").

Don't make them Public and then invoke them explicitly. Instead, have that other code toggle the state of the control, and let the control fire its event - assuming an ActiveX/MSForms control:

Dim ctrl As MSForms.ComboBox
Set ctrl = wb.Worksheets("Sheet2").OLEObjects("ComboBox1").Object
ctrl.Value = Not ctrl.Value

If you don't want to toggle the control's state, only to invoke its handler, then pull the handler's implementation out - instead of this:

Private Sub ComboBox_Change()
    'do stuff
End Sub

Do this:

Private Sub ComboBox_Change()
    DoStuff
End Sub

Public Sub DoStuff()
    'do stuff
End Sub

And then invoke DoStuff like you would any other macro-in-another-workbook.

Note that the Call keyword is completely redundant.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • In this case, `Public DoStuff(SomeUsefulParameter as SomeUsefulType)` to enable broad re-use. For example, your handler or main code could pass the relevant worksheet to the `DoStuff` routine, and you could use this for nay worksheet that is logical to do so. But I am sure you already thought of that :-) – AJD Jun 12 '18 at 20:37
  • @AJD absolutely - there simply wasn't enough meat in the OP to have even the slightest idea what `DoStuff` should be doing though :) – Mathieu Guindon Jun 12 '18 at 20:40