Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help worksheet event code
Hi,
I received help writing this worksheet event code, but i was hoping to change 1 small thing. 1. Id like to format only the cells in columns E,F,H,I as opposed to the whole row. Here is the code i currently have that formats the whole row rather than what i need. ================================================== ============================= Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit If Target.Column = 2 Then Select Case Target.Value Case "ES": Target.EntireRow.NumberFormat = "(###0.00)" Case "NQ": Target.EntireRow.NumberFormat = "(###0.00)" Case "AB": Target.EntireRow.NumberFormat = "(###0.00)" Case "YM": Target.EntireRow.NumberFormat = "(###0.00)" Case "ZB": Target.EntireRow.NumberFormat = "(# ??/32)" Case "EC": Target.EntireRow.NumberFormat = " (#.0000)" Case "JY": Target.EntireRow.NumberFormat = " (##0.00)" Case "ED": Target.EntireRow.NumberFormat = " (#0.000)" End Select End If ws_exit: Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help worksheet event code
Scott,
It's best to keep to the original thread, helps us know where we are. Try this Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit If Target.Column = 2 Then Select Case Target.Value Case "ES": FormatCells Target, "(###0.00)" Case "NQ": FormatCells Target, "(###0.00)" Case "AB": FormatCells Target, "(###0.00)" Case "YM": FormatCells Target, "(###0.00)" Case "ZB": FormatCells Target, "(# ??/32)" Case "EC": FormatCells Target, " (#.0000)" Case "JY": FormatCells Target, " (##0.00)" Case "ED": FormatCells Target, " (#0.000)" End Select End If ws_exit: Application.EnableEvents = True End Sub Private Sub FormatCells(rng As Range, format As String) rng.Cells(1, 4).NumberFormat = format rng.Cells(1, 5).NumberFormat = format rng.Cells(1, 7).NumberFormat = format rng.Cells(1, 8).NumberFormat = format End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "scott23" wrote in message om... Hi, I received help writing this worksheet event code, but i was hoping to change 1 small thing. 1. Id like to format only the cells in columns E,F,H,I as opposed to the whole row. Here is the code i currently have that formats the whole row rather than what i need. ================================================== ========================== === Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit If Target.Column = 2 Then Select Case Target.Value Case "ES": Target.EntireRow.NumberFormat = "(###0.00)" Case "NQ": Target.EntireRow.NumberFormat = "(###0.00)" Case "AB": Target.EntireRow.NumberFormat = "(###0.00)" Case "YM": Target.EntireRow.NumberFormat = "(###0.00)" Case "ZB": Target.EntireRow.NumberFormat = "(# ??/32)" Case "EC": Target.EntireRow.NumberFormat = " (#.0000)" Case "JY": Target.EntireRow.NumberFormat = " (##0.00)" Case "ED": Target.EntireRow.NumberFormat = " (#0.000)" End Select End If ws_exit: Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command button to toggle worksheet event code on / off? | Excel Discussion (Misc queries) | |||
Starting/Stopping Worksheet Event Code | Excel Discussion (Misc queries) | |||
Where?Worksheet code module or Worksheet_SelectionChange event han | Excel Worksheet Functions | |||
Worksheet Event Code | Excel Worksheet Functions | |||
Enable/Disable Worksheet Change Event code | Excel Programming |