CODEWITHSUNDEEP
vbaexcel
mack
mack

Reputation: 2965

Obtain the value of an HTML Checkbox inserted in Excel worksheet

I have a spreadsheet that was copied from an HTML table for one of our internal applications. The table was pasted into Excel (2010) and has checkboxes for the True/False values. There are over 1800 checkboxes and it takes the spreadsheet a long time to load.

How can I quickly convert these to Yes/No values?

I tried to create a new column and insert an IF statement, but the result was always false. I guess I referenced the cell value and not the checkbox value. The checkboxes have names like "HTMLCheckbox1"..."HTMLCheckbox1824".

Upvotes: 1

Views: 4554

Answers (1)

i_saw_drones
i_saw_drones

Reputation: 3506

I cannot think of a way to do this via Excel formulas - you don't mention it explicitly, but you could use VBA to obtain a solution.

You should be able to access these checkboxes through the OLEObjects collection which is a member of the Worksheet object. You can then check the progID of each object to see if it is an HTML Checkbox (Forms.HTML:Checkbox.1) and then take a note of its value if this is the case.

Here's some sample code I threw together which searches Sheet1 for HTML checkboxes and then fills a collection with string arrays which contain three elements as follows:

  • HTMLName
  • Value
  • Checked

For example, the HTML <input type="checkbox" name="sports" value="soccer" /> would result in:

  • HTMLName: "sports"
  • Value: "soccer"
  • Checked: "TRUE" or "FALSE" depending on whether it was checked or not

This collection is then written to to Sheet2. Checkbox values are converted to strings. 

Sub GetCheckBoxValues()

    Dim colCheckboxValues As Collection

    Set colCheckboxValues = GetCheckBoxValuesFromSheet(Sheet1)

    Dim lCount As Long

    For lCount = 1 To colCheckboxValues.Count

        Sheet2.Cells(lCount, 1).Value = colCheckboxValues.Item(lCount)(0) 'HTMLName
        Sheet2.Cells(lCount, 2).Value = colCheckboxValues.Item(lCount)(1) 'Value
        Sheet2.Cells(lCount, 3).Value = colCheckboxValues.Item(lCount)(2) 'Checked (or unchecked)

    Next lCount

End Sub


Function GetCheckBoxValuesFromSheet(sht As Worksheet) As Collection

    Const HTMLCheckBoxProgID As String = "Forms.HTML:Checkbox.1"

    Dim colCheckBoxes As Collection
    Set colCheckBoxes = New Collection

    Dim oOLEObject As OLEObject

    Dim lCount As Long
    lCount = 0

    For Each oOLEObject In sht.OLEObjects

        If oOLEObject.progID = HTMLCheckBoxProgID Then

            Dim sCheckBoxData(2) As String

            sCheckBoxData(0) = oOLEObject.Object.HTMLName
            sCheckBoxData(1) = oOLEObject.Object.Value
            sCheckBoxData(2) = oOLEObject.Object.Checked

            lCount = lCount + 1

            colCheckBoxes.Add Item:=sCheckBoxData, Key:="CheckBox" & CStr(lCount)

        End If

    Next oOLEObject

    Set GetCheckBoxValuesFromSheet = colCheckBoxes

End Function

Upvotes: 3

Related Questions