Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set number format based on cell contents
I have a cell B3 which shows a Data Validation list of £ or %
Having selected £ or % in B3 the user then enters a number in C3. I want to have cell C3 formatted to General if B3 = % and formatted to Currency if B3 = £. I'd really appreciate some help Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set number format based on cell contents
You need VBA for that. Paste the following macro into the sheet module for
the sheet you are working on. To do that, right-click on the sheet tab, select View Code, and paste this macro into that module. Note that this macro does exactly what you asked. That is, it will change the format of C3 if you select $ or % in B3. If you want this to work on other cells, you will need to alter the macro. Note that the macro uses $ instead of the British pound symbol. You need to change that. Post back if you need more. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(0, 0) = "B3" Then If Target = "$" Then Target.Offset(, 1).NumberFormat = "$#,##0.00" Else Target.Offset(, 1).NumberFormat = "General" End If Application.EnableEvents = False Target.ClearContents Application.EnableEvents = True End If End Sub "nospaminlich" wrote in message ... I have a cell B3 which shows a Data Validation list of £ or % Having selected £ or % in B3 the user then enters a number in C3. I want to have cell C3 formatted to General if B3 = % and formatted to Currency if B3 = £. I'd really appreciate some help Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set number format based on cell contents
Thanks Otto.
I've edited the code as shown below and put it in the Sheet1 Object page but when I change the cells in B3 nothing happens in C3. Can't see what I've done wrong but I must have missed something. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(0, 0) = "B3" Then If Target = "£" Then Target.Offset(, 1).NumberFormat = "£#,##0.00" Else Target.Offset(, 1).NumberFormat = "General" End If Application.EnableEvents = False Target.ClearContents Application.EnableEvents = True End If End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set number format based on cell contents
All that is going to happen is that the format of C3 will change. If you
mean that the format is not changing then there is a problem. It worked for me. So let's troubleshoot it. Put the following line as the first line of the macro: MsgBox "Working" If you see the "Working" message box when you change the entry in B3, then we know the macro is firing. If you don't see that, then Excel is not recognizing the event of a change in B3. Close the file and close Excel. Open Excel and open the file and see what happens when you change B3. If this doesn't help, then I have to see the file myself. If you wish, send me the file direct (do not post the file in the newsgroup). If your file is large, make a copy of it and cut the file down to small and make sure that the response of the macro (nothing) is still the same. My email address is . Remove the "nop" from this address. HTH Otto "nospaminlich" wrote in message ... Thanks Otto. I've edited the code as shown below and put it in the Sheet1 Object page but when I change the cells in B3 nothing happens in C3. Can't see what I've done wrong but I must have missed something. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(0, 0) = "B3" Then If Target = "£" Then Target.Offset(, 1).NumberFormat = "£#,##0.00" Else Target.Offset(, 1).NumberFormat = "General" End If Application.EnableEvents = False Target.ClearContents Application.EnableEvents = True End If End Sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set number format based on cell contents
Thanks a lot Otto.
Tried everything you suggested but it still didn't work so I did the Ctrl-Alt-Del routine and having restarted Windows and Excel the code works perfectly. Don't understand why it needed a system restart but I'm just delighted it works. Thanks again |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set number format based on cell contents
I would guess that you somehow skipped over the line:
Application.EnableEvents = True when you were running the macro. This can happen when you incur an error and the code execution stops. That sets Excel to not recognize any events and it stays that way until you manually reset Excel, which you did. If this happens again then we need to put an error trap in the code somewhere. Post back if this happens again. HTH Otto "nospaminlich" wrote in message ... Thanks a lot Otto. Tried everything you suggested but it still didn't work so I did the Ctrl-Alt-Del routine and having restarted Windows and Excel the code works perfectly. Don't understand why it needed a system restart but I'm just delighted it works. Thanks again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
" / " Changing Decimal number format to Fraction on Protected Cell | Excel Worksheet Functions | |||
Formatting a cell as "text" in the number catagory. | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
Modify Row & Cell Contents based upon Cells Values | Excel Worksheet Functions | |||
returning a text cell based on a number cell | Excel Worksheet Functions |