| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
change event question
Hi - i have the following worksheet macro.
It does 2 things 1) Makes anything typed in cell E29 into upper case 2) If a change in e29 takes place ... it changes the corresponding cells below into the proper format. The only problem is that anytime i type anywhere on the worksheet and then press enter - its bringing me to cell E29. How do eliminate that so it only has a change event when THAT cell (E29) is changed, and NOT any cell on the sheet ? Thanks scott Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit Range("f20:f20").Select Dim Rng As Range For Each Rng In Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value) End If Next Rng With Target If .Column = 6 Then If ((.Row = 20 And .Row <= 20)) Then Select Case Target.Value Case "ES": FormatCells Target, "###0.00;[Red]###0.00" Case "NQ": FormatCells Target, "###0.00;[Red]###0.00" Case "ER2": FormatCells Target, "###0.00;[Red]###0.00" Case "YM": FormatCells Target, "###0;[Red]####" Case "ZB": FormatCells Target, "# ??/32;[Red]# ??/32" Case "EUR": FormatCells Target, "0.0000;[Red]0.0000" Case "JPY": FormatCells Target, "0.0000" Case "GE": FormatCells Target, "00.000;[Red]00.000" Case "CAD": FormatCells Target, "00.000;[Red]00.000" Case "YG": FormatCells Target, "###0.00;[Red]###0.00" Case "YI": FormatCells Target, "0.000;[Red]0.000" Case "SPY": FormatCells Target, "###.00" Case "QQQ": FormatCells Target, "###.00" End Select End If End If End With ws_exit: Application.EnableEvents = True End Sub Private Sub FormatCells(Rng As Range, format As String) Rng.Cells(1, 1).NumberFormat = format Rng.Cells(1, 2).NumberFormat = format Rng.Cells(1, -1).NumberFormat = format Rng.Cells(1, -2).NumberFormat = format Rng.Cells(0, 0).NumberFormat = format Rng.Cells(-0, 1).NumberFormat = format Rng.Cells(0, 2).NumberFormat = format Rng.Cells(0, 3).NumberFormat = format Rng.Cells(0, -1).NumberFormat = format Rng.Cells(0, -2).NumberFormat = format Rng.Cells(0, -3).NumberFormat = format End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
change event question
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
change event question
Hi Scott23,
Hi - i have the following worksheet macro. It does 2 things 1) Makes anything typed in cell E29 into upper case 2) If a change in e29 takes place ... it changes the corresponding cells below into the proper format. The only problem is that anytime i type anywhere on the worksheet and then press enter - its bringing me to cell E29. How do eliminate that so it only has a change event when THAT cell (E29) is changed, and NOT any cell on the sheet ? I think your code can be simplified to this: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit If Range("f20:f20").HasFormula = False Then Range("f20:f20").Value = UCase(Range("f20:f20").Value) End If With Target If .Column = 6 Then If ((.Row = 20 And .Row <= 29)) Then Select Case Target.Value Case "ES": FormatCells Target, "###0.00;[Red]###0.00" Case "NQ": FormatCells Target, "###0.00;[Red]###0.00" Case "ER2": FormatCells Target, "###0.00;[Red]###0.00" Case "YM": FormatCells Target, "###0;[Red]####" Case "ZB": FormatCells Target, "# ??/32;[Red]#??/32" Case "EUR": FormatCells Target, "0.0000;[Red]0.0000" Case "JPY": FormatCells Target, "0.0000" Case "GE": FormatCells Target, "00.000;[Red]00.000" Case "CAD": FormatCells Target, "00.000;[Red]00.000" Case "YG": FormatCells Target, "###0.00;[Red]###0.00" Case "YI": FormatCells Target, "0.000;[Red]0.000" Case "SPY": FormatCells Target, "###.00" Case "QQQ": FormatCells Target, "###.00" End Select End If End If End With ws_exit: Application.EnableEvents = True End Sub Private Sub FormatCells(Rng As Range, format As String) Range(Rng.Cells(1, -2), Rng.Cells(1, 2)).NumberFormat = format Range(Rng.Cells(0, -3), Rng.Cells(0, 3)).NumberFormat = format End Sub Regards, Jan Karel Pieterse Excel MVP www.jkp-ads.com |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
change event question
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False On Error GoTo ws_exit If Not Intersect(Target, Range("E6")) Is Nothing Then If Target.HasFormula = False Then Target.Value = UCase(Target.Value) End If With Target If .Column = 6 Then If ((.Row = 20 And .Row <= 20)) Then Select Case Target.Value Case "ES": FormatCells Target, 1 Case "NQ": FormatCells Target, 1 Case "ER2": FormatCells Target, 1 Case "YM": FormatCells Target, 2 Case "ZB": FormatCells Target, 3 Case "EUR": FormatCells Target, 4 Case "JPY": FormatCells Target, 5 Case "GE": FormatCells Target, 6 Case "CAD": FormatCells Target, 6 Case "YG": FormatCells Target, 1 Case "YI": FormatCells Target, 7 Case "SPY": FormatCells Target, 8 Case "QQQ": FormatCells Target, 8 End Select End If End If End With End If ws_exit: Application.EnableEvents = True End Sub Private Sub FormatCells(Rng As Range, _ Optional formatIdx As Long) Dim format As String Select Case formatIdx Case 1: format = "###0.00;[Red]###0.00" Case 2: format = "###0;[Red]####" Case 3: format = "# ??/32;[Red]# ??/32" Case 4: format = "0.0000;[Red]0.0000" Case 5: format = "0.0000" Case 6: format = "00.000;[Red]00.000" Case 7: format = "0.000;[Red]0.000" Case 8: format = "###.00" End Select With Rng .Cells(1, 1).NumberFormat = format .Cells(1, 2).NumberFormat = format .Cells(1, -1).NumberFormat = format .Cells(1, -2).NumberFormat = format .Cells(0, 0).NumberFormat = format .Cells(-0, 1).NumberFormat = format .Cells(0, 2).NumberFormat = format .Cells(0, 3).NumberFormat = format .Cells(0, -1).NumberFormat = format .Cells(0, -2).NumberFormat = format .Cells(0, -3).NumberFormat = format End With 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 have the following worksheet macro. It does 2 things 1) Makes anything typed in cell E29 into upper case 2) If a change in e29 takes place ... it changes the corresponding cells below into the proper format. The only problem is that anytime i type anywhere on the worksheet and then press enter - its bringing me to cell E29. How do eliminate that so it only has a change event when THAT cell (E29) is changed, and NOT any cell on the sheet ? Thanks scott Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False On Error GoTo ws_exit Range("f20:f20").Select Dim Rng As Range For Each Rng In Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value) End If Next Rng With Target If .Column = 6 Then If ((.Row = 20 And .Row <= 20)) Then Select Case Target.Value Case "ES": FormatCells Target, "###0.00;[Red]###0.00" Case "NQ": FormatCells Target, "###0.00;[Red]###0.00" Case "ER2": FormatCells Target, "###0.00;[Red]###0.00" Case "YM": FormatCells Target, "###0;[Red]####" Case "ZB": FormatCells Target, "# ??/32;[Red]# ??/32" Case "EUR": FormatCells Target, "0.0000;[Red]0.0000" Case "JPY": FormatCells Target, "0.0000" Case "GE": FormatCells Target, "00.000;[Red]00.000" Case "CAD": FormatCells Target, "00.000;[Red]00.000" Case "YG": FormatCells Target, "###0.00;[Red]###0.00" Case "YI": FormatCells Target, "0.000;[Red]0.000" Case "SPY": FormatCells Target, "###.00" Case "QQQ": FormatCells Target, "###.00" End Select End If End If End With ws_exit: Application.EnableEvents = True End Sub Private Sub FormatCells(Rng As Range, format As String) Rng.Cells(1, 1).NumberFormat = format Rng.Cells(1, 2).NumberFormat = format Rng.Cells(1, -1).NumberFormat = format Rng.Cells(1, -2).NumberFormat = format Rng.Cells(0, 0).NumberFormat = format Rng.Cells(-0, 1).NumberFormat = format Rng.Cells(0, 2).NumberFormat = format Rng.Cells(0, 3).NumberFormat = format Rng.Cells(0, -1).NumberFormat = format Rng.Cells(0, -2).NumberFormat = format Rng.Cells(0, -3).NumberFormat = format End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Quick VBA Worksheet Change Event or Selection Question: | Excel Worksheet Functions | |||
| Change minimumscale with the change event of a combobox | Excel Programming | |||
| Question about an event | Excel Programming | |||
| change event/after update event?? | Excel Programming | |||
| Event Question | Excel Programming | |||
Linear Mode
