Sweet... That makes a lot of sense. I already had the tab order and the values blanked, so now I guess the ticket is to write a function to handle the OnChange event.
Thanks for your help! Cheers, Matt On Oct 20, 10:37 am, Paul Schreiner <schreiner_p...@att.net> wrote: > 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 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 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
