1

Basically I would like to use a function or sub to "paste" code so that I don't have to paste the same thing over and over again. I'm kinda new with VBA so I'm not sure if there's an easier way to handle this. I have many rows of combo boxes that will be determined by their corresponding text box in front of them. I would like to just place a function/subroutine with a number to designate the combo box. 

Function cboAfterUpdate (x as Variant)
    Private Sub cboOperation &x& _AfterUpdate()
     'some other function that uses x'
    End Sub
 End Funcion

I'm hoping it'll end like this...

 cboAfterUpdate(5)

Will show..

 Private Sub cboOperation5_AfterUpdate()
   'some other function that uses 5'
 End Sub

In other words I'd like to do this

Private Sub cboOperation1_AfterUpdate()
Call SqlDes(1)
End Sub
Private Sub cboOperation2_AfterUpdate()
Call SqlDes(2)
End Sub
Private Sub cboOperation3_AfterUpdate()
Call SqlDes(3)
End Sub

up to 20 times since I have cboOperation(1-20) combo boxes. How can I do that in one function without having to have to copy/paste/type 20 private sub events?

Erik A
  • 31,639
  • 12
  • 42
  • 67
fredrandal
  • 21
  • 4
  • 4
    No, that's not possible at all. – rory.ap Oct 10 '18 at 17:36
  • 2
    Sounds like this would be better handled by [a control array](https://stackoverflow.com/q/3014421/4088852). – Comintern Oct 10 '18 at 17:43
  • Thanks -Comintern I'll take a look – fredrandal Oct 10 '18 at 18:36
  • Not quite clear what you're asking here - do you mean you want to add lots of comboboxes that have the same code, or you want the same piece of code to run but with the combobox passed in as a variable? – Absinthe Oct 10 '18 at 18:47
  • I tried clarifying my question by adding more details, thanks @Absinthe – fredrandal Oct 10 '18 at 22:12
  • 1
    Possible duplicate of [Making modular VBA function (MS Access)](https://stackoverflow.com/questions/47298349/making-modular-vba-function-ms-access) – Gustav Oct 11 '18 at 06:55
  • You wouldn't want such repeated code. It is much cleaner to use _WithEvents_ - it takes a little to get started with, but it pays off in the long run. – Gustav Oct 11 '18 at 06:57

1 Answers1

0

I'd probably suffix my comboboxes with a number and use use their Caller property to provide the parameter you want via their string name. E.G. create 3 comboboxes and call them cbo_1, cbo_2, and cbo_3. Then assign a single sub to them:

Sub CboHasChanged()

Dim cboNumber As Integer

Msgbox Application.Caller & " is about to call the sub SqlDes!", vbinformation
cboNumber = CInt(Right(Application.Caller, 1))
SqlDes cboNumber

End Sub

You can make the string manipulation more complicated of course, to account for numbers with double digits and so on but I hope you get the idea - use the name of the combobox to provide the required value.

By the way, you don't need to Call your sub like that, just put the sub name then any parameters without brackets, as I've done in the example.

If you really want to write code with code it is possible in VBA but is an advanced topic.

Absinthe
  • 3,258
  • 6
  • 31
  • 70