Combobox ctrl on worksheet

[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

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

---
Regards,
Norman



"Robbyn" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Robbyn]

Worked like a charm! Thanks sooo much Norman!

"Norman Jones" wrote:

> 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
> '<<========================
>
> ---
> Regards,
> Norman
>
>
>
> "Robbyn" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>
>