Solved

extract values from embedded form controls

Posted on 2011-03-18
10
358 Views
Last Modified: 2012-06-21
Hello cloud crowd,

I need some help.

The attached data sample shows a few rows of data, with columns F to L in each row sporting form controls. The first one is a combo box, the other ones are check boxes.

I need to extract the value of each combo box and check box and put them in the prepared cells in columns P to V in their respective row.

The spreadsheet has been set up by someone who obviously had little understanding of form controls. None of the controls are linked back to a cell, so the only indication I have for the value of a form control is to look at it and read it.

In most cases, all controls on one row are grouped into one group, but throughout the spreadsheet I also have some rows where not all objects are grouped, and others, where none of them are grouped. In the example, row 9 contains a group that does not contain all objects of the row and row 11 is not grouped at all.

I basically need a script that ungroups all groups, reads the value of each control and writes the value into the respective cell in the respective column of the same row.

I don't mind if the result for the combo box is just the number of the selected item as it appears in the source list (like the value you'd find if the combo box were linked to a cell). If you can extract the text, that's great, but I'll be fine with just the number equivalent. I'm quite comfortable with Vlookup and Index/Match, but not quite there yet with the VBA required for this.

For the check boxes, I can live with a 1 for checked and 0 for not checked, or any other easy to interpret binary.

The real spreadsheet has about ten of these sheets, with around 1000 rows each, and a couple of hundred empty rows with empty form controls. The spreadsheet owner complains that the file runs slowly in 2010. I'm not surprised.

The file is 2003 format, obviously, but I'm happy to process solutions in later versions.

No rush, I'll be offline for most of the next 36 hours.

cheers, teylyn
excel-sample.xls
0
Comment
Question by:Ingeborg Hawighorst
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 19

Expert Comment

by:akoster
ID: 35164344
using these subs you can debug the values of the checkboxes and dropdowns :
Sub test()
    
    For Each Shape In Shapes
        Shape.Select
        
        If InStr(Shape.Name, "Group") Then
            For Each item In Shape.GroupItems
                process item
            Next item
        Else
            process Shape
        End If
    Next Shape
    
End Sub

Sub process(item)

    If InStr(item.Name, "Check Box") > 0 Then
        item.Select
        If Selection.Value = xlOn Then
            Debug.Print item.Name & " is selected"
        Else
            Debug.Print item.Name & " is not selected"
        End If
    End If
    If InStr(item.Name, "Drop Down") > 0 Then
        item.Select
        Debug.Print item.Name & " = " & Selection.Value
    End If
    If item.Name = "Drop Down 64" Then Stop

End Sub

Open in new window

0
 
LVL 19

Expert Comment

by:akoster
ID: 35164411
it seems that most, if not all, groups are built using the following structure :

1) checkbox printed
2) checkbox supplier ack
3) checkbox action required
4) checkbox completed
5) dropdown
6) checkbox receipted
7) checkbox emailed

The problem is that I cannot get the cells the objects are placed in because of the grouping. normally a reference to the cell would suffice to know the location.
In this case the best way would be to use X&Y coordinates of the objects, although they may change when rearranging the worksheet.

The other alternative, using the structure as above in this post, is very tricky as one cannot give any guarantees that it will hold for all groups.
0
 
LVL 50

Author Comment

by:Ingeborg Hawighorst
ID: 35164439
Hi, appreciate the effort so far. I can see what you're doing (although I wouldn't be able to do it myself).

Is it possible to do a global "ungroup" on the sheet, and then process each control based on the cell it sits in?

Will be off-line for most of the weekend.

cheers, teylyn
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Accepted Solution

