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

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!

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

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
--------------------

will help

--

Fecozisk Wrote:
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
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=537547

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

"Fecozisk" wrote:

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.

--
greetings from Belgium
Stefan.

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

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 or someone! -- I need some help again!
 

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

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

Thanks all you guys! 100% solved!

"Ken Johnson" escreveu:

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

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

Hi Fecozisk,

You're always welcome.

Thanks for the feedback.

Ken Johnson