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
|