Prompt user when cell is changed

Question

Hello Everyone,

I'm very new to creating macros and programming in general. I have a worksheet of 38 tabs. 31 tabs are for the days of the month. I would like to create a macro that will prompt users with a warning message any time "MCO" is selected in column N for each of these 31 tabs. Is that possible?

Thanks

Answer 1

It is possible, using a workbook level SheetSelectionChange event. In the ThisWorkbook module in your VBA project paste the following code:

Option Compare Text
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim SelectedCellsInColN As Range
    ' Make a range of the cells in Target range that intersect with the cells in the N   column
    Set SelectedCellsInColN = Intersect(Target, Target.Parent.Range("N1:N" & CStr(Target.Parent.Rows.Count)))

    If Not SelectedCellsInColN Is Nothing Then
        ' The user has selected a cell in column N
        Dim CurrCell As Range
        For Each CurrCell In SelectedCellsInColN
            ' If the cell's value contains mco (in any case) then prompt the user with a messagebox
            If CurrCell.Value Like "*MCO*" Then
                MsgBox "You've selected MCO"
                ' Exit the sub so we don't keep bugging the user about it...
                Exit Sub
            End If
        Next CurrCell
    End If
End Sub

Basically what the code does is look at the Target range to see if any cells in the N column are selected, and then loops through any of those cells in column N that are selected to see if their value contains MCO (you can get rid of the stars if you only want a prompt when the cell contains just "MCO"), and if so prompts the user and exits out.

Hope that helps.

-Jon

Answer 2

Are you looking for a macro solution or a vba solution. The two are different. For the macro run through the steps using the macro recoder, for a VBA solution start with Jon's answer