Consulting

Results 1 to 4 of 4

Thread: Attach event to multiple ComboBoxes

  1. #1

    Attach event to multiple ComboBoxes

    Hi, I have a multipage with total of 50 comboboxes.
    When Userform loads, they start with same items sourced from Range("A1:A50")
    As I start to select values box by box, I'd like to have number of options in next ComboBox shrink by 1.
    So I'd like to attach one change event to all 50 combos instead of 50 subroutines.

    I found similar thread:
    http://www.vbaexpress.com/forum/show...during+runtime

    However, it's beyond my comprehension.
    Can anybody help please?

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    The attached should get you started. Theres' code in Module1:
    Sub Button1_Click()
    UserForm1.Show
    End Sub
    which is called when the user clicks the button on Sheet1.
    Code in the Userfrom1's code-module:
    Dim Combos() As New CmboClass
    
    Private Sub UserForm_Initialize()
    Dim ComboCount As Long, ctl As Control
    ComboCount = 0
    For Each ctl In Me.Controls
      If TypeName(ctl) = "ComboBox" Then
        ComboCount = ComboCount + 1
        ReDim Preserve Combos(1 To ComboCount)
        Set Combos(ComboCount).ComboGroup = ctl
      End If
    Next ctl
    End Sub
    Which put the comboxes you want to respond into a group (Combos). In this case it's all of them but you can be selective if you want.
    Code in the CmboClass class module:
    Public WithEvents ComboGroup As MsForms.ComboBox
    
    Private Sub ComboGroup_Change()
    Msg = "You changed " & ComboGroup.Name & vbCrLf & vbCrLf
    Msg = Msg & "Its value: " & ComboGroup.Value & vbCrLf
    Msg = Msg & "Left Position: " & ComboGroup.Left & vbCrLf
    Msg = Msg & "Top Position: " & ComboGroup.Top
    MsgBox Msg, vblnformation, ComboGroup.Name
    End Sub
    which just demonstrates it's working correctly.
    What I haven't done is anything about changing the other comboboxes' lists of options; I can do it in a convoluted way, but it would be easier if I were able to expose the Combos object to the class module.
    To delve further I'd like to see a workbook more closely representing your query.

    (ps the file name I've used is NOT because it's the max number of rows in older versions of Excel, it's the number in the url of this thread! I think this tells you I might be a bit geeky.)
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    p45cal, you are geeky indeed. It's much simpler compared to the other thread. My lucky number 65536 got me this prize and hopefully some tax returns in future. Thanks a ton !

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by pulsar777 View Post
    As I start to select values box by box, I'd like to have number of options in next ComboBox shrink by 1.
    Quote Originally Posted by p45cal View Post
    I can do it in a convoluted way
    See attached; 12 comboboxes, each one's options reduce/increase as others take on options.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

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