Number format

[excelliot]

hi in excel comma format gives 3 digit separter e.g. 1,234,567,890

can i have format like this 1,23,45,67,890

i.e. first 3 digit comma separater form right side then 2 comma seprater onwards for remaining digits

:beerchug:

[sheeeng]

Hi all,

I'm also cannot do that formatting after I tried the codes below..:banghead:


Range("A1").Select
ActiveCell.FormulaR1C1 = "1234567890"
Selection.NumberFormat = "#,##,##,##,###"

Sorry...

[excelliot]

This i know but when number of digit fall below it it doesnot work

bcoz i'm not sure about number of digits.:dunno

[Bob Phillips]

hi in excel comma format gives 3 digit separter e.g. 1,234,567,890

can i have format like this 1,23,45,67,890

i.e. first 3 digit comma separater form right side then 2 comma seprater onwards for remaining digits


[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0

[excelliot]

[>=10000000]##\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0

it workjs upto 9 digits

what if it is more then 9 digits?

any vba macro can solve it?

[Bob Phillips]

it workjs upto 9 digits

what if it is more then 9 digits?

any vba macro can solve it?

Okay, but this changes it to text


Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
With Target
For i = Len(.Value) - 2 To 1 Step -2
.Value = Left(.Value, i) & "," & Right(.Value, Len(.Value) - i)
Next i
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

This is worksheet event code.

[excelliot]

this is not wprking??????



Okay, but this changes it to text


Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
With Target
For i = Len(.Value) - 2 To 1 Step -2
.Value = Left(.Value, i) & "," & Right(.Value, Len(.Value) - i)
Next i
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code.

[Bob Phillips]

It is worksheet event code, so you need to add it to the code module of the applicable worksheet.

[excelliot]

i have added , but no macro is displayed in tools>macro
pls tell me how it works


It is worksheet event code, so you need to add it to the code module of the applicable worksheet.

[JKwan]

You don't run the Worksheet Event Code, it executes when there are changes to the worksheet. The VBA is setup only to run IF it is updated in RANGE H1:H10.

XLD, the only problem is that you are adding a comma with an offset of 2 not 3 then 2.

[Bob Phillips]

XLD, the only problem is that you are adding a comma with an offset of 2 not 3 then 2.

How do you work that one out?

Actually, it could do with one change to facilitate editing



Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
With Target
.Value = CStr(Replace(.Value, ",", ""))
For i = Len(.Value) - 2 To 1 Step -2
.Value = Left(.Value, i) & "," & Right(.Value, Len(.Value) - i)
Next i
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

[excelliot]

so this macro isn't giving required format i.e. first 3 digit separtor by comma, then after words 2 digit for remaining

[Bob Phillips]

so this macro isn't giving required format i.e. first 3 digit separtor by comma, then after words 2 digit for remaining

Are you saying it is not working? If so, post your workbook.

[excelliot]

Are you saying it is not working? If so, post your workbook.
my sheet is attached
check it
:cool::cloud9:

[Bob Phillips]

:)
(file:///C:/Documents%20and%20Settings/bob/Desktop/comma.zip)

[excelliot]

:)
(file:///C:/Documents%20and%20Settings/bob/Desktop/comma.zip)

Oopssss
it doesnt work for decimles,
And it cahnges all type of format
i wants it to change that cells which has number format only
& i wants default right aligned, then where to change.

is it possible to make .xla(add ins) of this macro so that it can be applied to all excel file

thanks :whistle:

[Bob Phillips]

is it possible to make .xla(add ins) of this macro so that it can be applied to all excel file

Well yes, I suppose so. This add-in uses using application events, so we have to have a way of knowing whether it applies to a specific sheet or not, and if so, what range.

What I have done is to use worksheet names. So, when you want this format to apply to a worksheet, create a local name of xld.Format.Range, and give it the range value.

Enjoy!

[excelliot]

Well yes, I suppose so. This add-in uses using application events, so we have to have a way of knowing whether it applies to a specific sheet or not, and if so, what range.

Ehat I have doen is to use worksheet names. So, when you want this format to apply to a worksheet, create a local name of xld.Format.Range, and give it the range value.

Enjoy!

Thanks dere
how to create a local name of xld.Format.Range
i tried by fooll step

insert>name>xld.Format.Range

but it didinot worked

can u explain me in brief stp by step
:doh:

[Bob Phillips]

Thanks dere
how to create a local name of xld.Format.Range
i tried by fooll step

insert>name>xld.Format.Range

but it didinot worked

can u explain me in brief stp by step
:doh:

That won't, because as I said, it works by local/worksheet names.

Make sure you have the applicable worksheet selected, say Sheet1.

Insert>Name>Define ..., add a name of Sheet1!xld.Format.Range, and set the range in the refersto box, make sure it is an absolute formula.


A more robust method might be to make two amendments:
- add a menu to add/remove the names
- allow creation of a workbook name, and if this is et, all sheets in that workbook would have this formatting applicable.

[excelliot]

That won't, because as I said, it works by local/worksheet names.

Make sure you have the applicable worksheet selected, say Sheet1.

Insert>Name>Define ..., add a name of Sheet1!xld.Format.Range, and set the range in the refersto box, make sure it is an absolute formula.


A more robust method might be to make two amendments:
- add a menu to add/remove the names
- allow creation of a workbook name, and if this is et, all sheets in that workbook would have this formatting applicable.

i'm unable to do it?

i'm attching sheet & i 've defined name but it is not working ?

pls do it 4 me

thanking u:help:clap:

[Bob Phillips]

i'm unable to do it?

I was able to do it no problem. I have edited the first two numbers, the rest need to be done.

[excelliot]

I was able to do it no problem. I have edited the first two numbers, the rest need to be done...

Thanks gr8 work

:beerchug:

[sheeeng]

I was able to do it no problem. I have edited the first two numbers, the rest need to be done.

Do share wit us ur solution. :friends:

[Bob Phillips]

Do share wit us ur solution. :friends:

It is in the spreadsheet I posted.

[excelliot]

I dont know how u did

but it is not workin with number having decimle places

anybody can help on it?

[Bob Phillips]

I dont know how u did

but it is not workin with number having decimle places

anybody can help on it?

You didn't mention that. What is the format for decimal places, just strung along, or comma separated as well? If the latter, what sequence?

I will make the change when I hear from you but I will also add menu items to add/remove workbook names and/or worksheet names to simplify the process.

[excelliot]

You didn't mention that. What is the format for decimal places, just strung along, or comma separated as well? If the latter, what sequence?

I will make the change when I hear from you but I will also add menu items to add/remove workbook names and/or worksheet names to simplify the process.

Number of decimle to be selected to be left on user, generally 2 decimle places

sorry to bother u again but i manually apllied name as told by u but it didnot worked

pls mentione me in brief bcos im not used to it

u did not replied to my PM dont worry , waiting to here u

thanks once again

not to mention u r gr8 & fast coder i ever seen xld

[Bob Phillips]

I will make the change when I hear from you but I will also add menu items to add/remove workbook names and/or worksheet names to simplify the process.

As promised, here is the amended add-in.

This add-in gives you the ability to select a range within a worksheet, and to define custom formatting for that range. A range can be specified for any individual worksheets, within any workbook (as it is an add-in)., and the formats for each selected worksheet can be different, or the same, totally configurable.

The add-in is driven by a menu, which is added to 'Worksheet Menu Bar', with options to:
- prime a worksheet by specifying the range that the formatting will apply to and to define the format mask to apply,
- to clear a worksheet by removing its formatting details,
- to highlight the range in the activesheet that the add-in will format
- to remove any highlight on the activesheet
- a small about window.
At present, it only handles numeric formats, not dates or anything else. Another point to be aware of is that you need to have that add-in on any machine that a workbook that uses this formatting is deployed to, as the formatting code is maintained within the add-in.

There are a couple of amendments that I have in mind that I would welcome feedback on:
- whether I provide an option to apply the format to every worksheet in the activeworkbook - the same format applied to the same range on each worksheet (relatively simple), although a particular worksheet could then be primed independently and to a difefrent range/format mask
- similar to the previous, but where the workbook gets a default range and mask, but then each sheet can be defined independently at the same time
- the ability to easily add more formats to my supplied start list (a must-do IMO)
- provide an option to make any formatted workbooks stand-alone by adding the formatting code to that workbook
- other formats other than just numeric
- anything else?

Whilst I don't think this is the world's most necessary add-in, formatting numbers like #,##,##,##0.00 is a tad specific, but I did promise it to excelliot (http://www.vbaexpress.com/forum/member.php?userid=2582). I think it is quite a useful technique to be able to define worksheet specific rules, and would be interested if anyone has a really useful use for this technique (such as a row/column highlighter), let me know, I will consider building it.

Other than that, I would appreciate any feedback, bug reports, etc.

[excelliot]

As promised, here is the amended add-in.

This add-in gives you the ability to select a range within a worksheet, and to define custom formatting for that range. A range can be specified for any individual worksheets, within any workbook (as it is an add-in)., and the formats for each selected worksheet can be different, or the same, totally configurable.

The add-in is driven by a menu, which is added to 'Worksheet Menu Bar', with options to:
- prime a worksheet by specifying the range that the formatting will apply to and to define the format mask to apply,
- to clear a worksheet by removing its formatting details,
- to highlight the range in the activesheet that the add-in will format
- to remove any highlight on the activesheet
- a small about window.
At present, it only handles numeric formats, not dates or anything else. Another point to be aware of is that you need to have that add-in on any machine that a workbook that uses this formatting is deployed to, as the formatting code is maintained within the add-in.

There are a couple of amendments that I have in mind that I would welcome feedback on:
- whether I provide an option to apply the format to every worksheet in the activeworkbook - the same format applied to the same range on each worksheet (relatively simple), although a particular worksheet could then be primed independently and to a difefrent range/format mask
- similar to the previous, but where the workbook gets a default range and mask, but then each sheet can be defined independently at the same time
- the ability to easily add more formats to my supplied start list (a must-do IMO)
- provide an option to make any formatted workbooks stand-alone by adding the formatting code to that workbook
- other formats other than just numeric
- anything else?

Whilst I don't think this is the world's most necessary add-in, formatting numbers like #,##,##,##0.00 is a tad specific, but I did promise it to excelliot (http://www.vbaexpress.com/forum/member.php?userid=2582). I think it is quite a useful technique to be able to define worksheet specific rules, and would be interested if anyone has a really useful use for this technique (such as a row/column highlighter), let me know, I will consider building it.

Other than that, I would appreciate any feedback, bug reports, etc.

Great Work XLD

Yes I will notify if there is any bug
& if any more function which need to be added will be notifeid to u if i am unable to resolve it

yes i will share ammended sheet with u:clap: