Finding which cells are inputs, by looking for controls and data-validation

I need to write code to discover which cells in a spreadsheet are inputs. This is difficult in general, but I can use rules of thumb. One rule of thumb, which my posting is about, is that a cell with controls or data-validation entities in it is likely to be an input. By data-validation entities, I mean things inserted via the Data/Validation option. By controls, I mean Forms or ActiveX controls, especially if these are buttons, text boxes, check boxes, and so on.

I've written VBA code to scan a sheet and work out which cells contain data-validation entities or controls, and I'd welcome advice. To decide which cells contain data-validation entities, I loop through the sheet's UsedRange property, and see whether each cell's Validation property exists. I hope that UsedRange will include all the cells with data-validation entities in, though VBA documentation doesn't specify this. To decide which cells contain controls, I loop through the sheet's Shapes property, and for each Shape in it, get the TopLeftCell property.

I've got several questions about this. I am not sure about controls that cover more than one cell, because Shape also has a BottomRightCell property. I am also not sure whether some cells that are not inputs might contain these controls, or whether there can be cell-linked controls that are not accessible via the Shapes property. Also, how can I find out what kind of control a control is? I couldn't find an appropriate Type field or similar. Could I get a class name from somewhere?

By the way, why didn't Microsoft make this easier by treating controls like data-validation entities, so that a cell has a Control property...? It's unsystematic to access data-validation entities via a property in a cell, but to access other controls via a separate list attached to the worksheet.

My code is below. The attached spreadsheet contains it, and also has a sheet containing data-validation entities and buttons, to test it. The code in the spreadsheet contains a hashtable implementation, in which to store mappings from cell names to info about the cells. I've omitted that from the listing below, because Xtreme VB Talk won't let me post that long a message. For the same reason, I've also omitted some of my comments, and the test routines that insert buttons into the sheet. Here is the code:

' This code starts with a subroutine named "test" which
' calls the main routine,
' ExamineSheetForDataValidationEntitiesAndControls .
'
' ExamineSheetForDataValidationEntitiesAndControls
' first creates a hashtable to hold
' the mapping from cell addresses such as "A1" or
' "B24" to strings giving info about the cell.
' In a realistic implementation, one would store this
' info in more advanced data structures such as
' records, but VBA's type system restricts where
' these can be used. I kept getting the message
' "Only user defined types defined in public object
' modules can be coerced to or from a variant
' or passed to late-bound functions"
' when I wrote a function that returned a user-defined
' record, so since this is only a demo, I gave up
' and just stored the info in strings.
'
' After creating the hashtable,
' ExamineSheetForDataValidationEntitiesAndControls
' first scans the sheet for cells with data-validation
' entities in, and then scans its list of controls.
' Note that we have to find the data-validation
' entities by looking inside cells, but we have to
' find the controls by looking at this separate list
' of controls attached to the sheet. This is a typical
' Excel irregularity. Storing results in a hashtable
' lets me present both sets of results in a consistent
' way.
'
' The two routines that do this are named
' ExamineSheetForValidationEntities and ExamineSheetForControls.
' Each takes a worksheet and a hashtable, and adds
' mappings to the hashtable.
'
' As mentioned, ExamineSheetForValidationEntities
' scans the sheet for cells with data-validation
' entities in. It does this by looping over the
' sheet's UsedRange property. I _hope_ that this
' will include all the cells with with data-validation
' entities in, though the VBA documentation doesn't
' say.
'
' Also as mentioned, ExamineSheetForControls
' can't get useful info by looking inside cells, but
' has to inspect a list of controls that's
' attached to the sheet. This is the sheet's
' Shapes property. If a Shape has been put in
' a cell, the Shape's TopLeftCell property
' should be set: this is how ExamineSheetForControls
' knows which cell the button belongs to.


' Main test function. Call this to demonstrate the
' code.
'
Sub test()
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Sheet1")
Call ExamineSheetForDataValidationEntitiesAndControls(ws)
End Sub


' Main routine. Builds and reports a hashtable containing the
' info about what's in sheet ws's cells.
'
Sub ExamineSheetForDataValidationEntitiesAndControls(ws As Worksheet)
Dim htable() As hashtable

' Make the hashtable.
'
Call CreateHashTable(htable())

' Scan the sheet for info and put it into the hashtable.
'
Call ExamineSheetForValidationEntities(ws, htable())
Call ExamineSheetForControls(ws, htable())

' Display the results.
'
MsgBox "These are the data-validation entities and controls, dumped from the hashtable:"
For i = 1 To UBound(htable)
MsgBox "Cell " & CStr(i) & CStr(htable(i).key) & " -> " & CStr(htable(i).value)
Next i

End Sub


' Examines cells for validation entities, by looping
' through the sheet's UsedRange property and inspecting each
' cell in it.
'
Sub ExamineSheetForValidationEntities(ws As Worksheet, htable() As hashtable)
Dim all_cells As Range
Dim c As Range
Dim ValidationInfo As String

' Get all the used cells. I hope this
' includes all those with data-validation
' entities in.
'
Set all_cells = ws.UsedRange

' Loop through this, inspecting each cell and
' updating the hashtable if appropriate.
'
For Each c In all_cells
ValidationInfo = CellToValidationInfo(c)
If ValidationInfo <> "" Then
Call AddValue(htable(), c.AddressLocal, ValidationInfo)
End If
Next c
End Sub


' Returns a string giving info about the data-validation
' entity in the cell in range r, assumed to contain
' just one cell. If the cell contains none, returns "".
'
Function CellToValidationInfo(r As Range) As String
Dim ValidationType As Integer
' The integer that Microsoft use to indicate
' what kind of thing the data-validation
' entity is.

ValidationType = -1
' This is not a valid Excel data-validation
' type. So if the cell doesn't contain one,
' my code will keep it at -1.

On Error Resume Next
' If the cell contains no data-validation
' entity, this will make control go to the next
' statement anyway.

ValidationType = r.Validation.Type

On Error GoTo 0
' Reset error handler.

' Now return a result. Either "",
' or an informative string.
'
If (ValidationType = -1) Then
CellToValidationInfo = ""
Else
CellToValidationInfo = ValidationTypeToValidationTypeName(ValidationType) _
& " (type=" & ValidationType & ")"
End If
End Function


' Returns my name for Microsoft's data-validation type t.
'
Function ValidationTypeToValidationTypeName(t As Integer)
Select Case t
Case 1
ValidationTypeToValidationTypeName = "WholeNumber"
Case 2
ValidationTypeToValidationTypeName = "Decimal"
Case 3
ValidationTypeToValidationTypeName = "List (that is, dropdown)"
Case 4
ValidationTypeToValidationTypeName = "Date"
Case 5
ValidationTypeToValidationTypeName = "Time"
Case 6
ValidationTypeToValidationTypeName = "Text length"
Case 7
ValidationTypeToValidationTypeName = "Custom (that is, by formula)"
Case Else
MsgBox "Illegal validation type: " & t
End Select
End Function


' Examines a sheet for conrols, by looping
' through the sheet's Shapes property and inspecting each
' control in it.
'
Sub ExamineSheetForControls(ws As Worksheet, htable() As hashtable)
Dim s As Shape
Dim ControlInfo As String
Dim c As Range

' Loop over the sheet's Shapes
' property, getting the name of
' each control in it. Then get
' that control's cell from its
' TopLeftCell property.
'
For Each s In ws.Shapes
ControlInfo = s.Name
Set c = s.TopLeftCell
Call AddValue(htable(), c.AddressLocal, ControlInfo)
Next s
End Sub


Jocelyn Paine
http://www.j-paine.org

Jocelyn's Cartoons:
http://www.j-paine.org/blog/jocelyns_cartoons/

Jocelyn,

A control is not in the cell, so you cannot say that the cell is an input cell. A control is in a separate layer to the worksheet. Whilst a control has those properties, and you could determine the range that occupies the same part of the window as a control, I don't see what that gets you, it doesn't make them input cells. And ... a user could move them.

Bob,

Thanks for the reply.

We have a customer's spreadsheet with radio buttons in several columns. The spreadsheet labels indicate that these are intended for input: clicking on one changes its cell. That's an example of the kind of control-filled input cell I'm thinking of. I agree that buttons can be used for other things, most commonly to run a macro.

I suppose a user could change the position of those buttons, but surely doing so would be as perverse as changing a formula in the spreadsheet, or removing a data-validation entity. The user is only supposed to put their data into the spreadsheet, not to reprogram it.

What is the purpose of a control's TopLeftCell property? I'd assumed it denoted the cell that the control was "in" — or "above"; I take your point about the control being in a separate layer — and that the spreadsheet's author would normally intend that to be an input cell.

Jocelyn

I agree, but I have seen users change formulas and so on.

The TopLeftCell property just says which cell the top left of the control overlaps. Visually, this is not always obvious because the control may have some whitespace around it that makes it overlap a cell other than the obvious.

I guess I just don't get your objective. Why do you need to know this, and what are you going to do with it? Isn't it (visually) obvious that controls are for user interaction?

