OLEObjects - Paste combobox contents to a sheet

[James]

Hi everyone,
Im looking to take the contents of a ComboBox and paste the values in the
list to a sheet (but only if a checkbox next to the combobox is clicked). I
need to seperate them so each value is pasted into a different cell. ie. item
1 goes in cell A1, item2 goes in A2 etc.

the controls are from the control toolbar not the forms toolbar
here is my code, Im having problems first of all with the line:
If o.Value = True Then, ive tried OLEObjects(o.name).value = true etc.
nothing works. any help would be great thanks!

Private Sub CommandButton1_Click() 'Create Custom Report
Dim sh As Worksheet
Dim o As OLEObject, CBX As OLEObject
Dim NewBook As Worksheet, sNum As String
Set sh = ActiveSheet
Workbooks.Add (1)
Set NewBook = ActiveWorkbook.ActiveSheet
For Each o In sh.OLEObjects
If InStr(1, o.Name, "CheckBox") > 0 Then
If o.Value = True Then
sNum = Right(o.Name, InStrRev(o.Name, "_"))
Set CBX = ThisWorkbook.Worksheets("Summary").OLEObjects("CBX_" &
sNum)
'PASTE ALL CONTENTS TO "NewBook"
'KEEP 1 ITEM FROM CBX_# PER ONE CELL ON A SHEET
End If
End If
Next o
Set sh = Nothing
End Sub

 

[joel]

the checkbox has a linkedcell property that will put either true or false in
a cell. Use the linked cell in your macro. The linked cell can be a hidden
column or make it in column IV where it won't be seen.

The problem is finding the correct check box to use. The check box doexn't
have a column and row location. OLE object are pictures that have .left and
..top. You would have to compare the .left and .top with the cell .top and
..left properties which isn't easy.

 

[James]

thanks joel for the reply
I forgot about linked cells. thanks
I actually got it to work though, i just didnt have the .Object in there

Linking the checkbox to the combobox i think i can do. The checkbox will be
named "CheckBox1" and the corresponding combobox will be named "CBX_1" so i
can use the number to take the info from the combobox.

One other question, how do I get the items listed in the combobox??? ive
been trying stuff like:
Set CBX = ThisWorkbook.Worksheets("Summary").OLEObjects("CBX_" & sNum)
MsgBox CBX.Item(1)
'MsgBox CBX.Object.List(0,0)
'MsgBox Cbx.List(0,0)
'Nothin is working, Thanks

 

[James]

the Problem Im having is that I cant retrieve all the contents of the
combobox in my sheet. anyone know how to retrieve the list of data in a
combobox? Thanks

 

[joel]

Use an array starting at 0. This code adds items and then displays them

Sub test()
With ActiveSheet
Set a = .ComboBox1
.ComboBox1.Clear
.ComboBox1.ColumnCount = 10
For i = 0 To 9
For j = 0 To 9

If j = 0 Then
.ComboBox1.AddItem (100 * i) + (j + 1)
Else
.ComboBox1.Column(j, i) = _
(100 * i) + (j + 1)
End If
Next j
Next i

For i = 0 To (.ComboBox1.ListCount - 1)
For j = 0 To (.ComboBox1.ColumnCount - 1)

MsgBox ("Row : " & i & " Column : " & j & _
" = " & .ComboBox1.List(i, j))
Next j
Next i

End With

End Sub