Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Do it for "n" cells
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default Do it for "n" cells
Hi all

My code reads one cell and through the options it locks or not others cells.
Im trying to extend my programming to all subsequent cells. How can I do it?
Like using "for" instruction?!?! Does anybody help me?

Private Sub Worksheet_Change(ByVal Target As Range)
If [E9]= "A" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = True
ActiveSheet.Range("F9").Locked = False
[G9:K9].Interior.ColorIndex = 15
[F9].Interior.ColorIndex = 0
ActiveSheet.Protect ("")
ElseIf Target.Cells.Text = "B" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = False
ActiveSheet.Range("F9").Locked = True
[G9:K9].Interior.ColorIndex = 0
[F9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
Else
ActiveSheet.Unprotect ("")
ActiveSheet.Range("F9:K9").Locked = True
[F9:K9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
End If

End Sub


Thanks in advance


Kelson




Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Do it for "n" cells
Worksheet change functions should look something like this. This is only an
example. code may not do exactly what you need it to do.


Private Sub Worksheet_Change(ByVal Target As Range)

for each cell in Target
If cell.column = 1 Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = True
ActiveSheet.Range("F9").Locked = False
[G9:K9].Interior.ColorIndex = 15
[F9].Interior.ColorIndex = 0
ActiveSheet.Protect ("")
Else
If cell.column = 2 Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = False
ActiveSheet.Range("F9").Locked = True
[G9:K9].Interior.ColorIndex = 0
[F9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
Else
ActiveSheet.Unprotect ("")
ActiveSheet.Range("F9:K9").Locked = True
[F9:K9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
end if
End If
next cell
End Sub




"Kelson" wrote:

Hi all

My code reads one cell and through the options it locks or not others cells.
Im trying to extend my programming to all subsequent cells. How can I do it?
Like using "for" instruction?!?! Does anybody help me?

Private Sub Worksheet_Change(ByVal Target As Range)
If [E9]= "A" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = True
ActiveSheet.Range("F9").Locked = False
[G9:K9].Interior.ColorIndex = 15
[F9].Interior.ColorIndex = 0
ActiveSheet.Protect ("")
ElseIf Target.Cells.Text = "B" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = False
ActiveSheet.Range("F9").Locked = True
[G9:K9].Interior.ColorIndex = 0
[F9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
Else
ActiveSheet.Unprotect ("")
ActiveSheet.Range("F9:K9").Locked = True
[F9:K9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
End If

End Sub


Thanks in advance


Kelson




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default Do it for "n" cells
Well

Is not exactly like that. I mean, i would like to lock/unlock cells based on
value selected in another cell.
Selected value in Ex
Range Gx:Kx

E9,E10,E11,E12 and all subsequent cells

Any help?

Cheers




"Joel" escreveu:

Worksheet change functions should look something like this. This is only an
example. code may not do exactly what you need it to do.


Private Sub Worksheet_Change(ByVal Target As Range)

for each cell in Target
If cell.column = 1 Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = True
ActiveSheet.Range("F9").Locked = False
[G9:K9].Interior.ColorIndex = 15
[F9].Interior.ColorIndex = 0
ActiveSheet.Protect ("")
Else
If cell.column = 2 Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = False
ActiveSheet.Range("F9").Locked = True
[G9:K9].Interior.ColorIndex = 0
[F9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
Else
ActiveSheet.Unprotect ("")
ActiveSheet.Range("F9:K9").Locked = True
[F9:K9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
end if
End If
next cell
End Sub


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Do it for "n" cells
Let me explain how worksheet change works. the target parameter can be one
or many cells. If you change one cell then target is only one cell. If you
do a copy and paste of more than one cell on the worksheet then Target is
many cells. the statement "for each cell in Target" allows you do check all
cells when a copy and paste is performed.

From cell (without the s at the end) you can get the row number or column
number (cell.row and cell.column). if you want to check another cell in the
same row as cell you would use

if cells(.cell.row,"D") = 'this value"
Notice there is a cell with and without the 's'.

I don't completly understnad what you want but I made some changes to the
code as an example

Private Sub Worksheet_Change(ByVal Target As Range)

for each cell in Target
If cell.column = 1 Then
ActiveSheet.Unprotect ("")
if cells(cell.row,"G").locked = true then <= NEW
ActiveSheet.Range("G9:K9").Locked = True
ActiveSheet.Range("F9").Locked = False
[G9:K9].Interior.ColorIndex = 15
[F9].Interior.ColorIndex = 0
ActiveSheet.Protect ("")
end if
Else
If cell.column = 2 Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = False
ActiveSheet.Range("F9").Locked = True
[G9:K9].Interior.ColorIndex = 0
[F9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
Else
ActiveSheet.Unprotect ("")
ActiveSheet.Range("F9:K9").Locked = True
[F9:K9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
end if
End If
next cell
End Sub



"Kelson" wrote:

Well

Is not exactly like that. I mean, i would like to lock/unlock cells based on
value selected in another cell.
Selected value in Ex
Range Gx:Kx

E9,E10,E11,E12 and all subsequent cells

Any help?

Cheers




"Joel" escreveu:

Worksheet change functions should look something like this. This is only an
example. code may not do exactly what you need it to do.


Private Sub Worksheet_Change(ByVal Target As Range)

for each cell in Target
If cell.column = 1 Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = True
ActiveSheet.Range("F9").Locked = False
[G9:K9].Interior.ColorIndex = 15
[F9].Interior.ColorIndex = 0
ActiveSheet.Protect ("")
Else
If cell.column = 2 Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = False
ActiveSheet.Range("F9").Locked = True
[G9:K9].Interior.ColorIndex = 0
[F9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
Else
ActiveSheet.Unprotect ("")
ActiveSheet.Range("F9:K9").Locked = True
[F9:K9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
end if
End If
next cell
End Sub


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Do it for "n" cells
Check your other thread.

Kelson wrote:

Hi all

My code reads one cell and through the options it locks or not others cells.
Im trying to extend my programming to all subsequent cells. How can I do it?
Like using "for" instruction?!?! Does anybody help me?

Private Sub Worksheet_Change(ByVal Target As Range)
If [E9]= "A" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = True
ActiveSheet.Range("F9").Locked = False
[G9:K9].Interior.ColorIndex = 15
[F9].Interior.ColorIndex = 0
ActiveSheet.Protect ("")
ElseIf Target.Cells.Text = "B" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = False
ActiveSheet.Range("F9").Locked = True
[G9:K9].Interior.ColorIndex = 0
[F9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
Else
ActiveSheet.Unprotect ("")
ActiveSheet.Range("F9:K9").Locked = True
[F9:K9].Interior.ColorIndex = 15
ActiveSheet.Protect ("")
End If

End Sub

Thanks in advance

Kelson



--

Dave Peterson
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default Do it for "n" cells
Hi Joel

I got it working!
Have a look on it:


Private Sub Worksheet_Change(ByVal Target As Range)

n = Target.Cells.Row

If Range("E" & n).Value = "A Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G" & n & ":K" & n).Locked = True
ActiveSheet.Range("F" & n).Locked = False
ActiveSheet.Protect ("")
Else
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G" & n & ":K" & n).Locked = False
ActiveSheet.Range("F" & n).Locked = True
ActiveSheet.Protect ("")
End If

End Sub



Thank you anyway!



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
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
Check if cells contain the word "Thailand", return "TRUE" ali Excel Worksheet Functions 7 September 14th 07 09:53 AM
Do mighty exchange "pixels" on "mm" - cells (width, height)? :( And1 Excel Programming 0 May 1st 06 01:17 PM
Can you "duplicate" "copy" listboxes and code to multiple cells? HotRod Excel Programming 1 September 1st 04 05:03 PM
Using "Cells" to write "Range("A:A,H:H").Select" Trip Ives[_2_] Excel Programming 3 June 5th 04 03:13 PM


All times are GMT +1. The time now is 07:38 PM.

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