2010/10/20 RemyMaza <remym...@gmail.com>
> That works fantastic. It has some problems with how my form works
> though. Let's say for all 7 combo-boxes, the user can only select
> values in order. So if I need 4 devices added, I can't pick from the
> first two and then jump to combo box 5 and 6 to complete the form. I
> need to force the entries to flow in order. How would I do that or is
> that possible?
>
> Thanks for all of your help!
> Matt
>
if I understand, it is more difficult ... try this soluction ... maybe
there is simpler solution ... you can adjust the order and number of
combobox, you must add a class module with the name cCombo:
'xxxxxxxxxxxxxxxx
'in standard module
Public vOrder
'xxxxxxxxxxxxxxxx
'in userform class module
Option Explicit
Dim myCombo() As New cCombo
Sub iniz_u()
vOrder = Array("ComboBox5", "ComboBox2", "ComboBox3", "ComboBox4", _
"ComboBox1", "ComboBox6", "ComboBox7", "")
End Sub
Private Sub UserForm_Initialize()
Dim c As MSForms.Control
Dim v, l As Long, b As Boolean
'for example add same value to all combobox
v = Array(1, 2, 3, 4, 5, 6, 8, 9)
iniz_u
For Each c In Me.Controls
If TypeName(c) = "ComboBox" Then
c.List = v
ReDim Preserve myCombo(l)
Set myCombo(l).CC = c
'If b Then
c.Enabled = c.Name = vOrder(0)
'Else
'b = True
'End If
l = l + 1
End If
Next
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim i
For i = 0 To UBound(myCombo)
Set myCombo(i) = Nothing
Next
End Sub
'xxxxxxxxxxxxxxxx
'in the class module cCombo
Option Explicit
Public WithEvents CC As MSForms.ComboBox
Private Sub CC_Change()
If CC.ListIndex > -1 Then
enable_combo CC.Name
End If
End Sub
Sub enable_combo(scombo As String)
Dim i As Long, x As Long
Dim c As MSForms.Control
For i = 0 To UBound(vOrder)
If vOrder(i) = scombo Then
Exit For
End If
Next
For Each c In UserForm1.Controls
If TypeName(c) = "ComboBox" Then
If c.Name = vOrder(i + 1) Then
c.Enabled = True
Else
c.Enabled = False
End If
End If
Next
End Sub
I worked fast ... but in italy is night :-)
regards
r
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
