How to show a confirmation pop-up that allows to edit specific text that exists in any cell of an excel file using a macro?

user_12801970 0
Viewed 120

What I'm trying to implement is to display a Pop-up if the user tries to modify a cell that contains the text "don't edit". The user will be able to modify the cell value only if accepts the the confirm button.

How could I apply this condition to a column that can grow and the position of the text "don't edit" can exists on any cell of the column 4?

I try with this macro and it works only to detect if the value of column D1, D2 and D3 cell of the columns 4 changes, then show a pop-up. But I don't know how to show a pop-up to request the confirmation to allows edit the cell if the user accepts, otherwise keep the "don't edit" value.

This my first attempt to show a message when the user changes a value:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Column = 4 Then
     If Target.Row = 1 Or Target.Row = 2 Or Target.Row = 3 Then
        Beep
        MsgBox "Do you really want to change it?", vbYesNo + vbQuestion
     End If
  End If
End Sub

This a sample of my worksheet

enter image description here

vba excel
2 Answers

Consider the below modified version of your code which will throw the MsgBox prompt when any cell is selected where column 4 in the selected row contains "don't edit".

It checks if Column 4 (Column D) in the row of the selected cell contains "don't edit". If true it triggers the msgbox to prompt your user.

The Return variable captures the response of the user (Yes or No) using the return value for the button clicked then either allows the selection (and thus the edits) if Yes is returned or selects cell A1 if No is returned.

Private Sub Worksheet_SelectionChange(ByVal target As Range)
    Dim Result As Long
    If Selection.Count = 1 Then
        If LCase(Me.Cells(target.Row, 4)) = "don't edit" Then
            Beep
            Result = MsgBox("Do you really want to change it?", vbYesNo + vbQuestion)
            If Result = 6 Then '6 is the return value for the "Yes" button.
    
            Else
                Range("A1").Select
            End If
        End If
    Else
        'Do something if more than one cell is selected.
        MsgBox "More than one cell is selected.", vbCritical, "You can't do that"
        Range("A1").Select
    End If
End Sub

Note: The Range("A1").Select is a placeholder for whatever action you want to take should the user click No on the Msgbox.

Note: you could use the MsgBox return constants rather than value such as vbYes or vbNo - See below for documentation.

MsgBox Function - See here for further information on the MsgBox function including the button arguments (what constants/values return for which button clicks etc.)

user_96630060

You could change your code to the following to implement what you want:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 4 Then
        'check that only a single cell is selected
        If Target.CountLarge = 1 Then
            'if don't edit ask question
            If LCase(Target) = "don't edit" Then
                Beep
                answer = MsgBox("Do you really want to change it?", vbYesNo + vbQuestion)
                If answer = vbYes Then
                    'take yes action
                Else
                    'take no action
                End If
            End If
        End If
    End If
End Sub

Target is the current range (cell or cells selected) so you can check that only a single cell is selected. If so you can check the value of that cell for "don't edit" Then take the appropriate action once the user click a confirm button.

user_27803000
Related