Extracting information from Multiple Form Control Drop Down Buttons

[Matt123]

Hi All!

My problem is I cannot copy from a Form Control drop down box, and I need to do this for ~10 drop down boxes in about 200 xls files. The files follow a template so the drop down button Inputs are uniform. Here is some more detail:


I’m building an Access Database so that our data (currently in many, many Excel Spreadsheets) can be efficiently queried. The old system was that when asset data was collected, the data would be entered into an Excel spreadsheet, using a Template. So for example:

Project A1234
Name	Main Street Pipe	High Street Pipe
Ref No.	12345	12346
Installation date	22/08/13	19/07/12
X Coordinate	55667	99212
Y Coordinate	77889	12364

<tbody>
</tbody>

We have hundreds of these files, but I managed to get all the files in one folder and Paste and Transpose the data from the relevant Cell Ranges in each file (using a VB sub) into ONE summary sheet. Success; or so I thought.

Unfortunately for one generation of the template (people changed them slightly every year), the template had people enter information into Drop Down Menus. Not a problem when they’re associated with a cell, but these were free floating Form Controls.. so you can’t actually click and copy from them, or call them in VB as you would a specific cell.. afaik.

So my issue is how can I extract the data from those Form Buttons? I’ll try and provide as much info as I can:

Firstly I tried to find some sort of identifier for each Form Button. The dropdowns are called Combo Boxes, (Developer tab > Insert > Combo Box (Form Control) ), and when you select them (by right clicking) and hit the View Code button in Developer Tab > Controls the following shows up in VB.

Sub DropDown44_Change()

End Sub

I’m guessing that the number is related to the button so that’s one useful thing, an identifier of sorts. There’s no actual code there though really from what I can see.. My problem is I have no idea how to extract from DropDown44!

The range of values in the drop down comes from a separate sheet where there’s just a list of the values. There aren’t any cells linked to the drop downs though. If I enter a cell in (Right Click) Format Control > Cell link: then it prints the number (e.g. 4) of the value in the range. So if the drop down options are “Monday”, “Tuesday”, “Wednesday”, “Thursday” and “Thursday” is selected, the linked cell says: “4”. Hope that makes sense, apologies for breaking this down to a basic level I’m just trying to be thorough!
<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600">
<v:stroke joinstyle="miter">

<v:formulas>My first thought was, what a shame they didn’t link a cell, then I could call that cell in a VB sub and the job would be done. Unfortunately they didn’t, so I tried Recording a Macro and selecting the drop down form control.. That didn’t really work, except if I right clicked it, I got some more info from the macro VB:

ActiveSheet.Shapes.Range(Array("Drop Down 19")).Select

So is my drop down an Array? My understanding of an Array is that it’s a list of arguments.. all I get there is the name of the button. I am not experienced in VB so this is as far as I’ve got. Perhaps I can get a sub to select the button using the above line, but I need the info from it, i.e. which value is currently selected in the Drop Down.

Does anyone have any experience with these things in VB? Or maybe there’s something I can do in Excel to have them print to a cell.. I COULD right click each one and link it to a cell. I have a couple of hundred of these Excel Templates though and each one has maybe 10 Drop Downs.. so I’d really rather not do it that way.

(Using Excel 2010 but the .xls files are from ~2005)

Thanks a Lot in Advance for anyone able to help me!
<v:f eqn="if lineDrawn pixelLineWidth 0"><v:f eqn="sum @0 1 0"><v:f eqn="sum 0 0 @1"><v:f eqn="prod @2 1 2"><v:f eqn="prod @3 21600 pixelWidth"><v:f eqn="prod @3 21600 pixelHeight"><v:f eqn="sum @0 0 1"><v:f eqn="prod @6 1 2"><v:f eqn="prod @7 21600 pixelWidth">
<v:f eqn="sum @8 21600 0">
<v:f eqn="prod @7 21600 pixelHeight">
</v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas>
<vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f">
</vath></v:stroke></v:shapetype><v:shape style="width: 398.25pt; height: 96.75pt; visibility: visible;" id="Picture_x0020_1" type="#_x0000_t75" o:spid="_x0000_i1025">
</v:shape>

 

[Matt123]

A colleague has actually solved this for me by finding some code to select the boxes.. I clearly wasn't thorough enough in my search. Will post the code when I finish it in case anyone's interested.

 

[Matt123]

I'd forgotten about this, thankfully I saved the code although I might not be able to explain it again.
Sub DropDownPaster()
Dim MyPath, FilesInPath, strDrops As String
Dim mybook As Workbook
Dim BaseWks As Worksheet
Dim rNum As Long
Dim CalcMode As XlCalculation
Dim dd As Integer
varDD = Array(19, 6, 12, 15, 16, 17, 18)
MyPath = "C:\Work\DropDownBoxes\Files\"

FilesInPath = Dir(MyPath & "*.xl*")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
rNum = 1

While FilesInPath <> ""
Set mybook = Workbooks.Open(MyPath & FilesInPath)


Sheets("Template").Activate

BaseWks.Cells(rNum, "A").Value = FilesInPath


For dd = 1 To 20

Select Case dd
Case 19, 6, 12, 15, 16, 17, 18
strDrops = "Drop Down " & (dd)

With mybook.Worksheets("Template").Shapes(strDrops).ControlFormat
BaseWks.Range("B" & rNum).Value = .List(.ListIndex)
End With
End Select
rNum = rNum + 1
Next dd
mybook.Close savechanges:=False
FilesInPath = Dir()
Wend

BaseWks.Columns.AutoFit

ExitTheSub:
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub

As far as I recall, that should pull the data from a bunch of drop downs and write it out horizontally: with each drop down as a field, each row has the data for one file. Puts file name in the first field I think.
You can see I define the folder containing the Excel files, and the sheet in the spreadsheet that the code should look at. I also define an array which covers the drop down labels; so they're called something like Drop Down1, Drop Down2, in the files so I've written those numbers out as an array (1,2,3 etc.). The code goes back to that array picking the next number along each time in order to make a string and call the data from that box. The numbers were the same as it was a template file used multiple times.. whoever made the template I'm guessing made or deleted a lot of boxes so the labels weren't sequential (e.g. drop down 4, drop down 9 etc).
If you change the bits that were specific to my case, it should work for you. Not an expert on VB so doubt I can offer much help though!