ExcelBanter - change event question

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - change event question (https://www.excelbanter.com/excel-programming/298167-change-event-question.html)

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

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

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