by:
akoster earned 500 total points
ID: 35164575
this does the trick :
Sub test()
Dim controls()

    '-- initialise
    ReDim controls(0)
    
    '-- first ungroup
    For Each Shape In Shapes
        If InStr(Shape.Name, "Group") Then
            For Each item In Shape.Ungroup
                Set controls(UBound(controls)) = item
                ReDim Preserve controls(UBound(controls) + 1)
            Next item
        Else
            Set controls(UBound(controls)) = Shape
            ReDim Preserve controls(UBound(controls) + 1)
        End If
    Next Shape
    
    For Each item In controls
        If Not IsEmpty(item) Then
            process item
        End If
    Next item
    
End Sub

Sub process(item)
Dim item_value
Dim item_address



    If InStr(item.Name, "Check Box") > 0 Then
        item.Select
        item_value = (Selection.Value = xlOn)
        item_address = Replace(item.TopLeftCell.Address, "$", "")
    ElseIf InStr(item.Name, "Drop Down") > 0 Then
        item.Select
        item_value = Selection.Value
        item_address = Replace(item.TopLeftCell.Address, "$", "")
    End If
    Debug.Print item.Name & " on [" & item_address & "] = " & item_value
    
    Select Case Left(item_address, 1)
        Case "F"
            Range("P" & Mid(item_address, 2)) = item_value
        Case "G"
            Range("Q" & Mid(item_address, 2)) = item_value
        Case "H"
            Range("R" & Mid(item_address, 2)) = item_value
        Case "I"
            Range("S" & Mid(item_address, 2)) = item_value
        Case "J"
            Range("T" & Mid(item_address, 2)) = item_value
        Case "K"
            Range("U" & Mid(item_address, 2)) = item_value
        Case "L"
            Range("V" & Mid(item_address, 2)) = item_value
    End Select

End Sub

Open in new window

0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 35164595
I ungrouped them all like this.

Curt
Sub Ungroup()

For i = 1 To 65536
On Error Resume Next
ActiveSheet.Shapes("Group " & i).Select
    Selection.ShapeRange.Ungroup.Select
Next i
On Error GoTo 0
End Sub

Open in new window

0
 
LVL 50

Author Closing Comment

by:Ingeborg Hawighorst
ID: 35164644
Great! That's the ticket. Thank you very much!!

cheers, teylyn
0
 
LVL 50

Author Comment

by:Ingeborg Hawighorst
ID: 35179100
@akoster,

I spent a few hours today re-working the huge file my end user gave me, with gazillions of combo and check boxes. Thanks to your macro, I was able to convert the information to cell-based data and reduced the overall size of the file from over 2MB to a mere 140 KB in XL2010 format.

The objective was to convert the file from 2003 to 2010, but the original 2003 file threw an error when the user tried to save it as an XLSX, so a simple Save As XLSX was out of the question.

With the help of your macro, I extracted the combo- and check box values, used an Index formula to look up the combo box values and used Marlett font with the character "a" to replace the tick boxes (based on the True/False your macro provided). I bundled the whole thing into an Excel Table and got rid of hundreds of rows with pre-filled empty combo/check boxes for future data entry.

Ten minutes of user education about the new ways of doing things, and everybody was happy, everything pickety-boo.

Thank you again for providing the macro. One day I hope I'll have the skills to write something like that myself. If I read it very slowly, I kind of understand it, but the fine points still have to sink in.

There's so much I can learn at EE, it's really worth spending all this time here!

Are you Dutch? If so:  dank u zeer hartelijk!!! If not: Thanks very much!

cheers, teylyn
0
 
LVL 19

Expert Comment

by:akoster
ID: 35180144
Graag gedaan Teylyn ! (you're welcome)

It is really not that difficult, if you give yourself the time to play around and experiment you will be able to pull this trick yourself before you know it.
Especially with sites like EE, who will help you out when you get stuck, it pays to try to solve questions and difficulties.

0
 
LVL 18

Expert Comment

by:Curt Lindstrom
ID: 35180481
I think Teylyn already can pull some tricks in Excel!

Hall of Fame:
12. teylyn    1,971,153
0
 
LVL 19

Expert Comment

by:akoster
ID: 35180656
oeps,

that's something I failed to notice...
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Outlook Free & Paid Tools
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question