| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatig cells
Thanks for reading this post.
How can I CUSTOM format a cell to have the the text USD/TT/2008/ precede the number entered in the cell. eg. Number entered is 0001; required format is USD/TT/2008/0001 Is their any way I could further improve on this. For instance in cell Aa if USD is entered the format in cell B1 should be USD/TT/2008/0001 and if GBP is entered it should be GBP/TT/2008/0016 and so on (0001 and 0016 are variables entered manually). Regards Preman |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatig cells
Format Cells... Number Custom "USD/TT/2008/"0000
-- Gary''s Student - gsnu200811 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatig cells
Thanks Gary''s Student.
That helped solve my first question. Any solutions for the second part. Regards Preman "Gary''s Student" wrote: Format Cells... Number Custom "USD/TT/2008/"0000 -- Gary''s Student - gsnu200811 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatig cells
Excel will not recognize 0001 as a number, only as text.
Try Custom format as "USD/TT/2008/"0000 Then enter just 1 or 16 To cover the USD and GBP part you could use sheet event code to choose the formatting. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False With Me.Range("B1:B10") Select Case UCase(Target.Value) Case "USD" .NumberFormat = """USD/TT/2008/""0000" Case "GBP" .NumberFormat = """GBP/TT/2008/""0000" Case "EURO" .NumberFormat = """EUR/TT/2008/""0000" End Select End With endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code" Copy/paste the above into that module. Edit to suit then Alt + q to return to the Excel window. Gord Dibben MS Excel MVP On Tue, 4 Nov 2008 09:25:01 -0800, jpreman wrote: Thanks for reading this post. How can I CUSTOM format a cell to have the the text USD/TT/2008/ precede the number entered in the cell. eg. Number entered is 0001; required format is USD/TT/2008/0001 Is their any way I could further improve on this. For instance in cell Aa if USD is entered the format in cell B1 should be USD/TT/2008/0001 and if GBP is entered it should be GBP/TT/2008/0016 and so on (0001 and 0016 are variables entered manually). Regards Preman |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatig cells
Thanks Gord for your response.
I haven't tried out your solution yet. Shall try it out and revert to you soon. Kind regards Preman "Gord Dibben" wrote: Excel will not recognize 0001 as a number, only as text. Try Custom format as "USD/TT/2008/"0000 Then enter just 1 or 16 To cover the USD and GBP part you could use sheet event code to choose the formatting. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False With Me.Range("B1:B10") Select Case UCase(Target.Value) Case "USD" .NumberFormat = """USD/TT/2008/""0000" Case "GBP" .NumberFormat = """GBP/TT/2008/""0000" Case "EURO" .NumberFormat = """EUR/TT/2008/""0000" End Select End With endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code" Copy/paste the above into that module. Edit to suit then Alt + q to return to the Excel window. Gord Dibben MS Excel MVP On Tue, 4 Nov 2008 09:25:01 -0800, jpreman wrote: Thanks for reading this post. How can I CUSTOM format a cell to have the the text USD/TT/2008/ precede the number entered in the cell. eg. Number entered is 0001; required format is USD/TT/2008/0001 Is their any way I could further improve on this. For instance in cell Aa if USD is entered the format in cell B1 should be USD/TT/2008/0001 and if GBP is entered it should be GBP/TT/2008/0016 and so on (0001 and 0016 are variables entered manually). Regards Preman |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatig cells
Hi Gord,
I tried it out as directed but had no outcome. After pasting script and saving the file tried entering USD in cell A1 expecting a desired results in B1. But had no effect. Repeated the same with GBP and the results were same. Seeking your kind assistance. Kind regards Preman "Gord Dibben" wrote: Excel will not recognize 0001 as a number, only as text. Try Custom format as "USD/TT/2008/"0000 Then enter just 1 or 16 To cover the USD and GBP part you could use sheet event code to choose the formatting. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False With Me.Range("B1:B10") Select Case UCase(Target.Value) Case "USD" .NumberFormat = """USD/TT/2008/""0000" Case "GBP" .NumberFormat = """GBP/TT/2008/""0000" Case "EURO" .NumberFormat = """EUR/TT/2008/""0000" End Select End With endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code" Copy/paste the above into that module. Edit to suit then Alt + q to return to the Excel window. Gord Dibben MS Excel MVP On Tue, 4 Nov 2008 09:25:01 -0800, jpreman wrote: Thanks for reading this post. How can I CUSTOM format a cell to have the the text USD/TT/2008/ precede the number entered in the cell. eg. Number entered is 0001; required format is USD/TT/2008/0001 Is their any way I could further improve on this. For instance in cell Aa if USD is entered the format in cell B1 should be USD/TT/2008/0001 and if GBP is entered it should be GBP/TT/2008/0016 and so on (0001 and 0016 are variables entered manually). Regards Preman |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatig cells
Did you paste it into the worksheet module............right-click on tab and
"View Code" Do you have numbers in B1:B10? I tested before posting and results were as expected in B1:B10 Have you somehow disabled events? Open Immediate Window in VBE and copt/paste this line Application.EnableEevents = True then hit Enter key. Gord On Tue, 4 Nov 2008 20:38:00 -0800, jpreman wrote: Hi Gord, I tried it out as directed but had no outcome. After pasting script and saving the file tried entering USD in cell A1 expecting a desired results in B1. But had no effect. Repeated the same with GBP and the results were same. Seeking your kind assistance. Kind regards Preman "Gord Dibben" wrote: Excel will not recognize 0001 as a number, only as text. Try Custom format as "USD/TT/2008/"0000 Then enter just 1 or 16 To cover the USD and GBP part you could use sheet event code to choose the formatting. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False With Me.Range("B1:B10") Select Case UCase(Target.Value) Case "USD" .NumberFormat = """USD/TT/2008/""0000" Case "GBP" .NumberFormat = """GBP/TT/2008/""0000" Case "EURO" .NumberFormat = """EUR/TT/2008/""0000" End Select End With endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code" Copy/paste the above into that module. Edit to suit then Alt + q to return to the Excel window. Gord Dibben MS Excel MVP On Tue, 4 Nov 2008 09:25:01 -0800, jpreman wrote: Thanks for reading this post. How can I CUSTOM format a cell to have the the text USD/TT/2008/ precede the number entered in the cell. eg. Number entered is 0001; required format is USD/TT/2008/0001 Is their any way I could further improve on this. For instance in cell Aa if USD is entered the format in cell B1 should be USD/TT/2008/0001 and if GBP is entered it should be GBP/TT/2008/0016 and so on (0001 and 0016 are variables entered manually). Regards Preman |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatig cells
Thanks a lot for all you help and patience.
Well I think the problem was with events. As suggested pasted the command to enable events. On hitting ENTER I got the following error message Run-time error '438' Object doesn't support this property or method. Nevertheless, the script is working fine Thanks a million. Kind regards Preman "Gord Dibben" wrote: Did you paste it into the worksheet module............right-click on tab and "View Code" Do you have numbers in B1:B10? I tested before posting and results were as expected in B1:B10 Have you somehow disabled events? Open Immediate Window in VBE and copt/paste this line Application.EnableEevents = True then hit Enter key. Gord On Tue, 4 Nov 2008 20:38:00 -0800, jpreman wrote: Hi Gord, I tried it out as directed but had no outcome. After pasting script and saving the file tried entering USD in cell A1 expecting a desired results in B1. But had no effect. Repeated the same with GBP and the results were same. Seeking your kind assistance. Kind regards Preman "Gord Dibben" wrote: Excel will not recognize 0001 as a number, only as text. Try Custom format as "USD/TT/2008/"0000 Then enter just 1 or 16 To cover the USD and GBP part you could use sheet event code to choose the formatting. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False With Me.Range("B1:B10") Select Case UCase(Target.Value) Case "USD" .NumberFormat = """USD/TT/2008/""0000" Case "GBP" .NumberFormat = """GBP/TT/2008/""0000" Case "EURO" .NumberFormat = """EUR/TT/2008/""0000" End Select End With endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code" Copy/paste the above into that module. Edit to suit then Alt + q to return to the Excel window. Gord Dibben MS Excel MVP On Tue, 4 Nov 2008 09:25:01 -0800, jpreman wrote: Thanks for reading this post. How can I CUSTOM format a cell to have the the text USD/TT/2008/ precede the number entered in the cell. eg. Number entered is 0001; required format is USD/TT/2008/0001 Is their any way I could further improve on this. For instance in cell Aa if USD is entered the format in cell B1 should be USD/TT/2008/0001 and if GBP is entered it should be GBP/TT/2008/0016 and so on (0001 and 0016 are variables entered manually). Regards Preman |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formatig cells
You got the 438 error message due to me adding an extra e
Application.EnableEevents = True should have been Application.EnableEvents = True But, if event code working.................OK Gord On Thu, 6 Nov 2008 05:17:01 -0800, jpreman wrote: Thanks a lot for all you help and patience. Well I think the problem was with events. As suggested pasted the command to enable events. On hitting ENTER I got the following error message Run-time error '438' Object doesn't support this property or method. Nevertheless, the script is working fine Thanks a million. Kind regards Preman "Gord Dibben" wrote: Did you paste it into the worksheet module............right-click on tab and "View Code" Do you have numbers in B1:B10? I tested before posting and results were as expected in B1:B10 Have you somehow disabled events? Open Immediate Window in VBE and copt/paste this line Application.EnableEevents = True then hit Enter key. Gord On Tue, 4 Nov 2008 20:38:00 -0800, jpreman wrote: Hi Gord, I tried it out as directed but had no outcome. After pasting script and saving the file tried entering USD in cell A1 expecting a desired results in B1. But had no effect. Repeated the same with GBP and the results were same. Seeking your kind assistance. Kind regards Preman "Gord Dibben" wrote: Excel will not recognize 0001 as a number, only as text. Try Custom format as "USD/TT/2008/"0000 Then enter just 1 or 16 To cover the USD and GBP part you could use sheet event code to choose the formatting. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False With Me.Range("B1:B10") Select Case UCase(Target.Value) Case "USD" .NumberFormat = """USD/TT/2008/""0000" Case "GBP" .NumberFormat = """GBP/TT/2008/""0000" Case "EURO" .NumberFormat = """EUR/TT/2008/""0000" End Select End With endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code" Copy/paste the above into that module. Edit to suit then Alt + q to return to the Excel window. Gord Dibben MS Excel MVP On Tue, 4 Nov 2008 09:25:01 -0800, jpreman wrote: Thanks for reading this post. How can I CUSTOM format a cell to have the the text USD/TT/2008/ precede the number entered in the cell. eg. Number entered is 0001; required format is USD/TT/2008/0001 Is their any way I could further improve on this. For instance in cell Aa if USD is entered the format in cell B1 should be USD/TT/2008/0001 and if GBP is entered it should be GBP/TT/2008/0016 and so on (0001 and 0016 are variables entered manually). Regards Preman |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel: match two cells in one sheet to two cells in another and return a third cells value | Excel Worksheet Functions | |||
| Cells won't change font color or show hi-lighted cells in document | Excel Discussion (Misc queries) | |||
| display a range of cells editible cells based on matching date | Excel Worksheet Functions | |||
| Setting of input cells as blue font and formula cells as black fon | Excel Discussion (Misc queries) | |||
| Print Formatig | Excel Discussion (Misc queries) | |||
Linear Mode
