Sending ActiveX controls as variables in VBA

[metz.bd]

Hi all. Thanks in advance for any help you can give me; this is rather vexing. I have 4 combo boxes (lvl0_ComboBox,... lvl3_ComboBox) in a sheet titled "Data Input". Because I end up needing to reset them to default a lot I made the following function:

Function resetCombo(cmboBox As Object)
' Resets combo to only have value "[any]"
  With cmboBox
    .Clear
    .AddItem "[any]"
    .Value = "[any]"
  End With
End Function

I'm having trouble passing the objects in an elegant way. The subroutine where I call resetCombo() from is in a module, so I have to get specific. If my module reads:

Call resetCombo(resetCombo(Worksheets("Data Input").lvl1_ComboBox)

then everything works fine. But I want to save myself from some errors (especially since the page name is subject to change), so I tried to simplify:

Public InputSheet As Worksheet

Sub whatever()
  Set InputSheet = Worksheets("Data Input")
  Call resetCombo(InputSheet.lvl1_ComboBox)
End Sub

but this gives me a "Method or data member not found" error, highlighting the line in the whatever() sub. I did some searching online and thought that maybe it would work if I passed it as an OLEObject:

Public InputSheet As Worksheet

Sub whatever()
  Dim cmboBoxes as Object
  
  Set InputSheet = Worksheets("Data Input")
  Set cmboBoxes = InputSheet.OLEObjects

  cmboBoxes("lvl1_ComboBox").clear
  Call resetCombo(cmboBoxes("lvl1_ComboBox")
End Sub

When I run the clear outside of the function (3rd to last line) I don't get any errors, but when I try to pass it to resetCombo() (2nd to last line) I get the "Object doesn't support this property or method" error.

I've got everything working now using Worksheets("Data Input").X, but I'd really like the more concise way. Does anyone see what I'm doing wrong? Thanks again.

 

[Worf]

Hello and welcome to the Board

Please test this:

Option Explicit
Public InputSheet As Worksheet


Function resetCombo(cmboBox As OLEObject)


' Resets combo to only have value "[any]"


  With cmboBox.Object
    .Clear
    .AddItem "[any]"
    .Value = "[any]"
  End With
  
End Function


Sub whatever()
  
  Set InputSheet = Worksheets("Data Input")
  
  MsgBox InputSheet.OLEObjects(1).Name  ' reference by index
    
  resetCombo InputSheet.OLEObjects("LVL2_ComboBox") ' reference by name
  
End Sub

 

[metz.bd]

Ah ha! That seems to have worked! That extra .object was unexpected. The interesting thing is, now if I add back the cmboBoxes lines, I can call the function as <tt>resetCombo cmboBoxes("lvl0_ComboBox")</tt> without an error as well, even though I'd be expecting a type mismatch.

Thanks!

 

[Worf]

You are welcome and thanks for the feedback

 

[OriginalAccess]

I'm working on a similar problem, but regarding your malleable Worksheet names, I have a solution (old programmer's trick)

Create a basGlobals Module containing all your global variables and constants (Win 32 DLLs should also go there). In it define constants for all your Worksheets:

Global Const cstrDataInputTab As String = "Data Input"
Global Const cstrSettingsTab As String = "Settings"
etc.

You would use it like this:
ThisWorkbook.Worksheets(cstrDataInputTab).Range("A1") = no quotes, no spaces

Use constants, not variables, because variables disappear every time Excel hiccups. You would need a procedure with a trigger to restore them

After that you can change (and reorder) your worksheets to your heat content, and would need to adjust name only in one place - basGlobals.

Good luck