Formatig cells

[jpreman]

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

 

[Gary''s Student]

Format > Cells... > Number > Custom > "USD/TT/2008/"0000

 

[jpreman]

Thanks Gary''s Student.

That helped solve my first question.

Any solutions for the second part.

Regards


Preman

 

[Gord Dibben]

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

 

[jpreman]

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

 

[jpreman]

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]

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

 

[jpreman]

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]

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