change event question

S

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 ?

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
 
J

Jan Karel Pieterse

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 ?
Click to expand...

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
 
B

Bob Phillips

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 said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to run macros from combobox selection 5
Getting compile error on WorksheetFuntion.Sum line 5
Junp start my Select Case Macro 12
Get rid of need for F2.Select 3
Format change with VBA language dependent? 5
"ElseIf c.Value = "X_Clear" Then" and "...Target.Cells.Count > 1"don't work 4
Change_Event not responding to change made by formula. 11
Change_Event troubles, no error & no work. 6

Top