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 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
