Previous Topic Next Topic

Correcting VBA code in Excel

Solved/Closed
Issa - Feb 6, 2012 at 08:32 AM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 - Mar 15, 2012 at 10:25 AM
Hello dears,

I have the following VBA code :


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

1 reply

Answer 1 / 1
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 514
Feb 6, 2012 at 09:42 AM
Hi 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


Best regards,
Trowa
0
Issa
Feb 6, 2012 at 02:17 PM
Dear Trowa,

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.

Best regards,
Issa
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 514
Feb 7, 2012 at 09:50 AM
Hi Issa,

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.

Kind regards,
Trowa
0
issahamameh03
Posts
3
Registration date
Wednesday February 8, 2012
Status
Member
Last seen
December 13, 2013
Feb 8, 2012 at 08:56 AM
Hi Trowa,

Yes you are absolutely right, I mean (if(and)) not (if(or)), sorry for that.

By the way, I became now a member in Kioskea.net now :) (my username is "issahamameh03")

Thank you again for all your interest and support.

BR,
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
 514
Feb 9, 2012 at 10:04 AM
Wow, are there already two other issahamameh's that you had to add 03?! ;-)
I'm still gonna call you Issa, if you don't mind.

But your query remains unsolved right? The formula containing AND instead of OR doesn't change the problem.

Kind regards,
Trowa
0
issahamameh03
Posts
3
Registration date
Wednesday February 8, 2012
Status
Member
Last seen
December 13, 2013
Feb 10, 2012 at 12:37 AM
Dear Trowa,

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