Hi Ryan,
No - only the comboboxes.
thanks,
Paul B.
--
Paul Byrne
"RyanH" wrote:
> Do you have anyother controls on the worksheet? I see in your loop you count
> all ActiveX controls. If you have a checkbox in your worksheet for example,
> you will get an error.
> --
> Cheers,
> Ryan
>
>
> "Paul D Byrne" wrote:
>
> > Hi,
> >
> > I am developing a program to read a matrix of comments and then add an item
> > to a combo box on a worksheet. In a sheet labeled 'DataHandler' I have 5
> > ranges "Comment_Step" which holds the step reference eg 1.1, 1.2, 2.3 etc,
> > then another range "Comment_Test" which holds a list of comments that apply
> > to the steps in the first range. In a worksheet titled "OI - Test" I have a
> > number of comboboxes using the naming convention "Test1pt1", "Test1pt2",
> > "Test1pt3".
> >
> > Using a vba in the worksheet_activate event I am trying to loop through each
> > combobox and add the relevant items to each. Below is the code.
> >
> > For some reason the additem keeps throwing up error 438, "Object doesn't
> > support this property or method". I am obviously not setting the object
> > reference correctly or have a property set incorrectly, as the additem method
> > doesn't appear in the objCombo reference. Any clues as to how to get this
> > going? Below is the code
> >
> > Private Sub Worksheet_Activate()
> >
> > Dim strSheetName As String
> > Dim strCommentName As String
> > Dim strStep As String
> > Dim strStepConversion As String
> >
> > Dim intStepStart As Integer
> > Dim intStepPt As Integer
> > Dim sngStep As Single
> > Dim i, j As Integer
> >
> > Dim shtActive As Worksheet
> > Dim oleCombo As OLEObject
> >
> > Dim shtHandler As Worksheet
> > Dim rngStep As Range
> > Dim rngList As Range
> >
> > Set shtActive = ThisWorkbook.ActiveSheet
> > strSheetName = Trim(Mid(shtActive.Name, 6, Len(shtActive.Name) - 5))
> > strCommentName = "Comment_" & strSheetName
> >
> > For i = 1 To shtActive.OLEObjects.Count
> >
> > Set oleCombo = shtActive.OLEObjects(1)
> > oleCombo.Select
> > strStep = Mid(oleCombo.Name, Len(strSheetName) + 1,
> > Len(oleCombo.Name) - Len(strSheetName))
> > intStepPt = InStr(1, strStep, "pt", vbTextCompare)
> > strStepConversion = Left(strStep, intStepPt - 1) & "." &
> > Mid(strStep, intStepPt + 2, Len(strStep) - intStepPt + 1)
> > sngStep = CSng(strStepConversion)
> > Debug.Print sngStep
> >
> > Set shtHandler = ThisWorkbook.Sheets("DataHandler")
> > Set rngStep = shtHandler.Range("Comment_Step")
> > Set rngList = shtHandler.Range(strCommentName)
> >
> > For j = 2 To rngStep.Rows.Count
> >
> > If rngStep.Cells(j, 1).Value = sngStep Then
> >
> > If rngList.Cells(j, 1).Value <> "" Then
> >
> > oleCombo.AddItem rngList.Cells(j, 1).Value
> >
> > Else
> > End If
> >
> > Else
> > End If
> >
> > Next
> >
> > Next
> >
> > End Sub
> >
> >
> > cheers,
> > --
> > Paul Byrne
|
Similar Threads
