Results 1 to 2 of 2
  1. #1
    Join Date
    May 2013
    Posts
    5

    Unanswered: Message box based on cell value within existing VBA code

    Hi to the good people of DB Forums!

    A few months back I asked for some help with some coding to copy and delete information in one spreadsheet, and paste it into another sheet.
    The formula that was suggested worked a treat! And has been continually tested by people who have no idea what 'Excel coding' is.

    The powers that be have requested I add in a message box that will appear when a certain value is inserted into a specific cell.

    The Situation
    I have the following code in my database:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        
        Dim lngNextRow As Long
        
        Application.ScreenUpdating = False
        
        If Target.Cells.Count > 1 Then Exit Sub
        
        If Target.Column = 10 And Target.Row > 1 Then
            
            lngNextRow = Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Row + 1
            Target.EntireRow.Copy Sheets(Target.Value).Range("A" & lngNextRow)
            Target.EntireRow.Delete xlUp
        
        End If
        
        Application.ScreenUpdating = True
        
    End Sub
    Which, basically means what ever value is put into column 'K', it will remove the data from this sheet, and put it into the corresponding sheet in the workbook.

    Columne 'K' will only ever be blank, or have 'paid' or 'withdrawn' in it.

    The Code
    Apart from simple bulls**t code to have a message box appear, I have been unable to even think of the coding that will go into the existing code to make it work. I'm blaming a mixture of de-motion, month-long sickness and pure unadulterated laziness/demotivation!

    The Plea
    I need a message box to pop-up only when 'withdrawn' is selected in column 'k', that reminds users to make sure a column is filled out once the data is transferred over.

    I have a thought that maybe the coding will go before this part of the original code:
    Code:
            lngNextRow = Sheets(Target.Value).Cells(Rows.Count, "A").End(xlUp).Row + 1
            Target.EntireRow.Copy Sheets(Target.Value).Range("A" & lngNextRow)
            Target.EntireRow.Delete xlUp
    But, I think it may be a little beyond my understanding right now.

    Thank you all so much for your help. As last time, it will prove to be invaluable!

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Not entirely sure what you want but maybe somthing like this
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim lngNextRow As Long
        Dim bWithdrawn As Boolean
        
        Application.ScreenUpdating = False
        
        With Target
            If .Cells.Count > 1 Then Exit Sub
            
            bWithdrawn = False
            
            If .Column = 10 And Target.Row > 1 Then
                If UCase(Cells(.Row, .Column + 1)) = "WITHDRAWN" Then bWithdrawn = True
                lngNextRow = Sheets(.Value).Cells(Rows.Count, "A").End(xlUp).Row + 1
                .EntireRow.Copy Sheets(.Value).Range("A" & lngNextRow)
                .EntireRow.Delete xlUp
                
                If bWithdrawn Then
                    MsgBox "You have just tranfered 'Withdrawn'.  Please fill out column 'K' as required!", vbExclamation, "Withdrawn Meaasage"
                End If
            End If
            
        End With
        Application.ScreenUpdating = True
    
    End Sub
    HTH


    MTB

Posting Permissions

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