Excel Cell Value Changing to TRUE when Number Entered

Question

I have a sheet with hundreds of userforms and macros running. When I enter a number in a textbox within the userform or directly into the problematic cell, it populates a cell with a value, however after I click out of the textbox, the cell automatically turns to TRUE. If I go overwrite the TRUE value with the intended value, the cell changes to the number, however equations referencing this cell do not recognize this value. I have the issue only on a few cells, the rest appear to not be affected by this and all have the same references, macros, etc. referencing them.

Any suggestions on what might be causing this issue, or a way to work around this? I have tried creating new sheets, copying from areas that don't have this issue, etc. but I keep getting this problem.

Here is a snippet of the code that applies to the affected area:

'Element Deficiency Matrix (1a to 3k)
With Worksheets("Abutment Data")
    For Each row In .Range("A2:J4").Rows
        For Each cell In row.Cells
            Set check = Me.MultiPage1.Pages(0).Controls.Add("Forms.Textbox.1")
            With check
                .ControlSource = "'" & cell.Parent.Name & "'!" & cell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
                .Left = Left
                .Font.Size = 6
                .Top = Top
                .Width = 20
                .Height = 12
                .BackColor = &HF6F6F6
                .BackStyle = fmBackStyleOpaque
                .BorderStyle = fmBorderStyleSingle
                .SpecialEffect = fmSpecialEffectFlat
                Left = Left + 24
            End With
        Next
        Left = 28
        Top = Top + 24
        Width = Width
    Next
End With

Thanks.

I would guess that you have formula in the cell referenced by your ControlSource and/or there's a formula in a different cell(s) that is referencing and changing that same cell, and/or you have something happening in the Worksheet_Change event that is changing the value after it's set by the textbox. — PeterT
Thanks for the suggestions! I checked Worksheet_Change and found nothing. There is no formula in the affected cells, and no precedent formulas. I traced all the Dependent cells and found no formulas affecting it either. — M. Albert

Jason Cameron Jason Cameron · Accepted Answer · 2019-02-01T20:54:02

Are any of the affect cells formatted as "Custom"? (TRUE/FALSE) Or perhaps, any cell referenced by a macro used alter the cells?

Trying to recreate a possible version of your problem based on my above hypothesis, I input 7 to the A1 cell with the Custom format True/False: It displays "True"

Making A2 =A1 it inherited the True/False Custom formatting. When I try referencing either of them in some formulas it seems to prioritize the True/False nature of the cell rather than the data behind the True/False state. E.g. Setting C1 to =A1+4 displays "True" and changed the formatting of C1 to Custom. As an example of how the custom formatting only interferes with some formulas: =Code(A1) will properly display 55 (the unicode reference for 7) and maintain its formatting.

Macros could also be causing this formatting inheritance from another stray cell, formatted as custom, referenced in their steps.

Given you have copied from unaffected areas (assuming you overwrote the formatting with the copied material), I would look into worksheet areas referenced by the macros. I would expect a malfunctioning macro to create more cells with the problem if it was within the macro itself.

You could also try selecting each column and setting them to its intended formatting to overwrite a stray cell. For Columns to the right of your data, block select them and either delete them or set their formatting to general. The concept here is to get per column formatting uniformity and clean up possible macro reference space (Keep in mind, I don't have any reference for what your Workspace/book looks like).

Excel Cell Value Changing to TRUE when Number Entered

2 Answers