Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > How to make sub handle more than 1 cell ?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default How to make sub handle more than 1 cell ?
Hi, I have this code for Worksheet_Change event which will lock & gray out
the cell in column Q if a value is entered into column P & vice-versa. How do
I modify the code below to be able to handle more than 1 cell at a time ?

Example: If P23 & P24 currently are empty & I paste in numbers into these
two cells, then Q23 & Q24 would gray out & become locked. Inversely, if I
select P23 & P24 which is filled and press the delete key to clear its
contents, then Q23 & Q24 would un-gray itself (become yellow in this case) &
become unlocked.

Secondly, how do I change the code to detect if the selection overlaps and
just execute for the relevant column ? Example: If I select Q23:R24 (where
Q23 & Q24 are filled) and I press the delete key thereby clearing contents
for that selection, then it will trigger P23 & P24 cells to unlock & un-gray
itself. I keep getting vba error when I test out this kind of action.

Any help appreciated. TIA.

Here's the code :

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Not Intersect(Range("P:P"), Target) Is Nothing Then

ActiveSheet.Unprotect "123456"

If ((IsEmpty(Target.Value) = False) And (IsNull(Target.Value) = False))
Then


Target.Offset(0, 1).Value = ""
Target.Offset(0, 1).Interior.ColorIndex = 16

Target.Offset(0, 1).Locked = True


If (Target.Offset(0, -10).Value = "A") Then
MsgBox "Please enter comments."
End If

If (Target.Offset(0, -10).Value = "B") And (Target.Offset(0, -9).Value
60) Then
MsgBox "Please enter comments."
End If

Else

Target.Offset(0, 1).Locked = False
Target.Offset(0, 1).Interior.ColorIndex = 36


End If

ActiveSheet.Protect Password:="123456"

End If

If Not Intersect(Range("Q:Q"), Target) Is Nothing Then

ActiveSheet.Unprotect "123456"

If ((IsEmpty(Target.Value) = False) And (IsNull(Target.Value) = False))
Then

Target.Offset(0, -1).Value = ""
Target.Offset(0, -1).Interior.ColorIndex = 16

Target.Offset(0, -1).Locked = True

If (Target.Offset(0, -11).Value = "A") Then
MsgBox "Please enter comments."
End If

If (Target.Offset(0, -11).Value = "B") And (Target.Offset(0, -10).Value
60) Then
MsgBox "Please enter comments."
End If

Else
Target.Offset(0, -1).Locked = False
Target.Offset(0, -1).Interior.ColorIndex = 36

End If

ActiveSheet.Protect Password:="123456"

End If

Application.EnableEvents = True

End Sub

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default How to make sub handle more than 1 cell ?
Don't believe you can do that with Worksheet_Change. That seems to be for a
single cell target. You would have to write code in either a Case or If
....Then statement to handle more than one cell at a time and it would have to
be evaluated after it is entered, rather than as it is entered.

"Mikaela" wrote:

Hi, I have this code for Worksheet_Change event which will lock & gray out
the cell in column Q if a value is entered into column P & vice-versa. How do
I modify the code below to be able to handle more than 1 cell at a time ?

Example: If P23 & P24 currently are empty & I paste in numbers into these
two cells, then Q23 & Q24 would gray out & become locked. Inversely, if I
select P23 & P24 which is filled and press the delete key to clear its
contents, then Q23 & Q24 would un-gray itself (become yellow in this case) &
become unlocked.

Secondly, how do I change the code to detect if the selection overlaps and
just execute for the relevant column ? Example: If I select Q23:R24 (where
Q23 & Q24 are filled) and I press the delete key thereby clearing contents
for that selection, then it will trigger P23 & P24 cells to unlock & un-gray
itself. I keep getting vba error when I test out this kind of action.

Any help appreciated. TIA.

Here's the code :

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Not Intersect(Range("P:P"), Target) Is Nothing Then

ActiveSheet.Unprotect "123456"

If ((IsEmpty(Target.Value) = False) And (IsNull(Target.Value) = False))
Then


Target.Offset(0, 1).Value = ""
Target.Offset(0, 1).Interior.ColorIndex = 16

Target.Offset(0, 1).Locked = True


If (Target.Offset(0, -10).Value = "A") Then
MsgBox "Please enter comments."
End If

If (Target.Offset(0, -10).Value = "B") And (Target.Offset(0, -9).Value
60) Then
MsgBox "Please enter comments."
End If

Else

Target.Offset(0, 1).Locked = False
Target.Offset(0, 1).Interior.ColorIndex = 36


End If

ActiveSheet.Protect Password:="123456"

End If

If Not Intersect(Range("Q:Q"), Target) Is Nothing Then

ActiveSheet.Unprotect "123456"

If ((IsEmpty(Target.Value) = False) And (IsNull(Target.Value) = False))
Then

Target.Offset(0, -1).Value = ""
Target.Offset(0, -1).Interior.ColorIndex = 16

Target.Offset(0, -1).Locked = True

