currency icon (british pounds)

I want to format columns in excel using the currency icon in the toolbar.
When I click on this I get the accountancy formatting, ie the pound sign £
appears at the extreme left of the cell and not directly in front of the
number (eg £ 123.45 not £123.45). So how do I make the currency icon work
properly ?

Set a custom format (Format>Cells>Custom) of £#,##0.00;£(#,##0.00)

--
HTH

Bob Phillips

"hrh frank" <hrh [email protected]> wrote in message
news:[email protected]...
> I want to format columns in excel using the currency icon in the toolbar.
> When I click on this I get the accountancy formatting, ie the pound sign £
> appears at the extreme left of the cell and not directly in front of the
> number (eg £ 123.45 not £123.45). So how do I make the currency icon
work
> properly ?

Thanks BOB. your suggestion does work, but would take 5 keystrokes. I want
to be able to highlight a column, click on a toolbar icon, and for all the
numbers to be then formatted as currency. So I need to customise a toolbar
icon to do this. any ideas ?

"Bob Phillips" wrote:

> Set a custom format (Format>Cells>Custom) of £#,##0.00;£(#,##0.00)
>
> --
> HTH
>
> Bob Phillips
>
> "hrh frank" <hrh [email protected]> wrote in message
> news:[email protected]...
> > I want to format columns in excel using the currency icon in the toolbar.
> > When I click on this I get the accountancy formatting, ie the pound sign £
> > appears at the extreme left of the cell and not directly in front of the
> > number (eg £ 123.45 not £123.45). So how do I make the currency icon
> work
> > properly ?
>
>
>

Add the toolbar button, or create a template with a column already formatted
thus.

--
HTH

Bob Phillips

"hrh frank" <hrh [email protected]> wrote in message
news:[email protected]...
> Thanks BOB. your suggestion does work, but would take 5 keystrokes. I
want
> to be able to highlight a column, click on a toolbar icon, and for all the
> numbers to be then formatted as currency. So I need to customise a toolbar
> icon to do this. any ideas ?
>
> "Bob Phillips" wrote:
>
> > Set a custom format (Format>Cells>Custom) of £#,##0.00;£(#,##0.00)
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "hrh frank" <hrh [email protected]> wrote in message
> > news:[email protected]...
> > > I want to format columns in excel using the currency icon in the
toolbar.
> > > When I click on this I get the accountancy formatting, ie the pound
sign £
> > > appears at the extreme left of the cell and not directly in front of
the
> > > number (eg £ 123.45 not £123.45). So how do I make the currency
icon
> > work
> > > properly ?
> >
> >
> >

frank

Format>Style>Currency>Modify>Custom.

Change to Bob's format and OK your way out.

That sets the default for the Currency button.


Gord Dibben Excel MVP

On Wed, 27 Apr 2005 08:32:03 -0700, hrh frank <hrh
[email protected]> wrote:

>Thanks BOB. your suggestion does work, but would take 5 keystrokes. I want
>to be able to highlight a column, click on a toolbar icon, and for all the
>numbers to be then formatted as currency. So I need to customise a toolbar
>icon to do this. any ideas ?
>
>"Bob Phillips" wrote:
>
>> Set a custom format (Format>Cells>Custom) of £#,##0.00;£(#,##0.00)
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> "hrh frank" <hrh [email protected]> wrote in message
>> news:[email protected]...
>> > I want to format columns in excel using the currency icon in the toolbar.
>> > When I click on this I get the accountancy formatting, ie the pound sign £
>> > appears at the extreme left of the cell and not directly in front of the
>> > number (eg £ 123.45 not £123.45). So how do I make the currency icon
>> work
>> > properly ?
>>
>>
>>

Hi Gord,

Thanks for your reply. I have just tried it and it works perfrctly, thankyou.
Only one thing, you have to do this everytime you open a new workbook. Is
there a way to make this a permanent fix, without making a template ?

hrh frank

"Gord Dibben" wrote:

> frank
>
> Format>Style>Currency>Modify>Custom.
>
> Change to Bob's format and OK your way out.
>
> That sets the default for the Currency button.
>
>
> Gord Dibben Excel MVP
>
> On Wed, 27 Apr 2005 08:32:03 -0700, hrh frank <hrh
> [email protected]> wrote:
>
> >Thanks BOB. your suggestion does work, but would take 5 keystrokes. I want
> >to be able to highlight a column, click on a toolbar icon, and for all the
> >numbers to be then formatted as currency. So I need to customise a toolbar
> >icon to do this. any ideas ?
> >
> >"Bob Phillips" wrote:
> >
> >> Set a custom format (Format>Cells>Custom) of £#,##0.00;£(#,##0.00)
> >>
> >> --
> >> HTH
> >>
> >> Bob Phillips
> >>
> >> "hrh frank" <hrh [email protected]> wrote in message
> >> news:[email protected]...
> >> > I want to format columns in excel using the currency icon in the toolbar.
> >> > When I click on this I get the accountancy formatting, ie the pound sign £
> >> > appears at the extreme left of the cell and not directly in front of the
> >> > number (eg £ 123.45 not £123.45). So how do I make the currency icon
> >> work
> >> > properly ?
> >>
> >>
> >>
>
>

Add this to the Thisworkbook code module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Styles("Currency").NumberFormat = _
"_-£* #,##0.00_-;-£* #,##0.00_-;_-£* ""-""??_-;_-@_-"
Selection.Style = "Currency"
End Sub

Private Sub Workbook_Open()
ActiveWorkbook.Styles("Currency").NumberFormat = _
"£#,##0.00;£(#,##0.00)"
Selection.Style = "Currency"
End Sub


--
HTH

Bob Phillips

"hrh frank" <[email protected]> wrote in message
news:[email protected]...
> Hi Gord,
>
> Thanks for your reply. I have just tried it and it works perfrctly,
thankyou.
> Only one thing, you have to do this everytime you open a new workbook. Is
> there a way to make this a permanent fix, without making a template ?
>
> hrh frank
>
> "Gord Dibben" wrote:
>
> > frank
> >
> > Format>Style>Currency>Modify>Custom.
> >
> > Change to Bob's format and OK your way out.
> >
> > That sets the default for the Currency button.
> >
> >
> > Gord Dibben Excel MVP
> >
> > On Wed, 27 Apr 2005 08:32:03 -0700, hrh frank <hrh
> > [email protected]> wrote:
> >
> > >Thanks BOB. your suggestion does work, but would take 5 keystrokes. I
want
> > >to be able to highlight a column, click on a toolbar icon, and for all
the
> > >numbers to be then formatted as currency. So I need to customise a
toolbar
> > >icon to do this. any ideas ?
> > >
> > >"Bob Phillips" wrote:
> > >
> > >> Set a custom format (Format>Cells>Custom) of £#,##0.00;£(#,##0.00)
> > >>
> > >> --
> > >> HTH
> > >>
> > >> Bob Phillips
> > >>
> > >> "hrh frank" <hrh [email protected]> wrote in message
> > >> news:[email protected]...
> > >> > I want to format columns in excel using the currency icon in the
toolbar.
> > >> > When I click on this I get the accountancy formatting, ie the pound
sign £
> > >> > appears at the extreme left of the cell and not directly in front
of the
> > >> > number (eg £ 123.45 not £123.45). So how do I make the currency
icon
> > >> work
> > >> > properly ?
> > >>
> > >>
> > >>
> >
> >