Change cell format from text to general on the fly
 

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.

Change cell format from text to general on the fly
 

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



"JSnow" wrote:

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.

Change cell format from text to general on the fly
 

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 wrote:

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.

--

Dave Peterson

Change cell format from text to general on the fly
 

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:D"

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

Thanks.

"Mike H" wrote:

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



"JSnow" wrote:

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.

Change cell format from text to general on the fly
 

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


--
Gary''s Student - gsnu200800


"JSnow" wrote:

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.

Change cell format from text to general on the fly
 

First off, thanks! I updated column A:A to D: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" wrote:

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



"JSnow" wrote:

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.

Change cell format from text to general on the fly
 

Hi,

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

"JSnow" wrote:

First off, thanks! I updated column A:A to D: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
PASTE IN HERE
End Sub

"Mike H" wrote:

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



"JSnow" wrote:

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.

Change cell format from text to general on the fly
 

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" wrote:

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


--
Gary''s Student - gsnu200800

Change cell format from text to general on the fly
 

Change:

t.Clear

to:

t.NumberFormat = "General"
--
Gary''s Student - gsnu200800


"JSnow" wrote:

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" wrote:

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


--
Gary''s Student - gsnu200800

Change cell format from text to general on the fly
 

You sir, are the shnizz!

"Gary''s Student" wrote:

Change:

t.Clear

to:

t.NumberFormat = "General"
--
Gary''s Student - gsnu200800


"JSnow" wrote:

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" wrote:

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


--
Gary''s Student - gsnu200800