Solved

extract values from embedded form controls

Posted on 2011-03-18
10
349 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:teylyn
  • 5
  • 3
  • 2
10 Comments
 
LVL 19

Expert Comment

by:akoster
Comment Utility
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
Comment Utility
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:teylyn
Comment Utility
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
 
LVL 19

Accepted Solution

by:
akoster earned 500 total points
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 50

Author Closing Comment

by:teylyn
Comment Utility
Great! That's the ticket. Thank you very much!!

cheers, teylyn
0
 
LVL 50

Author Comment

by:teylyn
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
oeps,

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now