Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto number formatting
I wonder if anyone knows how to have a column in Excel 2003
automatically formatted to a particular currency when this particular currency is selected from a drop down list in another cell? i.e,; Cell H14 has a drop down list to select USD, Canadian or Indonesian Rupiah. Cells G20 to G30 contain the Price column that I want to have automatically formatted to either 2 decimal places for USD currency or zero decimal places for the Indonesion Rupiah depending on what is selected in H14. Should I use a formula to start a macro or something like that. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto number formatting
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Select Case Target.Value Case "USD" Me.Range("G20:G30").NumberFormat = "[$USD] #,##0.00" Case "INR" Me.Range("G20:G30").NumberFormat = "[$INR] #,##0" Case "CAD" Me.Range("G20:G30").NumberFormat = "[$CAD] #,##0.00" End Select endit: Application.EnableEvents = True End Sub This is event code. Right-click on the sheet tab and "View code". Copy/paste the code into that sheet module. Gord Dibben MS Excel MVP On Thu, 13 Sep 2007 16:23:34 -0700, Randy wrote: I wonder if anyone knows how to have a column in Excel 2003 automatically formatted to a particular currency when this particular currency is selected from a drop down list in another cell? i.e,; Cell H14 has a drop down list to select USD, Canadian or Indonesian Rupiah. Cells G20 to G30 contain the Price column that I want to have automatically formatted to either 2 decimal places for USD currency or zero decimal places for the Indonesion Rupiah depending on what is selected in H14. Should I use a formula to start a macro or something like that. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto number formatting
I'm guessing, given the values produced by the drop-down list, that you
could replace your entire Select Case block with this single line of code... Me.Range("G20:G30").NumberFormat = "[$" & Target.Value &"] #,##0.00" Rick "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Select Case Target.Value Case "USD" Me.Range("G20:G30").NumberFormat = "[$USD] #,##0.00" Case "INR" Me.Range("G20:G30").NumberFormat = "[$INR] #,##0" Case "CAD" Me.Range("G20:G30").NumberFormat = "[$CAD] #,##0.00" End Select endit: Application.EnableEvents = True End Sub This is event code. Right-click on the sheet tab and "View code". Copy/paste the code into that sheet module. Gord Dibben MS Excel MVP On Thu, 13 Sep 2007 16:23:34 -0700, Randy wrote: I wonder if anyone knows how to have a column in Excel 2003 automatically formatted to a particular currency when this particular currency is selected from a drop down list in another cell? i.e,; Cell H14 has a drop down list to select USD, Canadian or Indonesian Rupiah. Cells G20 to G30 contain the Price column that I want to have automatically formatted to either 2 decimal places for USD currency or zero decimal places for the Indonesion Rupiah depending on what is selected in H14. Should I use a formula to start a macro or something like that. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto number formatting
Probably but OP wants no decimals if the choice is INR
I started with two choices.......USD and INR then realized OP also wanted CAD so the code just kinda grew. Gord On Thu, 13 Sep 2007 22:46:06 -0400, "Rick Rothstein \(MVP - VB\)" wrote: I'm guessing, given the values produced by the drop-down list, that you could replace your entire Select Case block with this single line of code... Me.Range("G20:G30").NumberFormat = "[$" & Target.Value &"] #,##0.00" Rick "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Select Case Target.Value Case "USD" Me.Range("G20:G30").NumberFormat = "[$USD] #,##0.00" Case "INR" Me.Range("G20:G30").NumberFormat = "[$INR] #,##0" Case "CAD" Me.Range("G20:G30").NumberFormat = "[$CAD] #,##0.00" End Select endit: Application.EnableEvents = True End Sub This is event code. Right-click on the sheet tab and "View code". Copy/paste the code into that sheet module. Gord Dibben MS Excel MVP On Thu, 13 Sep 2007 16:23:34 -0700, Randy wrote: I wonder if anyone knows how to have a column in Excel 2003 automatically formatted to a particular currency when this particular currency is selected from a drop down list in another cell? i.e,; Cell H14 has a drop down list to select USD, Canadian or Indonesian Rupiah. Cells G20 to G30 contain the Price column that I want to have automatically formatted to either 2 decimal places for USD currency or zero decimal places for the Indonesion Rupiah depending on what is selected in H14. Should I use a formula to start a macro or something like that. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto number formatting
On Sep 14, 10:06 am, Gord Dibben <gorddibbATshawDOTca wrote:
Probably but OP wants no decimals if the choice is INR I started with two choices.......USD and INR then realized OP also wanted CAD so the code just kinda grew. Gord On Thu, 13 Sep 2007 22:46:06 -0400, "Rick Rothstein \(MVP - VB\)" wrote: I'm guessing, given the values produced by the drop-down list, that you could replace your entire Select Case block with this single line of code... Me.Range("G20:G30").NumberFormat = "[$" & Target.Value &"] #,##0.00" Rick "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Select Case Target.Value Case "USD" Me.Range("G20:G30").NumberFormat = "[$USD] #,##0.00" Case "INR" Me.Range("G20:G30").NumberFormat = "[$INR] #,##0" Case "CAD" Me.Range("G20:G30").NumberFormat = "[$CAD] #,##0.00" End Select endit: Application.EnableEvents = True End Sub This is event code. Right-click on the sheet tab and "View code". Copy/paste the code into that sheet module. Gord Dibben MS Excel MVP On Thu, 13 Sep 2007 16:23:34 -0700, Randy wrote: I wonder if anyone knows how to have a column in Excel 2003 automatically formatted to a particular currency when this particular currency is selected from a drop down list in another cell? i.e,; Cell H14 has a drop down list to select USD, Canadian or Indonesian Rupiah. Cells G20 to G30 contain the Price column that I want to have automatically formatted to either 2 decimal places for USD currency or zero decimal places for the Indonesion Rupiah depending on what is selected in H14. Should I use a formula to start a macro or something like that.- Hide quoted text - - Show quoted text - Excuse my ignorance, but could you provide a little more simple description as I am not a programmer. I am merely an Excel user that thought he knew a lot about Excel and constantly gets reminded how little he actually does know! Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto number formatting
What do you need to know?
The code when pasted into the sheet module as directed will change the format of the cells G20:G30 when you choose an item from your dropdown list in H14. Your dropdown list will contain USD, CAD, INR(I'm guessing that INR is Indian Rupiah) The code does not convert the values from USD to CAD or INR, just changes the format. If you want an actual conversion you would need to have a list of conversion factors to reference as well. Gord On Fri, 14 Sep 2007 20:09:10 -0700, Randy wrote: Excuse my ignorance, but could you provide a little more simple description as I am not a programmer. I am merely an Excel user that thought he knew a lot about Excel and constantly gets reminded how little he actually does know! Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto number formatting
Probably but OP wants no decimals if the choice is INR
Damn! I missed the "no decimals" part. Rick |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto number formatting
Probably but OP wants no decimals if the choice is INR
Damn! I missed the "no decimals" part. Of course, we can still save the one-liner replacement for your Select Case block... Me.Range("G20:G30").NumberFormat = "[$" & target.Value & "] #,##0" & _ IIf(target.Value = "INR", "", ".00") Rick |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto number formatting
This seems to do the trick without select case.
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Me.Range("G20:G30").NumberFormat = "[$" & Target.Value & "] #,##0" & _ IIf(Target.Value = "INR", "", ".00") endit: Application.EnableEvents = True End Sub Thanks for the tip. Gord On Sat, 15 Sep 2007 12:07:54 -0400, "Rick Rothstein \(MVP - VB\)" wrote: Probably but OP wants no decimals if the choice is INR Damn! I missed the "no decimals" part. Of course, we can still save the one-liner replacement for your Select Case block... Me.Range("G20:G30").NumberFormat = "[$" & target.Value & "] #,##0" & _ IIf(target.Value = "INR", "", ".00") Rick |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto number formatting
On Sep 15, 11:21 pm, Gord Dibben <gorddibbATshawDOTca wrote:
This seems to do the trick without select case. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Me.Range("G20:G30").NumberFormat = "[$" & Target.Value & "] #,##0" & _ IIf(Target.Value = "INR", "", ".00") endit: Application.EnableEvents = True End Sub Thanks for the tip. Gord On Sat, 15 Sep 2007 12:07:54 -0400, "Rick Rothstein \(MVP - VB\)" wrote: Probably but OP wants no decimals if the choice is INR Damn! I missed the "no decimals" part. Of course, we can still save the one-liner replacement for your Select Case block... Me.Range("G20:G30").NumberFormat = "[$" & target.Value & "] #,##0" & _ IIf(target.Value = "INR", "", ".00") Rick- Hide quoted text - - Show quoted text - Sorry, Gord, I misinterpretted the instructions........... After pasting in the code I realized that I need the format fine tuned just a bit more. I hope you don't mind assisting? I've added a few more currencies to H14. I'd like USD, CAD, IDR, GBP, EURO and AUD as the choices. When one of these are choosen I want Cells F20:G38 formatted to 2 decimal places for all but the IDR currency. IDR currency will be shown without any decimal places. All of these cells need not display a currency symbol, e.g., $. When this is accomplished, I'd like Cells G39:G41 formatted the same as above but in this case I'd like Cell G41 to display the currency symbol before the value, i.e., $, € or £. In the case of the Indonesian Rupiah currency, the symbol (Rp.) should be displayed following the value. Thanks for helping me out. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto number formatting
For the first range F20:G38 change the INR to IDR in the code posted below.
Also change the G20:G30 range to Me.Range("F20:G38, G39:G40") No currency symbol will be displayed. For the range G41, not so easy. Do you want $100.00 , €100.00, £100.00 and 100Rp? I think you will have to go back to a Select Case code as per my first posting and make a case for each format as USD, AUD etc. are selected from the DV dropdown. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Me.Range("F20:G38, G39:G40").NumberFormat = "[$" & Target.Value & "] #,##0" & _ IIf(Target.Value = "IDR", "", ".00") Select Case Target.Value Case "USD", "AUD", "CAD" Me.Range("G41").NumberFormat = "$#,##0.00" Case "IDR" Me.Range("G41").NumberFormat = "#,##0Rp" Case "GBP" Me.Range("G41").NumberFormat = "£#,##0.00" Case "EURO" Me.Range("G41").NumberFormat = "€#,##0.00" End Select endit: Application.EnableEvents = True End Sub Gord On Sat, 15 Sep 2007 18:25:15 -0700, Randy wrote: On Sep 15, 11:21 pm, Gord Dibben <gorddibbATshawDOTca wrote: This seems to do the trick without select case. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Me.Range("G20:G30").NumberFormat = "[$" & Target.Value & "] #,##0" & _ IIf(Target.Value = "INR", "", ".00") endit: Application.EnableEvents = True End Sub Thanks for the tip. Gord On Sat, 15 Sep 2007 12:07:54 -0400, "Rick Rothstein \(MVP - VB\)" wrote: Probably but OP wants no decimals if the choice is INR Damn! I missed the "no decimals" part. Of course, we can still save the one-liner replacement for your Select Case block... Me.Range("G20:G30").NumberFormat = "[$" & target.Value & "] #,##0" & _ IIf(target.Value = "INR", "", ".00") Rick- Hide quoted text - - Show quoted text - Sorry, Gord, I misinterpretted the instructions........... After pasting in the code I realized that I need the format fine tuned just a bit more. I hope you don't mind assisting? I've added a few more currencies to H14. I'd like USD, CAD, IDR, GBP, EURO and AUD as the choices. When one of these are choosen I want Cells F20:G38 formatted to 2 decimal places for all but the IDR currency. IDR currency will be shown without any decimal places. All of these cells need not display a currency symbol, e.g., $. When this is accomplished, I'd like Cells G39:G41 formatted the same as above but in this case I'd like Cell G41 to display the currency symbol before the value, i.e., $, € or £. In the case of the Indonesian Rupiah currency, the symbol (Rp.) should be displayed following the value. Thanks for helping me out. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto number formatting
On Sep 16, 11:41 pm, Gord Dibben <gorddibbATshawDOTca wrote:
For the first range F20:G38 change the INR to IDR in the code posted below. Also change the G20:G30 range to Me.Range("F20:G38, G39:G40") No currency symbol will be displayed. For the range G41, not so easy. Do you want $100.00 , €100.00, £100.00 and 100Rp? I think you will have to go back to a Select Case code as per my first posting and make a case for each format as USD, AUD etc. are selected from the DV dropdown. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Me.Range("F20:G38, G39:G40").NumberFormat = "[$" & Target.Value & "] #,##0" & _ IIf(Target.Value = "IDR", "", ".00") Select Case Target.Value Case "USD", "AUD", "CAD" Me.Range("G41").NumberFormat = "$#,##0.00" Case "IDR" Me.Range("G41").NumberFormat = "#,##0Rp" Case "GBP" Me.Range("G41").NumberFormat = "£#,##0.00" Case "EURO" Me.Range("G41").NumberFormat = "€#,##0.00" End Select endit: Application.EnableEvents = True End Sub Gord On Sat, 15 Sep 2007 18:25:15 -0700, Randy wrote: On Sep 15, 11:21 pm, Gord Dibben <gorddibbATshawDOTca wrote: This seems to do the trick without select case. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Me.Range("G20:G30").NumberFormat = "[$" & Target.Value & "] #,##0" & _ IIf(Target.Value = "INR", "", ".00") endit: Application.EnableEvents = True End Sub Thanks for the tip. Gord On Sat, 15 Sep 2007 12:07:54 -0400, "Rick Rothstein \(MVP - VB\)" wrote: Probably but OP wants no decimals if the choice is INR Damn! I missed the "no decimals" part. Of course, we can still save the one-liner replacement for your Select Case block... Me.Range("G20:G30").NumberFormat = "[$" & target.Value & "] #,##0" & _ IIf(target.Value = "INR", "", ".00") Rick- Hide quoted text - - Show quoted text - Sorry, Gord, I misinterpretted the instructions........... After pasting in the code I realized that I need the format fine tuned just a bit more. I hope you don't mind assisting? I've added a few more currencies to H14. I'd like USD, CAD, IDR, GBP, EURO and AUD as the choices. When one of these are choosen I want Cells F20:G38 formatted to 2 decimal places for all but the IDR currency. IDR currency will be shown without any decimal places. All of these cells need not display a currency symbol, e.g., $. When this is accomplished, I'd like Cells G39:G41 formatted the same as above but in this case I'd like Cell G41 to display the currency symbol before the value, i.e., $, € or £. In the case of the Indonesian Rupiah currency, the symbol (Rp.) should be displayed following the value. Thanks for helping me out.- Hide quoted text - - Show quoted text - Gord, I pasted in the code and made a few changes........ decided to use full words rather than the acronyms. I had everything in Cells F20:G40 working fine but then when I plugged in the remainder of the code for G41 it all quit working! Now it seems like whatever I select for currency, the number formatting is always to the second decimal place and G41 gets no currency symbol of any kind or decimal place formatting. I likely screwed something up. Could you please have a look at what I've altered? Thanks. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Select Case Target.Value Case "U.S. Dollar" Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00" Case "Indonesian Rupiah" Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0" Case "Canadian Dollar" Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00" Case "British Pound" Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00" Case "European Euro" Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00" Case "Australian Dollar" Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00" If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Me.Range("F20:G38, G39:G40").NumberFormat = "[$" & Target.Value & "] #,##0" & _ IIf(Target.Value = "Indonesian Rupiah", "", ". 00") Select Case Target.Value Case "U.S. Dollar", "Australian Dollar", "Canadian Dollar" Me.Range("G41").NumberFormat = "$#,##0.00" Case "Indonesian Rupiah" Me.Range("G41").NumberFormat = "#,##0 Rp" Case "British Pound" Me.Range("G41").NumberFormat = "£#,##0.00" Case "European Euro" Me.Range("G41").NumberFormat = "€#,##0.00" End Select endit: Application.EnableEvents = True End Sub |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto number formatting
Yes, the code is messed up royally.
I'm having a hard time keeping up with your ever-changing formatting and requirements. For starters.............. You have a DV list in H14 with these items. Right? Australian Dollar U.S. Dollar Canadian Dollar European Euro Indonesian Rupiah British Pound What do you want to see in F20:G38 and G39:40 if you pick say U.S. Dollar from the list? How about European Euro? What do you want to see in G41 with a pick? Gord On Sun, 16 Sep 2007 15:25:38 -0700, Randy wrote: Gord, I pasted in the code and made a few changes........ decided to use full words rather than the acronyms. I had everything in Cells F20:G40 working fine but then when I plugged in the remainder of the code for G41 it all quit working! Now it seems like whatever I select for currency, the number formatting is always to the second decimal place and G41 gets no currency symbol of any kind or decimal place formatting. I likely screwed something up. Could you please have a look at what I've altered? Thanks. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto number formatting
On Sep 17, 6:13 am, Gord Dibben <gorddibbATshawDOTca wrote:
Yes, the code is messed up royally. I'm having a hard time keeping up with your ever-changing formatting and requirements. For starters.............. You have a DV list in H14 with these items. Right? Australian Dollar U.S. Dollar Canadian Dollar European Euro Indonesian Rupiah British Pound What do you want to see in F20:G38 and G39:40 if you pick say U.S. Dollar from the list? How about European Euro? What do you want to see in G41 with a pick? Gord On Sun, 16 Sep 2007 15:25:38 -0700, Randy wrote: Gord, I pasted in the code and made a few changes........ decided to use full words rather than the acronyms. I had everything in Cells F20:G40 working fine but then when I plugged in the remainder of the code for G41 it all quit working! Now it seems like whatever I select for currency, the number formatting is always to the second decimal place and G41 gets no currency symbol of any kind or decimal place formatting. I likely screwed something up. Could you please have a look at what I've altered? Thanks.- Hide quoted text - - Show quoted text - Sorry about the ever changing format. I promise not to make anymore changes! H14 has the correct choices. In Cells F20:G38 and G39:G40 I only want a number that is formatted to the appropriate decimal place for the choosen currency. I do not want to display any currency symbols in these cells. In Cell G41 I would like the number to be formatted according to the currency selected but this time with a currency symbol either before or after the number as in the case of Rupiah. Thanks for your assistance and your patience. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto number formatting
On Sep 17, 9:10Â*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Did you try the code I last posted? Â*Worked for me. The code you just posted will not work and why are you attempting to use it? Gord, Â*who is off for two days. Â*Back Wednesday. On Sun, 16 Sep 2007 20:25:20 -0700, Randy wrote: On Sep 17, 9:19 am, Gord Dibben <gorddibbATshawDOTca wrote: On Sun, 16 Sep 2007 16:42:42 -0700, Randy wrote: Sorry about the ever changing format. I promise not to make anymore changes! H14 has the correct choices. In Cells F20:G38 and G39:G40 I only want a number that is formatted to the appropriate decimal place for the choosen currency. I do not want to display any currency symbols in these cells. 1,000.00 for dollars, pounds and euros. Â*1000 for Rupiah In Cell G41 I would like the number to be formatted according to the currency selected but this time with a currency symbol either before $1,000.00, £1,000.00 and ‚¬1,000.00 dor dollars, pounds and euros. or after the number as in the case of Rupiah. 1000.00Rp for Rupiah Thanks for your assistance and your patience. Private Sub Worksheet_Change(ByVal Target As Range) Â*If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub Â*On Error GoTo endit Â* Â* Â* Â*Application.EnableEvents = False Â*Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0" & _ Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* IIf(Target.Value = "Indonesian Rupiah", "", ".00") Â*Select Case Target.Value Â* Â* Case "U.S. Dollar", "Australian Dollar", "Canadian Dollar" Â* Â* Â* Â* Me.Range("G41").NumberFormat = "$#,##0.00" Â* Â* Case "Indonesian Rupiah" Â* Â* Â* Â* Me.Range("G41").NumberFormat = "#,##0.00Rp" Â* Â* Case "British Pound" Â* Â* Â* Â* Me.Range("G41").NumberFormat = "£#,##0.00" Â* Â* Case "European Euro" Â* Â* Â* Â* Me.Range("G41").NumberFormat = "‚¬#,##0.00" Â* Â* Â*End Select endit: Â* Â* Â*Application.EnableEvents = True Â*End Sub Gord I input the code but it still doesn't work. Cell G41 stays with the number formatted to zero decimal places on all selections and no symbol is ever displayed, no matter if I choose dollars or any other currency. All the other cells in question stay formatted to two decimal places, no matter what the currency. When I make a currency selection a Visual Basic message pops up saying, "Compile Error: Select Case without End Select" and the "End Sub" at the end of the code gets highlighted. I understand if you are losing patience and choose not to reply. Thanks. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub On Error GoTo endit Â* Â* Â*Application.EnableEvents = False Select Case Target.Value Â* Â*Case "U.S. Dollar" Â* Â* Â* Â*Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00" Â* Â*Case "Indonesian Rupiah" Â* Â* Â* Â*Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0" Â* Â*Case "Canadian Dollar" Â* Â* Â* Â*Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00" Â* Â*Case "British Pound" Â* Â* Â* Â*Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00" Â* Â*Case "European Euro" Â* Â* Â* Â*Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00" Â* Â*Case "Australian Dollar" Â* Â* Â* Â*Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00" If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub On Error GoTo endit Â* Â* Â* Application.EnableEvents = False Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0" & _ Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*IIf(Target.Value = "Indonesian Rupiah", "", ". 00") Select Case Target.Value Â* Â*Case "U.S. Dollar", "Australian Dollar", "Canadian Dollar" Â* Â* Â* Â*Me.Range("G41").NumberFormat = "$#,##0.00" Â* Â*Case "Indonesian Rupiah" Â* Â* Â* Â*Me.Range("G41").NumberFormat = "#,##0.00Rp" Â* Â*Case "British Pound" Â* Â* Â* Â*Me.Range("G41").NumberFormat = "£#,##0.00" Â* Â*Case "European Euro" Â* Â* Â* Â*Me.Range("G41").NumberFormat = "‚¬#,##0.00" Â* Â* End Select endit: Â* Â* Application.EnableEvents = True End Sub- Hide quoted text - - Show quoted text - The reason I posted a question to this group was to get some assistance in solving a problem. If I was able to write the code myself, I would have done so. I posted the code in the last message so that someone could see what I have for code and perhaps correct it for me. If you are unable to assist, can you please direct me to another group site............. perhaps I'm dealing with a group that is only for professionals???????? Thanks for helping me get within grasp of the final solution. |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto number formatting
On Sep 17, 9:10Â*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Did you try the code I last posted? Â*Worked for me. The code you just posted will not work and why are you attempting to use it? Gord, Â*who is off for two days. Â*Back Wednesday. On Sun, 16 Sep 2007 20:25:20 -0700, Randy wrote: On Sep 17, 9:19 am, Gord Dibben <gorddibbATshawDOTca wrote: On Sun, 16 Sep 2007 16:42:42 -0700, Randy wrote: Sorry about the ever changing format. I promise not to make anymore changes! H14 has the correct choices. In Cells F20:G38 and G39:G40 I only want a number that is formatted to the appropriate decimal place for the choosen currency. I do not want to display any currency symbols in these cells. 1,000.00 for dollars, pounds and euros. Â*1000 for Rupiah In Cell G41 I would like the number to be formatted according to the currency selected but this time with a currency symbol either before $1,000.00, £1,000.00 and ‚¬1,000.00 dor dollars, pounds and euros. or after the number as in the case of Rupiah. 1000.00Rp for Rupiah Thanks for your assistance and your patience. Private Sub Worksheet_Change(ByVal Target As Range) Â*If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub Â*On Error GoTo endit Â* Â* Â* Â*Application.EnableEvents = False Â*Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0" & _ Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* IIf(Target.Value = "Indonesian Rupiah", "", ".00") Â*Select Case Target.Value Â* Â* Case "U.S. Dollar", "Australian Dollar", "Canadian Dollar" Â* Â* Â* Â* Me.Range("G41").NumberFormat = "$#,##0.00" Â* Â* Case "Indonesian Rupiah" Â* Â* Â* Â* Me.Range("G41").NumberFormat = "#,##0.00Rp" Â* Â* Case "British Pound" Â* Â* Â* Â* Me.Range("G41").NumberFormat = "£#,##0.00" Â* Â* Case "European Euro" Â* Â* Â* Â* Me.Range("G41").NumberFormat = "‚¬#,##0.00" Â* Â* Â*End Select endit: Â* Â* Â*Application.EnableEvents = True Â*End Sub Gord I input the code but it still doesn't work. Cell G41 stays with the number formatted to zero decimal places on all selections and no symbol is ever displayed, no matter if I choose dollars or any other currency. All the other cells in question stay formatted to two decimal places, no matter what the currency. When I make a currency selection a Visual Basic message pops up saying, "Compile Error: Select Case without End Select" and the "End Sub" at the end of the code gets highlighted. I understand if you are losing patience and choose not to reply. Thanks. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub On Error GoTo endit Â* Â* Â*Application.EnableEvents = False Select Case Target.Value Â* Â*Case "U.S. Dollar" Â* Â* Â* Â*Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00" Â* Â*Case "Indonesian Rupiah" Â* Â* Â* Â*Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0" Â* Â*Case "Canadian Dollar" Â* Â* Â* Â*Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00" Â* Â*Case "British Pound" Â* Â* Â* Â*Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00" Â* Â*Case "European Euro" Â* Â* Â* Â*Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00" Â* Â*Case "Australian Dollar" Â* Â* Â* Â*Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0.00" If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub On Error GoTo endit Â* Â* Â* Application.EnableEvents = False Me.Range("F20:G38, G39:G40").NumberFormat = "#,##0" & _ Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*IIf(Target.Value = "Indonesian Rupiah", "", ". 00") Select Case Target.Value Â* Â*Case "U.S. Dollar", "Australian Dollar", "Canadian Dollar" Â* Â* Â* Â*Me.Range("G41").NumberFormat = "$#,##0.00" Â* Â*Case "Indonesian Rupiah" Â* Â* Â* Â*Me.Range("G41").NumberFormat = "#,##0.00Rp" Â* Â*Case "British Pound" Â* Â* Â* Â*Me.Range("G41").NumberFormat = "£#,##0.00" Â* Â*Case "European Euro" Â* Â* Â* Â*Me.Range("G41").NumberFormat = "‚¬#,##0.00" Â* Â* End Select endit: Â* Â* Application.EnableEvents = True End Sub- Hide quoted text - - Show quoted text - ........ and yes, I did try the code but it didn't work. The results of trying the code were described in my second last post. |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto number formatting
I did correct your code.
Delete the code you currently have and paste this code into you sheet module. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("H14")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Me.Range("F20:G38, G39:G40").NumberFormat = "[$" & Target.Value & "] #,##0" & _ IIf(Target.Value = "Indonesian Rupiah", "", ".00") Select Case Target.Value Case "U.S. Dollar", "Australian Dollar", "Canadian Dollar" Me.Range("G41").NumberFormat = "$#,##0.00" Case "Indonesian Rupiah" Me.Range("G41").NumberFormat = "#,##0 Rp" Case "British Pound" Me.Range("G41").NumberFormat = "£#,##0.00" Case "European Euro" Me.Range("G41").NumberFormat = "€#,##0.00" End Select endit: Application.EnableEvents = True End Sub Or send me your workbook by email and I'll see what I can do. Change the AT and DOT to get my address. Gord On Tue, 18 Sep 2007 01:34:18 -0000, Randy wrote: The reason I posted a question to this group was to get some assistance in solving a problem. If I was able to write the code myself, I would have done so. I posted the code in the last message so that someone could see what I have for code and perhaps correct it for me. If you are unable to assist, can you please direct me to another group site............. perhaps I'm dealing with a group that is only for professionals???????? Thanks for helping me get within grasp of the final solution. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto number formatting | Excel Worksheet Functions | |||
How do I change auto formatting to allow a 0 as the first number | Excel Discussion (Misc queries) | |||
Auto Number the Rows of Auto Filter Result | Excel Discussion (Misc queries) | |||
how to stop excel from auto formatting whole number to decimal % | New Users to Excel | |||
Auto Formatting Custom Number | Excel Discussion (Misc queries) |