VBA Express : Excel - Using custom format to display text

Excel

Using custom format to display text

Ease of Use

Easy

Version tested with

2000

Submitted by:

mdmackillop

Description:

A cell will display text but have a value of Zero, allowing it to be included in calculations. (Unless used as a divisor, where the text will fail anyway!)

Discussion:

Sometimes you want need to enter text values in numeric cells. This will upset any formulae dependent on these cells. As an alternative to entering error correcting formulae all over the worksheet (and beyond), this routine creates a custom format where the text represents the Zero value, thus eliminating the subsequent error. The code toggles between Custom and a specified number format, which can be easily changed to suit the user.

Code:

instructions for use

			Option Explicit 
 
Sub SetTextZero() 
    Dim Cel As Range, Txt As Variant 
    For Each Cel In Selection 
        If Left(Cel.NumberFormat, 2) <> ";;" Then 
            Cel.NumberFormat = ";;" & """" & Cel & """" 
            Cel.Value = 0 
             'Indicate "special" cells with chosen format
            Cel.Interior.ColorIndex = 34 
        Else 
            Txt = Split(Cel.NumberFormat, ";") 
            Cel.NumberFormat = "#,##0.00" 
            Cel.Value = Mid(Txt(2), 2, Len(Txt(2)) - 2) 
             'Reinstate standard format
            Cel.Interior.ColorIndex = 6 
        End If 
    Next 
End Sub

How to use:

Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
From the Menu, choose Insert-Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.

Test the code:

On the attached example, select the yellow cells and click the button. Value errors should disappear and the "special" cell formats will be coloured Blue.

Sample File:

TextZero.zip 9.24KB

Approved by mdmackillop

This entry has been viewed 310 times.