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?

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?