R
Rodels
Hi everyone.
I want to create an alert that flashes a msgbox when a sheet is activated
and contains data that is flagged as invalid by using the validation circles.
In a large sheet, it is tricky to find all the cells that may contain
invalid data which is why I want to be able to create a msgbox that lists the
addresses or something like that.
By looping through the shapes in the sheet, I can determine that these
errors exist but I can't find out where they are as the data validation oval
does not appear to have a .topleftcell location. I can find out where it is
in absolute terms -
..top and .left. etc..
How do I reverse engineer screen position to a cell address?
Sub shaper()
Dim shp As Shape
Dim x As Long
Dim s_addr As String
For Each shp In ActiveSheet.Shapes
If shp.Type = 1 Then ' oval
x = x + 1
s_addr = _
Cells(shp.TopLeftCell.Row, shp.TopLeftCell.Column).Address ' fails on type 1
Debug.Print shp.Type & " - "; shp.Name & " - " & s_addr
End If
Next shp
If x <> 0 Then MsgBox "Sheet contains " & x & " Data Validation Errors."
End Sub
Help always appreciated!
Many thanks!
Robert
I want to create an alert that flashes a msgbox when a sheet is activated
and contains data that is flagged as invalid by using the validation circles.
In a large sheet, it is tricky to find all the cells that may contain
invalid data which is why I want to be able to create a msgbox that lists the
addresses or something like that.
By looping through the shapes in the sheet, I can determine that these
errors exist but I can't find out where they are as the data validation oval
does not appear to have a .topleftcell location. I can find out where it is
in absolute terms -
..top and .left. etc..
How do I reverse engineer screen position to a cell address?
Sub shaper()
Dim shp As Shape
Dim x As Long
Dim s_addr As String
For Each shp In ActiveSheet.Shapes
If shp.Type = 1 Then ' oval
x = x + 1
s_addr = _
Cells(shp.TopLeftCell.Row, shp.TopLeftCell.Column).Address ' fails on type 1
Debug.Print shp.Type & " - "; shp.Name & " - " & s_addr
End If
Next shp
If x <> 0 Then MsgBox "Sheet contains " & x & " Data Validation Errors."
End Sub
Help always appreciated!
Many thanks!
Robert