Possible to read data entered in a worksheet's Form object, using a VBA command?

[Android]

Hi,

I have an Excel workbook which had worksheet's own form objects and also
objects created with VBA's control toolbox.

Specifically I have a drop down form object called DropDown6 (I see no way
of changing the name). In my VBA code, I want to capture the data the user
had previously picked from this drop down & do further processeing with it.
I know that I can assign a macro to the dropdown, but I want to do other
things which are to be triggered by a separate command button.

Is it possible to use a VBA command to capture the data the user had
previously picked in this drop down?

Regards...

 

[pfsardella]

Place this in the subroutine for for the command button. Change the
sheet name and DropDown name as needed. strWI will return the value
selected by the user.

Dim drpWI As DropDown
Dim strWI As String

Set drpWI = ThisWorkbook.Sheets("MySht").DropDowns("DropDown6")
With drpWI
strWI = .List(.ListIndex)
End With

--------------------------------------------------------------------------------------------------------------

When I want to change the name of an object from the Forms Toolbar, I
select the object and use the Immediate Window to change the name.

Selection.Name = "Desired Name"

HTH
Paul

 

[Android]

Hello Paul,

I get run-time error 1004 - Unable to get the DropDowns property of the
worksheet class, at the following statement:
Set drpWI = ThisWorkbook.Sheets("MySht").DropDowns("DropDown6")

Also how do you change the name of the drop down. There is no property
window for it, only for the sheet as a whole.

Android...