Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change function
I am attempting to Conditionally format a range of cells (named "OUMcol") to
either 0 or 1 decimal place, depending on the value in a cell named "Programme". Following earlier advice, I have implemented a Worksheet_Change() function, as below. Cell "Programme" uses Data validation to implement a drop down list. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = Range("Programme").Address Then Application.EnableEvents = False If Range("Programme").Value = "Edexcel" Then Range("OUMcol").NumberFormat = "0" 'MsgBox "Edexcel" Else Range("OUMcol").NumberFormat = "0.0" 'MsgBox "Not" End If Application.EnableEvents = True End If End Sub In a simple test spreadsheet all works fine. Selecting "Edexcel" from the list formats the range to 0dp, any other to 1 dp. However, in my 'real', rather complex spreadsheet, it only works if I type the value into the cell. Selecting from the list does not work. I thought at first that selecting the value from the list failed to trigger the Change event, but not so. If I include the MsgBox statements above, the appropriate message is printed, but the cells are not formatted. Summary: if I type the value into the cell "Programme", the message is printed and the cells are reformatted. If I select the value from a list, the message is printed but the cells are NOT reformatted. in a trivial version of the spreadsheet, it all works fine, in either case. Can anyone suggest what is going on, please? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change function
Andrew
Try putting a break point on the first line of the macro and use F8 to go through the macro line-by-line when it's called. What version of Excel are you using? Feel free to email the workbook to me if you want me to look at it. Unmunge the reply to email address. -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Andrew" wrote in message ... I am attempting to Conditionally format a range of cells (named "OUMcol") to either 0 or 1 decimal place, depending on the value in a cell named "Programme". Following earlier advice, I have implemented a Worksheet_Change() function, as below. Cell "Programme" uses Data validation to implement a drop down list. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = Range("Programme").Address Then Application.EnableEvents = False If Range("Programme").Value = "Edexcel" Then Range("OUMcol").NumberFormat = "0" 'MsgBox "Edexcel" Else Range("OUMcol").NumberFormat = "0.0" 'MsgBox "Not" End If Application.EnableEvents = True End If End Sub In a simple test spreadsheet all works fine. Selecting "Edexcel" from the list formats the range to 0dp, any other to 1 dp. However, in my 'real', rather complex spreadsheet, it only works if I type the value into the cell. Selecting from the list does not work. I thought at first that selecting the value from the list failed to trigger the Change event, but not so. If I include the MsgBox statements above, the appropriate message is printed, but the cells are not formatted. Summary: if I type the value into the cell "Programme", the message is printed and the cells are reformatted. If I select the value from a list, the message is printed but the cells are NOT reformatted. in a trivial version of the spreadsheet, it all works fine, in either case. Can anyone suggest what is going on, please? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting around Worksheet_Change() | Excel Worksheet Functions | |||
Problem with function "Worksheet_Change" | Excel Worksheet Functions | |||
Can an add-in macro update a worksheets Worksheet_Change function? | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |