Clever Geek Handbook

User prompt when changing a cell

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

thank

0


source to share


2 answers


Perhaps using the SheetSelectionChange event at the workbook level. 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 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 column N are selected and then scrolls through any of those cells in column N that are selected to see if the MCO contains them (you can get rid of the stars if you only want a prompt, when the cell only contains "MCO") and if so, calls the user in and out.



Hope it helps.

-Jon

+2


source


Are you looking for a macro solution or vba solution. They are different. For a macro to be executed with a macro encoder, for a VBA solution, start with Jon's answer



0


source






More articles:


All Articles