Quote:
Originally Posted by Ben McClave
Isabelle,
SelText is a method for an ActiveX object (not Delphi code to my knowledge).. I know that Jerry mentioned the combobox is not an ActiveX object, but as far as I can tell the object should be either a "DropDown" type (Form control) or an ActiveX type. Since "DropDown" did not work, I thought it would be worth checking if the ActiveX method would work.
As an alternative, I wrote a function to check a shape object's type and return the selected text. In the case that the object is not a DropDown or ActiveX control, it will check the value in the linked cell (in this case "A1") to find the correct value using Select Case.
Ben
Function GetText(ws As Worksheet, sShapeName As String) As String
' ws = Worksheet containing ComboBox
' sShapeName = Name of ComboBox
' Example:
' GetText(ThisWorkbook.Sheets("HOME"), "ComboBox1")
Dim sShape As Shape
Dim dd As DropDown
Dim sText As String
Err.Clear
'Set range on next line to the ComboBox's Linked Cell
sText = ws.Range("A1").Value
On Error Resume Next
Set sShape = ws.Shapes(sShapeName)
If sShape Is Nothing Then GoTo NoShape
Select Case sShape.Type
Case 8 'Drop Down
Set dd = sShape.OLEFormat.Object
If Err.Number 0 Then GoTo NoShape
GetText = dd.List(dd.ListIndex)
Exit Function
Case 12 'ActiveX
GetText = ws.OLEObjects(sShapeName).Object.SelText
If Err.Number = 0 Then Exit Function
End Select
NoShape:
If Not IsNumeric(sText) Then
GetText = sText
Else
Select Case sText
Case 1
GetText = "Customer 1"
Case 2
GetText = "Customer 2"
Case 3
GetText = "Supplier"
Case Else
GetText = vbNullString
End Select
End If
End Function
|
My excel is in french , and it is said 'combined zone 89'
I have add this to the code:
Dim strVar As String
With Worksheets("Home")
strVar = .DropDowns("Zone combinée 89").List _
(.DropDowns("Zone combinée 89").ListIndex)
End With
so it goes like this:
Sub MoveToNewWB()
Dim ws As Worksheet 'ICD Sheet
Dim wbNew As Workbook 'New WB
Dim wsDest As Worksheet 'Destination WS
Dim rFind As Range 'Range to search for names
Dim rFound As Range 'Range of found names
Dim sFind As String 'Name to find
'Dim dd As DropDown 'DropDown box
'Set dd = ThisWorkbook.Sheets("Home").Shapes("Combo Box1").OLEFormat.Object
'sFind = dd.List(dd.ListIndex)
Dim strVar As String
With Worksheets("Home")
strVar = .DropDowns("Zone combinée 89").List _
(.DropDowns("Zone combinée 89").ListIndex)
End With
MsgBox strVar
sFind = strVar
'Assign variables
Set ws = ThisWorkbook.Sheets("ICD")
Set rFind = ws.Range("A1:A100")
sFind = ThisWorkbook.Sheets("Home").Range("A1").Value
the problem is it still look for the number and not for the name.
I don t understand why .