Detecting Overfilled (XXXXXXXX) Cells Through VBA?

[CaliKidd]

I have a protected sheet with many columns. Certain numeric cells in various columns are unlocked for data entry. The font size I am using is Arial 10. The column widths are fixed at 12 (89 pixels) because I don't want the user changing them as this can throw off the pre-set print and zoom settings. I do not want to make the columns any wider because I have a lot of columns in this worksheet and making them wider will just require the user to horizontally scroll more.

While this pre-set font size and column width setting allows the user to enter a fairly large number, if the number entered is too large then the cell display will be "XXXXXXXX". While Excel will still recognizes the overfilled cell value as a number, it looks ugly on the monitor and on printed reports.

I recognize that I can format the cells with the "Shrink To Fit" option, but I don't like this option since it only shrinks the applicable (overfilled) cells and leaves all the others as is. The result is different size fonts, which looks awkward.

My question is:
Is it possible through VBA to detect when a numeric cell is overfilled and displays "XXXXXXXX"?

If so, I can prompt the user to reduce the font size, reduce the number of digits after the decimal place (currently 4), and/or change to a narrower font. I could make these changes "global" across the entire data entry area so all fonts are displayed in the same size. The user cannot change the font face or size currently because the worksheet is protected.

Any ideas?

 

[Jaafar Tribak]

Since you have the Font size and column widths fixed you could figure out the maximum number of digits that the cells can hold. I have tested it and it allawed a maximum of 12 digits. You could then check the number of digits entered in the worksheet_Change event handler and prompt the user if the number of digits exceeds 12.

However the above depends on the worksheet zoom as well as monitor resolution which can be changed even if the worksheet is protected hence making this approach less secure.

Here is a workaround that should work regardless of the Font and columnWidth sizes or worksheet current zoom :

Place this in the worksheet module :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If IsRangeOverFilled(Target) Then
        MsgBox "Cell is overfilled." & vbNewLine & _
        "Reduce the font size or reduce the number of" & _
        "digits after the decimal place .", vbCritical
        Target.Activate
    End If
    
End Sub

Private Function IsRangeOverFilled(Rng As Range) As Boolean

    Dim vColumnWidth As Variant
    Dim bShtProtected As Boolean
    
    Application.ScreenUpdating = False
    If Me.ProtectContents Then bShtProtected = _
    True: Me.Unprotect "blabla"
    vColumnWidth = Rng.ColumnWidth
    Rng.EntireColumn.AutoFit
    If Rng.ColumnWidth > vColumnWidth Then
          IsRangeOverFilled = True
    End If
    Rng.ColumnWidth = vColumnWidth
    If bShtProtected Then Me.Protect "blabla"
    Application.ScreenUpdating = True

End Function

 

[CaliKidd]

Hi Jaafar,

Thanks for replying. I see the logic of your solution and I will experiment with it. I'm glad to see that this is a "trappable" condition. I appreciate your expertise!

 

[ZVI]

Below is another version of the code for IsRangeOverFilled:

Private Function IsRangeOverFilled(Rng As Range) As Boolean
  IsRangeOverFilled = Not Rng.Find("##*", LookIn:=xlValues, LookAt:=xlWhole) Is Nothing
End Function

 

[Jaafar Tribak]

Below is another version of the code for IsRangeOverFilled:

Private Function IsRangeOverFilled(Rng As Range) As Boolean
  IsRangeOverFilled = Not Rng.Find("##*", LookIn:=xlValues, LookAt:=xlWhole) Is Nothing
End Function

Thanks Vladimir. I never thought the Find Method would look at the displayed characters as opposed to the actual values. Definitely the best solution.

 

[ZVI]

... I never thought the Find Method would look at the displayed characters as opposed to the actual values. Definitely the best solution.

Thank you Jaafar.
Seems it’s undeclared feature of the Find method.
Vlad

 

[Peter_SSs]

Thank you Jaafar.
Seems it’s undeclared feature of the Find method.
Vlad

Is that a good or bad 'feature'? I would have thought more cons than pros.

For example I might have a workbook with several sheets and I know I have the number 123456789 in one or more columns of A:E of sheet 'abc'. I want to delete the other columns and just leave the column(s) that contain this number. So I use something like this ..

Sub DelCols()
    Dim c As Long
    Dim Found As Range
    
    With Sheets("abc")
        For c = 5 To 1 Step -1
            Set Found = .Columns(c).Find(What:=123456789, _
                LookIn:=xlValues, LookAt:=xlWhole)
            If Found Is Nothing Then
                .Columns(c).Delete
            End If
        Next c
    End With
End Sub

.. but if somebody has been to that sheet and narrowed the column widths I could possibly lose all my data!

It would be cumbersome to have to record the column widths, expand the columns to be safe, search and re-set the column widths, or read the data into an array and check there etc.

 

[CaliKidd]

Thanks Vladimir,

I am always amazed at the expertise of people in this forum. It motivates me to learn more. Your solution is very concise!

 

[ZVI]

Is that a good or bad 'feature'? I would have thought more cons than pros.

Hi Peter,

Nice point as everything undocumented is potentially dangerous.
Sometimes it’s useful. But sometimes such "feature" can be considered as the bug or at least as disadvantage.

By the way, Find method skips the cells hidden by autofilter.
And this behavior is also not documented in VBA Help AFAIK.
But the popular suggestion on the Board is in setting range by Range.End(xlUp) property similar to:
Set Rng = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
Rng.Find ...

In this case the code similar to yours with Find and rows hidden by autofilter can be dangerous as well.
It can be easily eliminated by:
With Sheets(1)
If .FilterMode Then .ShowAllData
End With
but who cares?
Ok, me is the one of.

There is one more known disadvantage of Find method - the search starts not from the first cell of the searched range.
And if What value is in the first cell and in another cell of the range then Find returns reference to that another one.
But this “feature” is documented and therefore can be taken into account

Kind regards,
Vlad

 

[ZVI]

Thanks Vladimir,

I am always amazed at the expertise of people in this forum. It motivates me to learn more. Your solution is very concise!

Happy to help CaliKidd !
I am admiring and learning here too