If one cell for each row from range A1:D50 is checked or has value>0, lock other cells in the row

vywes

New Member
Joined
Dec 30, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
NeverPer monthPer weekPer dayTotal
[/]valuevaluevalue
How do I write the vba such that

  1. if one cell for each row from range A2:D50 is checked or has value>0, lock other cells in the row.
  2. And cell from range E2:E50 (Total) =
  • =0 if checkbox is checked
  • OR =cell B2.value divide 30 if cell B2.value>0
  • OR =cell C2.value divide 7 if cell C2.value>0
  • OR =cell D2.value if cell D2.value>0

I wrote the code like this but ran into error

VBA Code:
Sub Frequency()

Dim rng As Range, cell As Range
Set rng = Range("C14:F16")

Dim rng0 As Range, cell0 As Range, CheckBox As Object
Set rng0 = Range("C14:C16")

Dim rng1 As Range, cell1 As Range
Set rng1 = Range("D14:D16")

Dim rng2 As Range, cell2 As Range
Set rng2 = Range("E14:E16")

Dim rng3 As Range, cell3 As Range
Set rng3 = Range("F14:F16")

For Each cell In rng
    If Range("rng0").Object("CheckBox").Value = True Then
        cell0.Offset(0, 10).Value = 0
    ElseIf Range("rng1").cell1.Value >= 1 Then
        cell1.Offset(0, 9).Value = cell1.Value / 30
    ElseIf Range("rng2").cell2.Value >= 1 Then
        cell2.Offset(0, 8).Value = cell2.Value / 7
    ElseIf Range("rng3").cell3.Value >= 1 Then
        cell3.Offset(0, 7).Value = cell3.Value
    End If
    
Next cell

End Sub
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
try thia
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range) For Each cell In Range("A2:D50")  If cell.Value > 0 Or cell.Value = True Then     Range("A2:D50").Locked = True  Else     Range("A2:D50").Locked = False  End If Next cell End Sub To write the code to calculate the values in the range E2:E50, you can use the following code: Private Sub Worksheet_Change(ByVal Target As Range) For Each cell In Range("A2:D50")  If cell.Value > 0 Then    If cell.Address = "B2" Then      Range("E2").Value = cell.Value / 30    ElseIf cell.Address = "C2" Then      Range("E2").Value = cell.Value / 7    ElseIf cell.Address = "D2" Then      Range("E2").Value = cell.Value    Else      Range("E2").Value = 0    End If  Else    Range("E2").Value = 0  End If Next cell End Sub.
 
Upvote 0
try thia
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range) For Each cell In Range("A2:D50")  If cell.Value > 0 Or cell.Value = True Then     Range("A2:D50").Locked = True  Else     Range("A2:D50").Locked = False  End If Next cell End Sub To write the code to calculate the values in the range E2:E50, you can use the following code: Private Sub Worksheet_Change(ByVal Target As Range) For Each cell In Range("A2:D50")  If cell.Value > 0 Then    If cell.Address = "B2" Then      Range("E2").Value = cell.Value / 30    ElseIf cell.Address = "C2" Then      Range("E2").Value = cell.Value / 7    ElseIf cell.Address = "D2" Then      Range("E2").Value = cell.Value    Else      Range("E2").Value = 0    End If  Else    Range("E2").Value = 0  End If Next cell End Sub.
When i wrote it as Private Sub Worksheet_Change(ByVal Target As Range) I have trouble stepping into the code.

So I tried to write it this way:

VBA Code:
Sub Frequency1()

Dim rng As Range, cell As Range
Set rng = Range("B14:E16")

For Each cell In rng
    If cell.Value > 0 Or cell.Value = True Then
        Range("rng").Locked = True
    Else: Range("rng").Locked = False
End If
Next cell

End Sub

However, I ran into application-defined or object-defined error 1004 at [Range("rng").Locked = False]
 
Upvote 0
What is "rng"?

Shouldn't that be:
VBA Code:
cell.Locked = True

instead?
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top