Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Correcting VBA code in Excel [Solved/Closed]

Report
-
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
 -
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
See more

1 reply

Answer 1 / 1
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
 440
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
View all 9 comments
Report
Posts
3
Registration date
Wednesday February 8, 2012
Status
Member
Last seen
December 13, 2013
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,
Report
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
 440
Hi Issa,

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

Kind regards,
Trowa
Report

Dear 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,
Report

Dear Trowa,

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
Report
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
 440
Sure thing Issa, take a look at you new question.

Best regards,
Trowa
Ask a question