CODEWITHSUNDEEP
vbauser-controlsexcel
Carlos
Carlos

Reputation: 5445

Excel-VBA: Getting the values from Form Controls

Embedded in the worksheet sheet1 I have a Form Control combo box named combobox_test and it has selected value x

in addition to that, i also have embbeded a button that when i press it i want it to take the value of combobox_test and place it in something.Rows(y). But i cant get it working and am getting a bit frustrated. Maybe you can point me in the right direction

Sub ButtonPressed_sample()
    Dim value As String

    Set putItRng = Range("theCells")        
    putItRng.Rows(1) = ActiveSheet.Shapes("combobox_test").Value        
End Sub

Any advise? Am an absolute beginner in VBA, so please be as detailed as you can. Thanks

Upvotes: 12

Views: 62345

Answers (6)

Whome
Whome

Reputation: 10400

Thanks been fighting with this this one but this topic gave me an answer. I don't know a difference of method1 or method2 reference type, value can be read using .value|.List attributes. It would be great to have a fully typed obj variable.

Dim obj As Object
Set obj = ws.DropDowns("combo1")  ' method 1
Set obj = ws.Shapes("combo1").ControlFormat  ' method 2
Debug.Print obj.value & "|" & obj.List(obj.value)  ' 1...n|Text1,Text2,..n

Upvotes: 0

Stiebl
Stiebl

Reputation: 103

As said before the Shape ComboBox has no Value property.

I use the DrawingObject property of the Shape object to get a CheckBox FormControl object. This CheckBox object can then be used like any other FormControl.

You should also be able to use the DrawinObject to get the ComboBox objcet form the Shape object.

If you want to get te selected text then you can try following code snipped:

Dim sh as Shape
Dim cB as ComboBox
For Each sh In ws.Shapes
    If sh.Type = msoFormControl Then
        If TypeOf sh.DrawingObject Is ComboBox Then
            Set cB = sh.DrawingObject
            ... 
            your code for getting the Data from ComboBox
            ...
        End If
    End If
Next

Upvotes: 1

rdg515
rdg515

Reputation: 75

ActiveSheet.Shapes("combobox_test").ControlFormat.ListIndex

Upvotes: 3

Patrick
Patrick

Reputation: 146

putItRng.Rows(1)= ActiveSheet.combobox_test.value

Try:

activesheet.cells(1,putItRng.column).value=activesheet.combobox_test.value

If it doesnt work then your combobox is not named "Combobox_test"

Upvotes: 1

Paul
Paul

Reputation: 111

   Sub QuickSelect_Change()
        With ActiveSheet.Shapes("QuickBox")
            MsgBox "My Selected Value " & .ControlFormat.List(.ControlFormat.ListIndex)
        End With
    End Sub

Upvotes: 11

Dick Kusleika
Dick Kusleika

Reputation: 33155

I'm not sure this is what you want, but it's a start. The Shape object doesn't have a Value property, which is the source of the error. There is a DropDown object that is deprecated, but still available.

Sub ButtonPressed_sample()

    Set putitrng = Range("theCells")
    putitrng.Rows(1) = ActiveSheet.DropDowns("combobox_test").value

End Sub

Upvotes: 8

Related Questions