Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Unanswered: Display Message when Cell Changed

    I need to display a message when particular cells on an Excel 2003 worksheet are changed. I am familiar with VBA but this one has me stumped and I am in a crunch.
    Thanks.
    Jerry

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy, Jerry. This requires using code in the Worksheet module. Right-click on the worksheet tab and choose View Code. In the resulting VBE window in the upper left choose Worksheet,a nd in the dropdown in the upper right, choose "Change". This will bring up the first and last lines for the code. Then refer to the range and use Intersect with it and Target, to determine what should happen if one of the cells change.

    If you need more, post back.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    It works! Now I would like to know if this worksheet event can be applied to every worksheet in the workbook, even added by someone else via my macro (VBA). The reason is that a subset of the worksheet will be copied into an added worksheet, which will be in the same format but the information will have been filtered. How can I get this distributed Excel workbook to apply the same worksheet event to an added worksheet? I will use VBA to let the user select their own information and put it in an added worksheet.

    Thanks.
    Jerry

    HTML Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        
        Dim cellRef1 As String, cellRef2 As String
        Dim cellValue1 As String, cellValue2 As String
        ' ...notice variables are declared in a module...notify only one time for each
        If BK15Notice = True And BK885Notice = True Then Exit Sub
        cellRef1 = "G" & ActiveCell.Row
        cellRef2 = "H" & ActiveCell.Row
        cellValue1 = Trim(Range(cellRef1).Value)
        cellValue2 = Trim(Range(cellRef2).Value)
       
        If cellValue1 <> "885-081" _
            And cellValue2 <> "15" Then
                Exit Sub
        End If
        
        If cellValue1 = "885-081" Then
            If BK15Notice <> True Then
                MsgBox "Don't forget to change data for Bank 15" & Chr(13)
            End If
            BK15Notice = True
        Else
            If BK885Notice <> True Then
                MsgBox "Don't forget to change data for Bank 885" & Chr(13)
            End If
            BK885Notice = True
        End If
    End Sub

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Glad that it works. Yes, you can write code to have VBA write additional code. It is a bit more complicated. I will research and find more info - I've read it (in a couple of palces), but haven't done it yet.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Bob (xldynamics) has given some direction on this. Here is a sample:

    Code:
    Sub AddWorksheetEventProc() 
        Dim StartLine As Long 
         
        With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule 
            StartLine = .CreateEventProc("Change", "Worksheet") + 1 
            .InsertLines StartLine, _ 
            "Dim ans" & vbCrLf & _ 
            "   ans = Msgbox( ""All OK"",vbYesNo)" & vbCrLf & _ 
            "   If ans = vbNo Then Cancel = True" 
        End With 
    End Sub
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Mar 2006
    Posts
    163
    Jerry

    If you want the code to run when a change is made in any sheet take a look at the SheetChange event in the ThisWorkbook module.

    The code stub for it looks like this.
    Code:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    End Sub
    This event has 2 parameters passed to it Sh, the sheet where the change was made, and Target, the range on that sheet that was changed.

    You can use these parameters in your code to decide what actions to take.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •