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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Jamal AlbusaidiAuthor Commented:
I want to look cell from end user if it no
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

LearnReporting 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
LearnReporting 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.