If (Target.Offset(0, -11).Value = "A") Then
MsgBox "Please enter comments."
End If

If (Target.Offset(0, -11).Value = "B") And (Target.Offset(0, -10).Value
60) Then
MsgBox "Please enter comments."
End If

Else
Target.Offset(0, -1).Locked = False
Target.Offset(0, -1).Interior.ColorIndex = 36

End If

ActiveSheet.Protect Password:="123456"

End If

Application.EnableEvents = True

End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default How to make sub handle more than 1 cell ?
Here is a site you might want to read to get a better understanding of the
Worksheet_Change event.

http://www.cpearson.com/excel/Events.aspx

"Mikaela" wrote:

Hi, I have this code for Worksheet_Change event which will lock & gray out
the cell in column Q if a value is entered into column P & vice-versa. How do
I modify the code below to be able to handle more than 1 cell at a time ?

Example: If P23 & P24 currently are empty & I paste in numbers into these
two cells, then Q23 & Q24 would gray out & become locked. Inversely, if I
select P23 & P24 which is filled and press the delete key to clear its
contents, then Q23 & Q24 would un-gray itself (become yellow in this case) &
become unlocked.

Secondly, how do I change the code to detect if the selection overlaps and
just execute for the relevant column ? Example: If I select Q23:R24 (where
Q23 & Q24 are filled) and I press the delete key thereby clearing contents
for that selection, then it will trigger P23 & P24 cells to unlock & un-gray
itself. I keep getting vba error when I test out this kind of action.

Any help appreciated. TIA.

Here's the code :

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Not Intersect(Range("P:P"), Target) Is Nothing Then

ActiveSheet.Unprotect "123456"

If ((IsEmpty(Target.Value) = False) And (IsNull(Target.Value) = False))
Then


Target.Offset(0, 1).Value = ""
Target.Offset(0, 1).Interior.ColorIndex = 16

Target.Offset(0, 1).Locked = True


If (Target.Offset(0, -10).Value = "A") Then
MsgBox "Please enter comments."
End If

If (Target.Offset(0, -10).Value = "B") And (Target.Offset(0, -9).Value
60) Then
MsgBox "Please enter comments."
End If

Else

Target.Offset(0, 1).Locked = False
Target.Offset(0, 1).Interior.ColorIndex = 36


End If

ActiveSheet.Protect Password:="123456"

End If

If Not Intersect(Range("Q:Q"), Target) Is Nothing Then

ActiveSheet.Unprotect "123456"

If ((IsEmpty(Target.Value) = False) And (IsNull(Target.Value) = False))
Then

Target.Offset(0, -1).Value = ""
Target.Offset(0, -1).Interior.ColorIndex = 16

Target.Offset(0, -1).Locked = True

If (Target.Offset(0, -11).Value = "A") Then
MsgBox "Please enter comments."
End If

If (Target.Offset(0, -11).Value = "B") And (Target.Offset(0, -10).Value
60) Then
MsgBox "Please enter comments."
End If

Else
Target.Offset(0, -1).Locked = False
Target.Offset(0, -1).Interior.ColorIndex = 36

End If

ActiveSheet.Protect Password:="123456"

End If

Application.EnableEvents = True

End Sub

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default How to make sub handle more than 1 cell ?
Hi, thanks for your explanation & the link. If it's not possible to do what I
want with Worksheet_Change, could you help to show me how to handle the
exceptions..... i.e. so the code will only run when only 1 cell is selected ?

TIA

"JLGWhiz" wrote:

Don't believe you can do that with Worksheet_Change. That seems to be for a
single cell target. You would have to write code in either a Case or If
...Then statement to handle more than one cell at a time and it would have to
be evaluated after it is entered, rather than as it is entered.

"Mikaela" wrote:

Hi, I have this code for Worksheet_Change event which will lock & gray out
the cell in column Q if a value is entered into column P & vice-versa. How do
I modify the code below to be able to handle more than 1 cell at a time ?

Example: If P23 & P24 currently are empty & I paste in numbers into these
two cells, then Q23 & Q24 would gray out & become locked. Inversely, if I
select P23 & P24 which is filled and press the delete key to clear its
contents, then Q23 & Q24 would un-gray itself (become yellow in this case) &
become unlocked.

Secondly, how do I change the code to detect if the selection overlaps and
just execute for the relevant column ? Example: If I select Q23:R24 (where
Q23 & Q24 are filled) and I press the delete key thereby clearing contents
for that selection, then it will trigger P23 & P24 cells to unlock & un-gray
itself. I keep getting vba error when I test out this kind of action.

Any help appreciated. TIA.

Here's the code :

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Not Intersect(Range("P:P"), Target) Is Nothing Then

ActiveSheet.Unprotect "123456"

If ((IsEmpty(Target.Value) = False) And (IsNull(Target.Value) = False))
Then


Target.Offset(0, 1).Value = ""
Target.Offset(0, 1).Interior.ColorIndex = 16

Target.Offset(0, 1).Locked = True


