Excel-VBA: Getting the values from Form Controls

Question

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

Run-time error 438. Object doesn't support this property or method... and it shows the line putItRng.Rows(1) = ActiveSheet.Shapes("combobox_test").Value — Carlos
– Carlos, Commented Nov 18, 2010 at 21:40

Carlos · Accepted Answer · 2010-12-16 08:26:17Z

11

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

edited Dec 16, 2010 at 8:26

Carlos

5,47521 gold badges71 silver badges118 bronze badges

answered Dec 15, 2010 at 15:07

Paul

1113 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dick Kusleika · Accepted Answer · 2010-11-19 17:51:00Z

8

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

answered Nov 19, 2010 at 17:51

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

1 Comment

Alain Over a year ago

Note, DropDowns (and ComboBoxes, Buttons, etc) are deprecated because if you choose to group several shapes together, controls that are in groups don't appear in those collection objects. So I guess this comment is just a caution not to put your control into any grouped shapes so long as you're using this method (which I myself also use) :)

Ondrej Janacek · Accepted Answer · 2014-01-10 13:49:39Z

3

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

edited Jan 10, 2014 at 13:49

Ondrej Janacek

12.6k14 gold badges62 silver badges96 bronze badges

answered Jan 10, 2014 at 13:25

rdg515

755 bronze badges

3 Comments

рüффп Over a year ago

While this can answer the question, it would be better to explain why it is the right solution.

rdg515 Over a year ago

The highest voted answer references the DropDowns collection, but Alain mentioned that this was deprecated because "if you choose to group several shapes together, controls that are in groups don't appear in those collection objects". The shapes collection is another means to reference a combobox form control, but Carlos post didn't work for me.

Codes with Hammer Over a year ago

I just tried ActiveSheet.Shapes("combobox_test").ControlFormat.Value and found it got the same result. Some scenarios might find this to be a more appropriate coding style.

Siddharth Rout · Accepted Answer · 2012-08-16 16:42:23Z

1

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"

edited Aug 16, 2012 at 16:42

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

answered Nov 18, 2010 at 21:59

Patrick

1461 gold badge4 silver badges13 bronze badges

1 Comment

jpinto3912 Over a year ago

You must reference by ActiveWorkbook.Activesheet ('cause multiple workbooks each have one Activesheet). Rows(1) gives you the entire range of all columns in row 1... Go step by step on this. Try find the real source of the problem using two Msgbox, one for the left side, the other for the right side of the above suggested assignment. That will better pinpoint what's the matter. I'm guessing your object naming is wrong.

Stiebl · Accepted Answer · 2018-10-17 21:41:34Z

1

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

edited Oct 17, 2018 at 21:41

answered Oct 17, 2018 at 21:22

Stiebl

1031 silver badge5 bronze badges

Comments

Whome · Accepted Answer · 2020-12-02 13:22:23Z

0

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

answered Dec 2, 2020 at 13:22

Whome

10.4k6 gold badges58 silver badges67 bronze badges

Collectives™ on Stack Overflow

Excel-VBA: Getting the values from Form Controls

6 Answers 6

Comments

1 Comment

3 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related