• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

Clear cell on dropdown select

Hi, i have a ss attached, when a user selects "Edit" in B31, i want it to enter the word "Enter Target" in K31

if "Delete" or "No Change" is selected i want the cell to be clear

I need these to activate on changing of the dropdown box

You can see now the desired result is achieved through formula


Thanks
Book2.xlsx
0
Seamus2626
Asked:
Seamus2626
  • 4
  • 2
1 Solution
 
Rgonzo1971Commented:
Hi,

I suppose it is an extension of your previous question (Q_28500836)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B31:B59")) Is Nothing And Target.Rows.Count = 1 Then
        If Target.Value = "Delete" Or Target.Value = "No Change" Then
            pw = InputBox("Enter password to delete.", "Password Required")
            If pw = "rumin8" Then
                Me.Unprotect Password:="obfuscate"
                Target.Offset(0, 9).ClearContents
                Me.Protect Password:="obfuscate"
            End If
        ElseIf Target.Value = "Edit" Then
            pw = InputBox("Enter password to edit.", "Password Required")
            If pw = "rumin8" Then
                varValue = InputBox("Enter new value:", "Edit Value")
                If varValue <> "" Then
                    Me.Unprotect Password:="obfuscate"
                    Target.Offset(0, 9).Value = varValue
                    Me.Protect Password:="obfuscate"
                End If
            End If
        End If
    End If
End Sub

Open in new window

Regards
0
 
Seamus2626Author Commented:
Thanks Rgonzo, but i dont want any passwords,

its -

If B31 = "Edit" then K31 = "Enter target"

else K31 is blank

Thanks
0
 
Seamus2626Author Commented:
Apologies, let me restate
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Seamus2626Author Commented:
its -

If B31 = "Edit" then K31 = "Enter target"

if B31 = No change K31 = "=I31"

If B31 = "Delete" K31 = "=IF((I35-J35)<0,I35,(J35))"

Thanks
0
 
Rgonzo1971Commented:
Hi,

pls try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B31:B59")) Is Nothing And Target.Rows.Count = 1 Then
        If Target.Value = "Delete" Then
            Target.Offset(0, 9).FormulaR1C1 = "=IF((RC[-2]-RC[-1])<0,RC[-2],RC[-1])"
        ElseIf Target.Value = "No Change" Then
            Target.Offset(0, 9).FormulaR1C1 = "=RC[-2]"
        ElseIf Target.Value = "Edit" Then
            Target.Offset(0, 9).Value = "Enter Target"
        End If
    End If
End Sub

Open in new window

Regards
0
 
Seamus2626Author Commented:
Perfect! Thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now