If (Target.Offset(0, -10).Value = "A") Then
MsgBox "Please enter comments."
End If

If (Target.Offset(0, -10).Value = "B") And (Target.Offset(0, -9).Value
60) Then
MsgBox "Please enter comments."
End If

Else

Target.Offset(0, 1).Locked = False
Target.Offset(0, 1).Interior.ColorIndex = 36


End If

ActiveSheet.Protect Password:="123456"

End If

If Not Intersect(Range("Q:Q"), Target) Is Nothing Then

ActiveSheet.Unprotect "123456"

If ((IsEmpty(Target.Value) = False) And (IsNull(Target.Value) = False))
Then

Target.Offset(0, -1).Value = ""
Target.Offset(0, -1).Interior.ColorIndex = 16

Target.Offset(0, -1).Locked = True

If (Target.Offset(0, -11).Value = "A") Then
MsgBox "Please enter comments."
End If

If (Target.Offset(0, -11).Value = "B") And (Target.Offset(0, -10).Value
60) Then
MsgBox "Please enter comments."
End If

Else
Target.Offset(0, -1).Locked = False
Target.Offset(0, -1).Interior.ColorIndex = 36

End If

ActiveSheet.Protect Password:="123456"

End If

Application.EnableEvents = True

End Sub

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default How to make sub handle more than 1 cell ?
Hi, thanks for your explanation & the link. If it's not possible to do what I
want with Worksheet_Change, could you help to show me how to handle the
exceptions..... i.e. so the code will only run when only 1 cell is selected ?

TIA

"JLGWhiz" wrote:

Don't believe you can do that with Worksheet_Change. That seems to be for a
single cell target. You would have to write code in either a Case or If
...Then statement to handle more than one cell at a time and it would have to
be evaluated after it is entered, rather than as it is entered.

"Mikaela" wrote:

Hi, I have this code for Worksheet_Change event which will lock & gray out
the cell in column Q if a value is entered into column P & vice-versa. How do
I modify the code below to be able to handle more than 1 cell at a time ?

Example: If P23 & P24 currently are empty & I paste in numbers into these
two cells, then Q23 & Q24 would gray out & become locked. Inversely, if I
select P23 & P24 which is filled and press the delete key to clear its
contents, then Q23 & Q24 would un-gray itself (become yellow in this case) &
become unlocked.

Secondly, how do I change the code to detect if the selection overlaps and
just execute for the relevant column ? Example: If I select Q23:R24 (where
Q23 & Q24 are filled) and I press the delete key thereby clearing contents
for that selection, then it will trigger P23 & P24 cells to unlock & un-gray
itself. I keep getting vba error when I test out this kind of action.

Any help appreciated. TIA.

Here's the code :

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Not Intersect(Range("P:P"), Target) Is Nothing Then

ActiveSheet.Unprotect "123456"

If ((IsEmpty(Target.Value) = False) And (IsNull(Target.Value) = False))
Then


Target.Offset(0, 1).Value = ""
Target.Offset(0, 1).Interior.ColorIndex = 16

Target.Offset(0, 1).Locked = True


If (Target.Offset(0, -10).Value = "A") Then
MsgBox "Please enter comments."
End If

If (Target.Offset(0, -10).Value = "B") And (Target.Offset(0, -9).Value
60) Then
MsgBox "Please enter comments."
End If

Else

Target.Offset(0, 1).Locked = False
Target.Offset(0, 1).Interior.ColorIndex = 36


End If

ActiveSheet.Protect Password:="123456"

End If

If Not Intersect(Range("Q:Q"), Target) Is Nothing Then

ActiveSheet.Unprotect "123456"

If ((IsEmpty(Target.Value) = False) And (IsNull(Target.Value) = False))
Then

Target.Offset(0, -1).Value = ""
Target.Offset(0, -1).Interior.ColorIndex = 16

Target.Offset(0, -1).Locked = True

If (Target.Offset(0, -11).Value = "A") Then
MsgBox "Please enter comments."
End If

If (Target.Offset(0, -11).Value = "B") And (Target.Offset(0, -10).Value
60) Then
MsgBox "Please enter comments."
End If

Else
Target.Offset(0, -1).Locked = False
Target.Offset(0, -1).Interior.ColorIndex = 36

End If

ActiveSheet.Protect Password:="123456"

End If

Application.EnableEvents = True

End Sub

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
fill handle and cell drag-and-drop jfh14 Excel Discussion (Misc queries) 1 October 8th 08 08:24 PM
How do I make the auto fill handle to input data from left to righ Jason Excel Discussion (Misc queries) 2 March 12th 08 10:27 PM
Fill handle turned into a move handle Northwoods Excel Discussion (Misc queries) 1 March 2nd 07 03:40 PM
Handle to a cell (Row and Column) prakash Excel Discussion (Misc queries) 1 September 12th 06 11:35 AM
How to make error handle for mis-typed variable? RB Smissaert Excel Programming 22 May 1st 05 10:33 PM


All times are GMT +1. The time now is 08:45 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement