If user presses the delete button on their keyboard in cell B2, insert text into that cell

[TheJay]

Hello there,

I understand it's possible for a worksheet to monitor for changes and if detected, run VBA using Private Sub Worksheet_Change.

I have a dropdown list in B2 and if the user presses delete on their keyboard, I'd like to run

sh.Range("B2").Value = "'Property Reference Guide (Click Arrow to Start)"

Could someone please tell me how I achieve this?

Thank you.

 

[Jimmypop]

Hi There

Why would you want to use delete button to trigger this?

 

[FormR]

Hi, you could try this is the sheet of interests code module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B2"), Target) Is Nothing Then
    If Range("B2").Value = "" Then
        Application.EnableEvents = False
        Range("B2").Value = "'Property Reference Guide (Click Arrow to Start)"
        Application.EnableEvents = True
    End If
End If
End Sub

 

[TheJay]

Hi There

Why would you want to use delete button to trigger this?

This is a work around to the problem with having text in a dropdown box as an instruction, which disappears forever once someone chooses an option.

When the workbook opens, it clears anything entered into the dropdown boxes by whoever last used it irrespective of whether they saved changes. I have multiple dropdown boxes and reset buttons. The answer was perfect for my needs.

When my workbook opens, it runs the following:

Private Sub Workbook_Open()
    Dim sh As Worksheet
    For Each sh In Worksheets
        If sh.Name = "Property Numbering" Then
            sh.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            sh.Range("C14,C8").ClearContents
            sh.Range("B2").Value = "'Property Reference Guide (Click Arrow to Start)"
            sh.Range("C14,C8").Value = "'Choose"
        ElseIf sh.Name = "VO Areas" Then
            sh.Protect UserInterFaceOnly:=True, AllowSorting:=True, AllowFiltering:=True
            sh.Range("C4").ClearContents
            sh.Range("C4").Value = "'Choose P"
        Else
            sh.Protect UserInterFaceOnly:=True
        End If
    Next
End Sub

Followed by:

Option Explicit

Private Sub Worksheet_Activate()
'    Call DropDownListToDefault
    Range("B2").Select
    With Worksheets("Property Numbering")
    With ActiveWindow
        .DisplayFormulas = False
        .DisplayHeadings = False
        .DisplayGridlines = False
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
    End With
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
    End With
    With Application
        .CommandBars("Full Screen").Visible = True
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Standard").Visible = False
        .CommandBars("Formatting").Visible = False
    End With
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sh          As Worksheet
    Dim formatting  As Range
    Dim example     As Range
    Dim instructions As Range
    Dim help        As Range
        If Target.Address = "$B$2" Then
        Set sh = ActiveSheet
        With sh
            Set formatting = .Range("B7:J9,B12")
            Set example = .Range("B13:J15,B18")
            Set instructions = .Range("B19:J22")
            Set help = .Range("B24:J24")
            Union(formatting, example, instructions, help).EntireRow.Hidden = True
            If Target = "Formatting" Then formatting.EntireRow.Hidden = False
            If Target = "Example" Then example.EntireRow.Hidden = False
            If Target = "Instructions" Then instructions.EntireRow.Hidden = False
            If Target = "Help" Then help.EntireRow.Hidden = False
        End With
    End If
    If Not Intersect(Range("B2"), Target) Is Nothing Then
        If Range("B2").Value = "" Then
            Application.EnableEvents = False
            Range("B2").Value = "'Property Reference Guide (Click Arrow to Start)"
            Application.EnableEvents = True
        End If
    End If
    If Not Intersect(Range("C8"), Target) Is Nothing Then
        If Range("C8").Value = "" Then
            Application.EnableEvents = False
            Range("C8").Value = "'Choose"
            Application.EnableEvents = True
        End If
    End If
    If Not Intersect(Range("C14"), Target) Is Nothing Then
        If Range("C14").Value = "" Then
            Application.EnableEvents = False
            Range("C14").Value = "'Choose"
            Application.EnableEvents = True
        End If
    End If
End Sub

 

[Jimmypop]

Thanks for clarifying