First is to set the tab order for each object. That way, it makes it EASIER to do it in order.
Next, I'd recommend setting the initial value of each box to either blank or a specific string. then, define a click event for each object that checks to see if the previous objects have been defined. If an object is not yet defined, set the focus to the first undefined object. Paul ----- Original Message ---- > From: RemyMaza <remym...@gmail.com> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> > Sent: Wed, October 20, 2010 9:39:03 AM > Subject: Re: $$Excel-Macros$$ Dynamically assign values to an array using >ComboBoxes > > 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 > > On Oct 20, 6:51 am, roberto mensa <robb....@gmail.com> wrote: > > try in a form add some combobox and a button > > and add this code: > > > > Option Explicit > > Private Sub CommandButton1_Click() > > Dim v > > v = not_null_control_list() > > MsgBox Join(v, ";") > > End Sub > > > > Private Sub UserForm_Initialize() > > Dim c As MSForms.Control > > Dim v > > 'for example add same value to all combobox > > v = Array(1, 2, 3, 4, 5, 6, 8, 9) > > For Each c In Me.Controls > > If TypeName(c) = "ComboBox" Then > > c.List = v > > End If > > Next > > End Sub > > > > Function not_null_control_list( _ > > Optional sNameC As String = "ComboBox") > > Dim c As MSForms.Control > > Dim v(), i As Long > > For Each c In Me.Controls > > If TypeName(c) = sNameC Then > > If c.ListIndex > -1 Then > > ReDim Preserve v(i) > > v(i) = c.Value > > Debug.Print c.Value > > i = i + 1 > > End If > > End If > > Next > > not_null_control_list = v > > End Function > > > > regards > > r > > > > https://sites.google.com/site/e90e50/user/grafici-cruscotto-varianti > > > > 2010/10/19 RemyMaza <remym...@gmail.com> > > > > > > > > > I have a form with 7 combo boxes on it and I'd like to take the values > > > from the selections and store the value into an array. Some > > > background: The first two combo boxes must be selected otherwise the > > > form should not do anything. As long as the first two equal a value, > > > the rest of them *could* be blank or contain a value. What I'd like > > > to do is grab all of the values that aren't blank and resize my array > > > with these values. I'm a bit green with arrays, so a layman's > > > approach would go a long way. Here's the code I have already: > > > > > Dim cCont As Control > > > Dim arrDeviceVal() As String > > > Dim i As Integer > > > > > i = 0 > > > > > 'I'm stuck on the logic for this array > > > 'Finds vals for Devices and ReDim's the array > > > For Each cCont In Me.Controls > > > If TypeName(cCont) = "ComboBox" Then > > > arrDeviceVal = ???? > > > 'Also when I type: cCont. I do not get a property of Value > > > as I would expect. How am I to get the value of the combo box then? > > > End If > > > Next cCont > > > > > For i = 0 To UBound(arrDeviceVal) > > > MsgBox arrDeviceVal(i) > > > Next i > > > If cmb1.Value = "" Or cmb2.Value = "" Then > > > MsgBox "Device 1 and Device 2 cannot be blank. Choose a > > > device for both of these to continue.", vbCritical > > > Else > > > 'ActiveWorkbook.Sheets("ALL FILE").Range("C" & (intCellRow - > > > 8)).Value = arrDeviceVal(0) > > > Unload Me > > > End If > > > > > Thanks for any insight!!! > > > Cheers, > > > Matt > > > > > -- > > > > > --------------------------------------------------------------------------- > > > >------- > > > 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 athttp://www.excel-macros.blogspot.com > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > > > 5. Excel Tips and Tricks athttp://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&;... > > -- >---------------------------------------------------------------------------------- >- > Some e 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 > -- ---------------------------------------------------------------------------------- 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
