Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format: General - Text - General | Excel Worksheet Functions | |||
excel numbers in general format i cant add cant change format | Excel Worksheet Functions | |||
Change CSV-load cell format from GENERAL to TEXT for numbers? | Excel Discussion (Misc queries) | |||
Change general format to US date format | Excel Discussion (Misc queries) | |||
Change General Format to Currency Format | Excel Worksheet Functions |