Allow enter value

How to allow and not allow to enter value based on other value

For example
Allow to input customize value in B2 if A1=Yes
If A1=No than B2 = sum(c1:c5)
Jamal AlbusaidiAsked:
Who is Participating?
 
LET (Learn Excel in Tamil)Connect With a Mentor Reporting Automation ExpertCommented:
Hi Jamal,

Here you can go

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    If Range("A1").Value = "No" Then
        Cells.Select
        ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
        Selection.Locked = False
        Range("B2").Value = "= sum(c1:c5)"
        Range("B2").Select
        Selection.Locked = True
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
     Else
        Cells.Select
        ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False
        Selection.Locked = False
        Selection.FormulaHidden = False
        Range("B2").Value = ""
    End If
End If
End Sub

Open in new window

Book1.xlsm
0
 
Roy CoxGroup Finance ManagerCommented:
Any manual entry in B1 will overwrite the formula in it. You need to have an alternative cell for manual input, e.g. D1

=IF(A1="NO",D1,SUM(C1:C5))
0
 
Roy CoxGroup Finance ManagerCommented:
You could even ive an instruction to make the alternative entry in D1

=IF(A1="NO",SUM(C1:C5),IF(ISBLANK(D1),"Enter in D1",D1))
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Jamal AlbusaidiAuthor Commented:
I want to look cell from end user if it no
0
 
LET (Learn Excel in Tamil)Reporting Automation ExpertCommented:
Hi Jamal,

Please copy and paste this code on Worksheet, if user change the value on A1, macro will run automatically and apply the formula if A1 = No, else B2 will be blank user can update there own value

Also attached snap shot, Please make sure highlighted fields are correct selected

.Please make sure highlighted fields are correct selected
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    If Range("A1").Value = "No" Then
    Range("B2").Value = "= sum(c1:c5)"
    Else
    Range("B2").Value = ""
    End If
End If
End Sub

Open in new window

0
 
Roy CoxGroup Finance ManagerCommented:
I wouldn't recommend VBA for this with the problems of having to explain how to enable macros, etc. If you do use it the because A1 is actually the Target in this event then the code should refer to it as Target and the result may as well be automatically entered and UCase allows for NO.No or no to be used.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$1" Then Exit Sub
    If UCase(Target.Value) = "NO" Then
        Range("B2").Value = Application.WorksheetFunction.Sum(Range("C1:C5"))
    Else: Range("B2").Value = ""
    End If
End Sub

Open in new window

0
 
Jamal AlbusaidiAuthor Commented:
Thanx
But how lock cell from update if cell =sum
0
 
Roy CoxGroup Finance ManagerCommented:
Are you talking with code? The worksheet would need to be protected
0
 
Roy CoxGroup Finance ManagerCommented:
here's an example

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Const PW As String = "Secret"
    If Target.Address <> "$A$1" Then Exit Sub
    Me.Unprotect PW
    If UCase(Target.Value) = "NO" Then
        With Range("B2")
            .Value = Application.WorksheetFunction.Sum(Range("C1:C5"))
            .Locked = True
        End With
        Me.Protect PW
    Else: Range("B2").Value = ""
    End If
End Sub

Open in new window

Lock-Cell.xlsm
0
 
Jamal AlbusaidiAuthor Commented:
It work but if A1 is No than i cant change it value or any other value in worksheet ... want to look only one cell
0
 
Roy CoxGroup Finance ManagerCommented:
You need to set up sheet protection properly. The code only locks B2 in my example all other cells remain unlocked, so follow the instructions here:
Protect a worksheet
0
 
Jamal AlbusaidiAuthor Commented:
Thanx as I expected
1
 
Roy CoxGroup Finance ManagerCommented:
This code allows input by the user

[code]Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Const PW As String = "Secret"
    Dim ans
    If Target.Address <> "$A$1" Then Exit Sub
    Me.Unprotect PW
    If UCase(Target.Value) = "NO" Then
        With Range("B2")
            .Value = Application.WorksheetFunction.Sum(Range("C1:C5"))
            .Locked = True
        End With
        Me.Protect PW
    Else: ans = Application.InputBox("What do you need to enter")
        If ans <> False Then
            Range("B2") = ans
        Else: Range("B2") = ""
        End If
    End If
End Sub

Open in new window

[/code]
0
 
Roy CoxGroup Finance ManagerCommented:
Sorry I bothered trying to help, Obviously you don't appreciate correct code. There's no need to select cells, that is accepted by all efficient coders and what I said about using Target should be addressed plus you do not need ActiveSheet because the code will automatically refer to the sheet it is contained in, you can use the keyword  Me to refer to the sheet in this instance
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.