Ken Johnson or someone! -- I need some help again!

[Guest]

Hi! and thanks for everything!
do you remember last topic "Help me! Using Excel for Soccer Games" ??
well, i have a problem on it... im using the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 46 Or Target.Row > 29 Then
Exit Sub
End If
If Target.Column < 5 Or Target.Row < 3 Then
Exit Sub
End If
Me.Unprotect
Target.Locked = True
Me.Protect
End Sub

with this i have one interval of cells being affected by the macro
perfectly, which is E4:AT29
The problem is that in the same sheet i also need to use another interval,
which is E66:AT91
....and i dont know anything about visual basic... i just copy and paste! how
should i rewrite it?
thanks!

 

[Bryan Hessey]

Perhaps something like


Code:
--------------------
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iCol As Integer, iRow As Integer, iLock As Integer

iLock = 0
iCol = Target.Column
iRow = Target.Row

If iCol >= 5 And iCol <= 46 And iRow >= 3 And iRow <= 29 Then
iLock = 1
Else
If iCol >= 5 And iCol <= 46 And iRow >= 66 And iRow <= 91 Then
iLock = 1
End If
End If

If iLock = 1 Then
Me.Unprotect
Target.Locked = True
Me.Protect
End If

End Sub

 

[Guest]

Hi! and thanks for everything!
do you remember last topic "Help me! Using Excel for Soccer Games" ??
well, i have a problem on it... im using the following:

The problem is that in the same sheet i also need to use another interval,
which is E66:AT91

If you have multiple ranges it's best to determine the valid fields instead
of the invalid ones:

Private Sub Worksheet_Change(ByVal Target As Range)
cm = Target.Column
rw = Target.Row
If (cm >= 5 And cm <= 46) And ((rw >= 4 And rw <= 29) Or (rw >= 66 And rw
<= 91)) Then
Me.Unprotect
Target.Locked = True
Me.Protect
End If
End Sub


In your case the columns are the same so you can test the column-range in
one time.

 

[Ken Johnson]

Hi Fecozisk,

This works too...

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("E4:AT29,E66:AT91"), Target) Is Nothing Then
Exit Sub
End If
If Target.Column < 5 Or Target.Row < 3 Then
Exit Sub
End If
Me.Unprotect
Target.Locked = True
Me.Protect
End Sub


Ken Johnson

 

[Ken Johnson]

Hi Fecozisk,

Sorry, I should've tidied it up a bit more..

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("E4:AT29,E66:AT91"), Target) Is Nothing Then
Exit Sub
End If
Me.Unprotect
Target.Locked = True
Me.Protect
End Sub

Ken Johnson

 

[Guest]

Thanks all you guys! 100% solved!

"Ken Johnson" escreveu:

 

[Ken Johnson]

Hi Fecozisk,

You're always welcome.

Thanks for the feedback.

Ken Johnson