Combobox ctrl on worksheet

R

Robbyn

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
 
N

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
'<<========================
 
R

Robbyn

Worked like a charm! Thanks sooo much Norman!

Norman Jones said:
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
'<<========================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top