I guess I just don't get your objective. Why do you need to know this, and what are you going to do with it? Isn't it (visually) obvious that controls are for user interaction?

I want it for automatically analysing and transforming spreadsheets. For example, I''m thinking about automatically Web-ifying spreadsheets. To do this, I either need to compile the formulae to executable code in e.g. C++, or to put the spreadsheet onto a server. Either way, I want to automatically create an HTML input form which displays input fields corresponding to the spreadsheet's input cells. So I want a way to automatically discover which are the input cells.

There are various rules of thumb I might use.

For example, a blank cell with dependents but no precedents, immediately below or to the right of a label, is almost certainly an input cell.

A cell containing a data-validation entity is certainly (?) an input cell.

A cell containing a Forms or ActiveX radio button is likely to be an input field, because the only other use for radio buttons I can think of is to run a macro, but you wouldn't do that with an on-off switch, which is what a radio button is.

Does this explain why I want to know, and why I am trying to use the controls to help inform me?

To decide which cells contain data-validation entities, I loop through the sheet's UsedRange property, and see whether each cell's Validation property exists.


Dominik Petri over at EXCEL-L suggested I use the SpecialCells method:

For Each rngCell in wksMySheet.Cells.SpecialCells(xlCellTypeAllValidation)
' Code to check the type of validation
Next rngCell


Jocelyn Paine
http://www.j-paine.org

Jocelyn's Cartoons:
http://www.j-paine.org/blog/jocelyns_cartoons/[/QUOTE]

Yeah, that is fine for DV, but it doesn't alter the controls issue one iota, which is what we have been discussing. To get the details of the controls you can loop through the controls collection, the method depends upon whether it is forms or control toolbox.

Yeah, that is fine for DV, but it doesn't alter the controls issue one iota, which is what we have been discussing. To get the details of the controls you can loop through the controls collection, the method depends upon whether it is forms or control toolbox.

Yes, that's true, Bob. I just mentioned the revised data-validation code so that anyone reading my original didn't go away with an inefficient idea of how to collect the data-validation cells.

One more answer would be very helpful if you know it. At the moment, I'm finding the controls on a sheet by looping through its Shapes collection. Once I've got a Shape, how do I find out what kind of control it is? That is, whether it's a radio button, a text box, a normal press button, and so on. Are these subclasses of Shape? And if so, can I ask VBA to give me the Shape's class name, or a class type code? Would the means depend on whether it's a Forms control or an ActiveX one?

Many thanks
Jocelyn

If they are ActiveX controls on a userform then you can loop through the userform's Controls object collection. If they are ActiveX controls on a sheet then you can loop through the OLEObjects object collection in preference to the Shapes object collection. You can then use VBA.TypeName or TypeOf to determine between controls.

For Forms controls on a sheet you can drill down via the shape's OLEFormat property; the basic idea being:

Dim shp As Shape

For Each shp In Shapes
Debug.Print VBA.TypeName(shp.OLEFormat.Object)
Next shp




Hope that helps

Here is some other code I have published numerous times, but it shows some interesting aspects of shapes (dropdown arrows are in the shapes collection).



'----------------------------------------------------------------
Sub RemoveShapes()
'----------------------------------------------------------------
' Written by : Bob Phillips
' Inspired by: Debra Dalgleish & Dave Peterson
' Improved by: Dave Peterson (cater for forms combobox)
'---------------------------------------------------------------
' Synopsis: Checks each shape to be form control, and if it
' is a dropdown, it aims to retain it.
' One problem is taht the forms combobox which is
' also a form control, and is a dropdown, so it
' does not get deleted.
'
' Catered for by testing top left of shape, as
' Autofilter and Data Validation dropdowns do not
' seem to have a topleftcell address.
'---------------------------------------------------------------
Dim shp As Shape
Dim sTopLeft As String
Dim fOK As Boolean

For Each shp In ActiveSheet.Shapes

fOK = True

sTopLeft = ""
On Error Resume Next
sTopLeft = shp.TopLeftCell.Address
On Error GoTo 0

If shp.Type = msoFormControl Then
If shp.FormControlType = xlDropDown Then
If sTopLeft = "" Then
fOK = False 'keep it
End If
End If
End If

If fOK Then
shp.Delete
End If

Next shp

End Sub

Thanks to those who replied here and at EXCEL-L. Here, taking your advice, is attached a revised version of a test spreadsheet with VBA that scans for data-validation stuff and controls, and builds a table mapping cells to these.

Jocelyn

Oops. Uploaded the wrong file. This is the right one.

Jocelyn