Change cell format from text to general on the fly

[JSnow]

I don't know if this is possible (although anything seems possible with vba),
but here's what I'm attempting. I'm using Excel 2003 and I have a column
formatted as 'text'. I'd like to switch any cell in that column to 'general'
formatting once the user enter '=' in the cell to start a formula. These
formulas will be very simple, basically linking the text data from one cell
to the current cell.

I can do this manually by selecting the cell, format > cell > number > text,
then enter my formula '=D12'. But I'm working with alot of data and it would
be much easier to have this format change on the fly when I enter the leading
equal sign.

I'm brand new to this forum, so thanks in advance for any direction.

 

[Mike H]

Hi,

Providing the formula are simple then this should do the trick. If you
expand into array formula then this will fail. Right click the sheet tab,
view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A"
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Target.NumberFormat = "General"
Target.Formula = Target.Formula
End If
Application.EnableEvents = True
End Sub

Mike

 

[Dave Peterson]

As soon as you start typing anything in the cell, no meaningful macro can do
anything.

But you could have an event macro that changes the format of the cell when the
user selects it -- or doubleclicks on it -- or even rightclicks on it.

 

[JSnow]

Mike, I'll happily admit I have no clue what your code means, but I'm pretty
sure that if I want this to effect column D I should change line 2 to read:

Const WS_RANGE As String = "D"

Am I seeing this correctly? I'll try it in the mean time.

Thanks.

 

[Gary''s Student]

This is an example using column B. It is a worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set b = Range("B:B")
If Intersect(t, b) Is Nothing Then Exit Sub
If t.NumberFormat = "General" Then Exit Sub
Dim s As String
s = t.Value
If Left(s, 1) <> "=" Then Exit Sub
Application.EnableEvents = False
t.Clear
t.Formula = s
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[JSnow]

First off, thanks! I updated column A:A to D and pasted your code below
another bit of code already called "Private Sub Worksheet_Change(ByVal Target
As Range)" and got a compile error: Ambiguous name detected:
Worksheet_Change. Is there a way for me to include the new code w/ the old?
Here's the original code:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Whoops
If Target.Count = 1 Then
If Target.Column = 3 Then 'this number picks the column: 1 is
column A, 2 is column B etc
If Target.Row > 1 Then 'ignore row 1 which is probably a header
Target.Value = UCase(Target.Value)
End If
End If
If Target.Column = 5 Then 'this number picks the column: 1 is
column A, 2 is column B etc
If Target.Row > 1 Then 'ignore row 1 which is probably a header
Target.Value = StrConv(Target.Value, vbProperCase)
End If
End If
End If

Whoops:
Application.EnableEvents = True

End Sub

 

[Mike H]

Hi,

Not tested but you should be able to paste it in where indicated below,
obviously without the Sub _End sub lines

 

[JSnow]

Wohoo! It worked Gary's Student!

However, it strips out all the other formatting for that cell. Example:
cell background should be colored and data should be left justified with 1
indent. Anyway to keep that other stuff?

 

[Gary''s Student]

Change:

t.Clear

to:

t.NumberFormat = "General"

 

[JSnow]

You sir, are the shnizz!

Change:

t.Clear

to:

t.NumberFormat = "General"