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

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

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