OLEObject xlApp, xlWorkbook, xlWorksheet
...
xlApp = OLEObject.Init("Excel.Application")
xlWorkbook = xlApp.Workbooks.Open("path/to/your/spreadsheet.xlsx")
xlWorksheet = xlWorkbook.Worksheets("Sheet1")
xlWorksheet.Activate()
cellAddress = "A1"
cell = xlWorksheet.Range(cellAddress)
For Each shape In xlWorksheet.Shapes
If shape.TopLeftCell = cell Then
' Handle Form Control Listbox
' Access the Listbox properties and methods
Exit For
End If
Next
For Each oleObject In xlWorksheet.OLEObjects
If oleObject.Left = cell.Left And oleObject.Top = cell.Top Then
' Handle ActiveX Control Listbox
' Access the Listbox properties and methods
Exit For
End If
Next
' Interact with the Listbox, select the desired item
listbox.ListIndex = 2
xlWorkbook.Save()
xlWorkbook.Close()
xlApp.Quit()
Sub SelectListBoxItem()
Dim wb As Workbook
Dim ws As Worksheet
Dim lb As ListBox
Dim cell As Range
Dim item As Variant
Dim targetValue As String
' Assuming you have an open workbook, set the workbook and worksheet references
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
' Assuming the listbox is in cell A1 of the worksheet
Set cell = ws.Range("A1")
' Check if the cell has a listbox
If cell.Validation.Type = xlValidateList Then
Set lb = cell.Validation.DropDown
targetValue = "Your desired value"
' Loop through each item in the listbox
For Each item In lb.List
' Compare the item value with the target value
If CStr(item) = targetValue Then
' Select the item in the listbox
lb.Selected(item) = True
Exit For
End If
Next item
End If
End Sub
Sub AccessListBox()
'Declare variables
Dim oExcel As Object
Dim oSheet As Object
Dim oListBox As Object
Dim i As Integer
Dim strValue As String
'Set Excel object
Set oExcel = CreateObject("Excel.Application")
'Set Excel sheet object
Set oSheet = oExcel.Sheets("Sheet1")
'Set listbox object
Set oListBox = oSheet.OLEObjects("ListBox1")
'Loop through listbox items
For i = 1 To oListBox.ListCount
'Get listbox item value
strValue = oListBox.Item(i).Caption
'If listbox item value is equal to desired value, then select it
If strValue = "Desired Value" Then
oListBox.Item(i).Selected = True
End If
Next i
'Close Excel application
oExcel.Quit
End Sub
Return to FiveWin for Harbour/xHarbour
Users browsing this forum: No registered users and 49 guests