The code below should do as you've requested. I've altered it so that
hopefully it will be easier for you to maintain/alter, if your worksheet
layout changes by using actual column letter identifiers instead of column
numbers. A tad slower, but easier to understand in the long run. You'll
note that where we need just a column number later on, the definitions are
like "BH1", with full cell address. When asking for the column number, the
row address of the cell is irrelevant, so
X = Range("BH1").Column
and
X = Range("BH99").Column
will return the same result, the column number for column BH.
The code has also been modified to test to see if the changed cell(s) are in
any of the 3 areas you've defined. It has been further modified to examine
Target (a Range, and a Range can be 1 cell or many) to get down to the
individual cell level. This allows you to cut and paste larger sections into
the sheet with proper results based on the location of the paste action you
make.
I've removed all error trapping, so that if something odd is going on,
you'll get notified with a program error message and you can press the
[Debug] button to see exactly what line in the code generated the error. I
don't think that's going to happen unless you have worksheet or workbook
protection activated.
As before, this code goes into the worksheet code module for the sheet that
you want it to work with. That leads to a short discussion of VBA and Excel
VBA in particular (especially in light of your "Pity ordinary BASIC is not
used now ..." statement) :
In Excel there are 4 general locations that you can put code:
there is one Workbook code module that is used to handle events at the
workbook level such as _Open() _BeforePrint() _BeforeSave() and others.
There is only one of these areas per workbook, but each has its own.
There is one Worksheet code module for each worksheet in the workbook. The
code in those is used primarily to handle worksheet level events such as
_Change() and _SelectionChange() - the code for each only works with that
sheet. If you wish for multiple sheets to act in the same way, easiest to
create one sheet with the code and then copy that sheet as many times as you
need. So the Worksheet_Change() event for Sheet1 is different, to the
system, than is the Worksheet_Change() event for any other sheet - even
though the routines may have the same names and exactly the same code within
them.
There is a code module associated with any UserForms you may add to the
workbook. These operate under the same rules as apply to Worksheet code
modules.
Finally there are 'general purpose' Modules - there can be none, one or many
of them. Their code is generally available to any other code in the
workbook, although access to individual routines or functions within them can
be restricted by the programmer. It is in these types of modules that Macros
created with the 'Record New Macro' feature are stored.
As for having moved on past 'ordinary' BASIC (a language unfortunately
maligned by 'real' programmers in days past - and one which I often used to
do rapid design with prior to laboriously coding up 'real' programs in a
variety of assembly languages in those very same days) ... well, you have to
consider the new name: VBA = Visual Basic for Applications. The Visual part
lets us know that it's designed to work within the GUI/Windows world rather
than being locked into a simple character oriented environment such as MS-DOS
(or TRS-DOS or C/PM, et al). The 'for Applications' portion tells us that
the core of the language has been extended to include a library that allows
you to communicate directly with the Application, so in VBA for Excel, the
Excel objects such as Workbook, Worksheet, Range, etc. are made readily
available without having to resort to #Includes or setting up Tools |
References to libraries to get the code to run properly right away.
The up side, for you, is that with a good background in BASIC, you've got a
good background in the core functions of all dialects of VBA - be it for
Excel, Access, Word [don't look at that one, it'll make you crazy - I think
the Word object model designers are now all occupying individual rubber rooms
<g>] or one of the other VBA enabled programs available as Visio or even
Outlook. The learning curve is toward learning the 'object model' for the
particular Application, and usually a search in the application's VB Editor
(not in the main application window) Help for "object model" will reveal it.
And recording macros to do what you want to code will often give you
revelations into what objects you need to address and what methods (actions)
and properties (appearance/behavior) you need to use to accomplish the task -
you can then use basic BASIC structures such as IF...THEN and FOR...NEXT to
automate the processes and make them more robust.
Private Sub Worksheet_Change(ByVal Target As Range)
'should work on even multiple cell changes
'you may change these Const Values to
'adjust for changes in the worksheet layout
'use no column number, just the column ID letter(s) here
Const CurrencyTypeColumn = "BH" ' where $, € and £ symbols are
'next two define what columns (inclusive) will change
'format to match symbol in CurrencyTypeColumn on a row
'we just use these to find a column number, so any row number
'will do fine, and using row 1 is consistent.
Const FirstSymChgColumn = "BI1"
Const LastSymChgColumn = "BQ1"
Const FirstAlwaysBritPound = "AT1"
Const LastAlwaysBritPound = "BG1"
Const FirstAlwaysEuro = "BR1"
Const LastAlwaysEuro = "BZ1"
'this variable will represent any individual
'cell within the entire range that is 'Target'
'it may be a single cell, it could be all cells
'on the sheet, or any size group in between
Dim anyCell As Range
For Each anyCell In Target ' may be 1 or many cells
If anyCell.Column >= Range(FirstSymChgColumn).Column _
And anyCell.Column <= Range(LastSymChgColumn).Column Then
'it is within columns BH:BQ, inclusive
Select Case Range(CurrencyTypeColumn & anyCell.Row)
Case Is = Chr$(36) ' U.S. dollar
anyCell.NumberFormat = "$#,##0.00"
Case Is = Chr$(128) ' Euros
anyCell.NumberFormat = "[$€-2] #,##0.00"
Case Is = Chr$(163) ' British Pound
anyCell.NumberFormat = "[$£-809]#,##0.00"
Case Else
'do nothing to change format in anyCell
End Select
'falls through and looks for/at next anyCell
ElseIf anyCell.Column >= Range(FirstAlwaysBritPound).Column _
And anyCell.Column <= Range(LastAlwaysBritPound).Column Then
'is within AT:BG, inclusive
anyCell.NumberFormat = "[$£-809]#,##0.00"
'falls through and looks for/at next anyCell
ElseIf anyCell.Column >= Range(FirstAlwaysEuro).Column _
And anyCell.Column <= Range(LastAlwaysEuro).Column Then
'is within BR:BZ, inclusive
anyCell.NumberFormat = "[$€-2] #,##0.00"
'falls through and looks for/at next anyCell
Else
'do absolutely nothing!
End If
Next
End Sub
Yendorian said:
Hi JL,
Thanks for your reply and work. this is exactly what I need. I tried the
macro in a new workbook and it worked excellently except that it returned
what seem like random signs in the cells after (E) instead of leaving the
original format (Number). (It was as if it was not reading the OR part of the
'IF Target.Column <3 Or Target.Column >5' line).
I then incorporated it into the prog I am working on - and nothing happened
! So I am doing something wrong somewhere. Here is how I altered your macro:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 61 Or Target.Column > 69 Then
'not in A(1) to BH(60) or in BR(70) onwards
Exit Sub
End If
On Error GoTo LeaveChange
'currency code (£,$,€) is in column BH
Select Case Range("BH" & Target.Row)
Case Is = Chr$(36) ' U.S. dollar symbol
Target.NumberFormat = "$#,##0.00"
Case Is = Chr$(128) ' Euro symbol
Target.NumberFormat = "[$€-2] #,##0.00"
Case Is = Chr$(163) ' British Pound symbol
Target.NumberFormat = "[$£-809]#,##0.00"
Case Else
'do nothing - no special symbol in A#
End Select
ExitSheetChange:
On Error GoTo 0 ' clear error trapping
Exit Sub
LeaveChange:
Resume ExitSheetChange
End Sub
The currency code is now in column BH (60) and the cells I want to change
are from BI (61) to BQ (69) inclusive.
Could I be a pain and ask how to add that, in as well as the above macro,
figures in cells AT (46) to BG (59) inclusive should be in £ sterling format
and those in BR (70) to BZ (78) inclusive should always have the € euro sign.
I think I have got the column numbers correct.
I've only been into VBA for a few weeks but explanations like yours are
extremely helpful. (Pity ordinary BASIC is not used now - I had almost
mastered that!!)
Thanks once again
:
Select the worksheet where you want this automatic formatting to take place
in columns C, D and E. Right-Click on the sheet's name tab, then choose
[View Code]. Copy and paste the code below into the code module that is
presented to you in the VB Editor. What this does is detect any change in
value in columns C, D or E on that sheet, and then test to see if one of the
special characters is 1st character in column A on that sheet, and if so,
sets formatting for the cell that changed value accordingly.
If this isn't what you desired, let me know.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 3 Or Target.Column > 5 Then
'not in C, D or E
Exit Sub
End If
On Error GoTo LeaveChange
Select Case Range("A" & Target.Row)
Case Is = Chr$(36) ' U.S. dollar symbol
Target.NumberFormat = "$#,##0.00"
Case Is = Chr$(128) ' Euro symbol
Target.NumberFormat = "[$€-2] #,##0.00"
Case Is = Chr$(163) ' British Pound symbol
Target.NumberFormat = "[$£-809]#,##0.00"
Case Else
'do nothing - no special symbol in A#
End Select
ExitSheetChange:
On Error GoTo 0 ' clear error trapping
Exit Sub
LeaveChange:
Resume ExitSheetChange
End Sub
:
In EXCEL 2003, i am transferring data from Sheet1 to Sheet2. The data
includes one of the three currency signs ($,€,£) in column 1 each time.
Can anyone help with a macro that will read the currency sign and then apply
that currency sign and format to other cells in the same row (C,D,E)- the
same process being repeated each time a new row of data is added.
e.g.
A B C D E
F
1 $ 25.00 $ 67.05 $ 98.00 $ 102.85
67.00
2 £ 34.00 £ 57.64 £ 87.00 £ 111.45
54.00
3 $ 198.00 $ 58.97 $ 25.00 $ 68.30
258.00
4 € 62.57 € 85.21 € 58.00 € 98.35
5.00
5 £ 7.54 £ 1.50 £ 69.00 £ 0.65
4.65
etc
Many thanks for all the advice given - this is a steep but fascinating
learning curve!
Yendorian
