Private Sub Worksheet_Change(ByVal Target As Range)
Dim cRow As Integer
If Intersect(Target, Range("R11:R20")) Is Nothing Then Exit Sub
If Target.Value = "F" Then
ActiveSheet.Unprotect
cRow = Target.Row
Range(Cells(cRow, "T"), Cells(cRow, "U")).Locked = True
ActiveSheet.Protect
End If
If Target.Value = "T" Or Target.Value = "" Then
ActiveSheet.Unprotect
cRow = Target.Row
Range(Cells(cRow, "T"), Cells(cRow, "U")).Locked = False
ActiveSheet.Protect
End If
End Sub
What I need is to ClearContents for (cRow target.Row) in the upper case first before it is locked.
Thanks a lot to any assistance.
Best regards,
Issa
I have Excel sheet to calculate overtime, includes several rows for each month, and the sheet is protected but allowing the user to enter data into some specified range (P11:P20,R11:X20) (Allow user to edit range).
Also, the Range (R11:R20) are rows with drop list menu of 4 values (1,2,3,4).
My case: For example, if the user selected either value 3 or 4 ONLY in cell (R15) I need to block or protect the range (T15:U15), and if then with the same sheet selected 3 or 4 value in cell R19 to protect the range (T19:U19) and so on.
Therefore I need if some value selected in specific cell to protect some ranges within the same row. Please note that the user may select one selection or multi selection in the same sheet.
I hope it's clear and you can find solution.
Thank you in advance for any kind of assistance and best regards,
Issa
Good to see you again. Did you know that when you become a member it is easier to keep track of your messages? Just a suggestion.
For your current query I assumed you want to clear the contents of column T and U of the target row.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cRow As Integer
If Intersect(Target, Range("R11:R20")) Is Nothing Then Exit Sub
If Target.Value = "F" Then
ActiveSheet.Unprotect
cRow = Target.Row
With Range(Cells(cRow, "T"), Cells(cRow, "U"))
.ClearContents
.Locked = True
End With
ActiveSheet.Protect
End If
If Target.Value = "T" Or Target.Value = "" Then
ActiveSheet.Unprotect
cRow = Target.Row
Range(Cells(cRow, "T"), Cells(cRow, "U")).Locked = False
ActiveSheet.Protect
End If
End Sub
Thank a lot to all your assistance.
Yes I would like to be one member of the greatest experts in programming, but how can I?
The code is working in normal situation, but my problem didn't solved yet.
The problem is that the value of Column (R) depending on the value of column (T)and (U), The function in column (R) is "IF formula" depending on column (T)and (U).
For example:
Column (R1): =if(or(T1>5,U1="M"),"F","T")
Now I need if the user entered (10) in (T1 ) and (M) in (U1) to run the code automaticly.
But I noticed that it will work only after going to (R1) and pressing F2 then Enter-Key.
Hope it's more clear now.
Thank you again in advance to all your support and help.
So if T1=6, R1 will be "F", this will clear T1 of it's contents.
T1 is now empty, thus R1 will be "T".
As you can see, R1 can never be "F"; if it does, the code will remove the requirements for it to be "F".
Maybe I misunderstood and you want to clear the contents of the targets row except the value's of R, T and U?
Please clearify.
And you don't have to be an expert (or helping others for that matter) to join Kioskea. It's just more convenient to be a member when you use this site on frequent occasion.
Of course I don't mind to call, if needed my mobile Personal data deleted by aquarelle (Moderator)
I have an idea in order my problem to be solved that if you can change the code to be from (Private Sub Worksheet_Change) to (Private Sub CommandButton1_Click()), in this case just change the code to fit this option then I'm sure it will work. I would be very grateful for that.
I didn't meant for you to give your mobile, I meant that I am going to refer to you as Issa instead of issahamameh03. Please be carefull posting personal data.
I have altered the code as requested, but I'm still standing behind my post of Feb 7.
Here is the code:
Private Sub CommandButton1_Click()
Dim cRow As Integer
For Each cell In Range("R11:R20")
If cell.Value = "F" Then
ActiveSheet.Unprotect
cRow = cell.Row
With Range(Cells(cRow, "T"), Cells(cRow, "U"))
.ClearContents
.Locked = True
End With
ActiveSheet.Protect
End If
If cell.Value = "T" Or cell.Value = "" Then
ActiveSheet.Unprotect
cRow = cell.Row
Range(Cells(cRow, "T"), Cells(cRow, "U")).Locked = False
ActiveSheet.Protect
End If
Next cell
End Sub
I need to make two codes for this type (Private Sub Worksheet_Change) but each one will work on different range with diferent actions.
Also I noticed that if I wrote two codes with the same name(i.e: Private Sub Worksheet_Change) it will give the following message:"Compil error: Ambigous name detected: Worksheet_Change"
Can you please help me in that.
Please note: I already wrote my question seperatly and in details in another question titled: "How to make 2 VBA codes to work correctly"
Of course I don't mind to call, if needed my mobile Personal data deleted by aquarelle (Moderator)
I have an idea in order my problem to be solved that if you can change the code to be from (Private Sub Worksheet_Change) to (Private Sub CommandButton1_Click()), in this case just change the code to fit this option then I'm sure it will work. I would be very grateful for that.
Thank you and best regards,
I didn't meant for you to give your mobile, I meant that I am going to refer to you as Issa instead of issahamameh03. Please be carefull posting personal data.
I have altered the code as requested, but I'm still standing behind my post of Feb 7.
Here is the code:
Kind regards,
Trowa
Yes it is solved finally :-)
Thanks a lot dear, you are really so gentle and supportive person and so kind.
I do appreciate all what you did for me.
Best regards,
Hi, how you can help me in the following:
I need to make two codes for this type (Private Sub Worksheet_Change) but each one will work on different range with diferent actions.
Also I noticed that if I wrote two codes with the same name(i.e: Private Sub Worksheet_Change) it will give the following message:"Compil error: Ambigous name detected: Worksheet_Change"
Can you please help me in that.
Please note: I already wrote my question seperatly and in details in another question titled: "How to make 2 VBA codes to work correctly"
Thanks a lot in advance for all your help :)
Issa
Best regards,
Trowa