Shapes are rediculous to work with

[malik641]

Right?

For example:
I received a workbook with an autoshape (type = -2) that's a drop down list. This thread (http://www.vbaexpress.com/forum/showthread.php?t=5656) to be specific. And I cannot figure out how to obtain the current selected value from the drop down box in the macro (keep in mind I do NOT want to SELECT the shape). And it's REDICULOUS how I CANNOT find ANYTHING on the internet that helps me :banghead::banghead::banghead:

I have NO CLUE how to work with shapes, and anything that I DO figure out...it's practically blind luck.

Can ANYBODY help me out here? Like a link or maybe even a book that goes in-depth with Shapes/msoAutoShapes???

Am I alone on this? Does anyone agree with me???

[Cyberdude]

Yeah, I agree with you (for what that's worth).
But type = -2 ??? Wow.
Sorry, can't help. But then I'm a guy who is still trying to figure out how to get forum logon to be automatic.

[Bob Phillips]

Shapes are a little tricky I agree, but that is because shapes are a superset, that is there are many different object sets that fall within the shapes sets, form controls, worksheet controls etc.

But, as with everything else, you need to study the object model and understand how it all fits in. This is no different to workbooks, charts, etc. If you want someting difficult, try the Word object model, or worse, the Visio object model.

BTW, by my investiagtions, the shape that you refer to is type 8, which translates to msoFormControl, which is entirely in-step with what I see, a forms toolbar combox dropdown.

[malik641]

But, as with everything else, you need to study the object model and understand how it all fits in.Where could I find such information for shapes?



BTW, by my investiagtions, the shape that you refer to is type 8, which translates to msoFormControl, which is entirely in-step with what I see, a forms toolbar combox dropdown.Check out my macro below, the second MsgBox will show 8 like you said, but the first -2...:dunno

Sub GetShapeType()
MsgBox ActiveSheet.Shapes("Drop Down 13").AutoShapeType
MsgBox ActiveSheet.Shapes("Drop Down 13").Type
End Sub So I guess...maybe type -2 is Custom...???

[malik641]

Okay I did a little bit of reading and I found that you can find what selection is in the drop down box by the following:

Option Explicit
Sub GetVal()
Dim DropDown As Object
Dim i As Long
Set DropDown = ActiveSheet.Shapes("Drop Down 13").ControlFormat
i = DropDown.Value
If i = 0 Then
MsgBox "No Value Selected"
Else
MsgBox DropDown.List(i)
End If
End Sub
I found this from the Help file for VBA. But I would prefer a website with some kind of hierarchy chart or something. Anybody know anywhere I can find that?

TIA

[JonPeltier]

I received a workbook with an autoshape (type = -2) that's a drop down list. This thread (http://www.vbaexpress.com/forum/showthread.php?t=5656) to be specific. And I cannot figure out how to obtain the current selected value from the drop down box in the macro (keep in mind I do NOT want to SELECT the shape).

Doesn't the drop down have a linked cell and an input range? Use a worksheet formula in another cell with this kind of formula:

=INDEX(A1:A10,B1)

where the input range is A1:A10 and the linked cell is B1. This puts the value into a cell so it's available to the worksheet, and also to VBA, without any of the angst you're expressing.

[malik641]

Thank Jon :thumb Didn't think of that.

I just wish that Excel had Auto List Members for Shapes, that would just make things MUCH easier in my opinion.