Combobox ctrl on worksheet

[Guest]

Hi,

I have several comboboxes(from toolbox, not forms) on a worksheet, and I'm
having trouble with the following code. I get the "object doesn't support
this method" error. Where am I going wrong?

With Worksheets("Database")
Set Rng = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
With Rng

p = 4
For Each ctrl In Worksheets("LessonForm").Controls
If TypeOf ctrl Is MSForms.ComboBox Then
Rng.Offset(0, p).Value = ctrl.Value
p = p + 1
End If
Next ctrl

 

[Norman Jones]

Hi Robbyn,

Try something like:
'========================>>
Sub TestIt()
Dim sh As Worksheet
Dim rng As Range
Dim OLEObj As OLEObject
Dim p As Long

With Worksheets("Database")
Set rng = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Set sh = Worksheets("LessonForm")

p = 4

For Each OLEObj In sh.OLEObjects
If TypeOf OLEObj.Object Is MSForms.ComboBox Then
rng.Offset(0, p).Value = OLEObj.Object.Value
p = p + 1
End If
Next OLEObj

End Sub
'<<========================

 

[Guest]

Worked like a charm! Thanks sooo much Norman!

Hi Robbyn,

Try something like:
'========================>>
Sub TestIt()
Dim sh As Worksheet
Dim rng As Range
Dim OLEObj As OLEObject
Dim p As Long

With Worksheets("Database")
Set rng = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Set sh = Worksheets("LessonForm")

p = 4

For Each OLEObj In sh.OLEObjects
If TypeOf OLEObj.Object Is MSForms.ComboBox Then
rng.Offset(0, p).Value = OLEObj.Object.Value
p = p + 1
End If
Next OLEObj

End Sub
'<<========================