If 3 userform comboboxes are changed, run macro

[mafallaize]

Hey guys,

I thought this deserved a separate thread after Mike Rickson helped to solve 2 comboboxes changing.

Does anyone know some code which fires a macro if 3 comboboxes have been changed?

Thanks

Mark

 

[LockeGarmin]

Here is a proof of concept. Create a new Userform, add three Combo-boxes named ComboBox1, ComboBox2 and ComboBox3, paste the following code and run.

Option Explicit

Private ComboBox1_IsChanged As Boolean
Private ComboBox2_IsChanged As Boolean
Private ComboBox3_IsChanged As Boolean


Private Sub UserForm_Initialize()
  ComboBox1.List = Array(1, 2, 3)
  ComboBox2.List = Array(4, 5, 6)
  ComboBox3.List = Array(7, 8, 9)
End Sub


Private Sub ComboBox1_Change()
  Call CheckChanges(ComboBox1_IsChanged)
End Sub


Private Sub ComboBox2_Change()
  Call CheckChanges(ComboBox2_IsChanged)
End Sub


Private Sub ComboBox3_Change()
  Call CheckChanges(ComboBox3_IsChanged)
End Sub


Private Sub CheckChanges(ByRef IsChanged As Boolean)
  
  IsChanged = True
  
  If AllChanged Then
    FireMacro
    RevertChanged
  End If
  
End Sub


Private Function AllChanged() As Boolean
  AllChanged = ComboBox1_IsChanged And ComboBox2_IsChanged And ComboBox3_IsChanged
End Function


Private Sub FireMacro()
  MsgBox "MacroFired!" & vbNewLine & vbNewLine & _
    ComboBox1.Value & "~" & ComboBox2.Value & "~" & ComboBox3.Value
End Sub


Private Sub RevertChanged()
  ComboBox1_IsChanged = False
  ComboBox2_IsChanged = False
  ComboBox3_IsChanged = False
End Sub

Hope this helps!

 

[mafallaize]

Thanks LockeGarmin it works a treat!