P
Paul D Byrne
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,
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,