Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Help with Message Box (Excel 2002/3)

    Hi
    Please see attached

    if a code is entered in Column E, I would like message box to show the matching code in Column B, With the message This code will migrate to (show the Migration Code.)


    Ie if 406600 is entered in E2 then Msgbox should say "This code will migrate to 75111592"
    if the code entered is not in this list then nothing will happen
    This is just a sample of the range

    This is for advice only nothing will be done with code the user will just close the Msgbox
    and so on Columns A and B will be in a hidden worksheet. I have used the range name "migration"
    This is my first thought on the matter, maybe there is a simpler way, <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with Message Box (Excel 2002/3)

    You could use the Worksheet_Change event in the sheet module. Right-click the sheet tab and select View Code from the popup menu to show the sheet module.

    Here is an example that will display a message box:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Dim strVal As String
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("E:E")).Cells
    strVal = ""
    oCell.Validation.Delete
    On Error Resume Next
    strVal = Application.WorksheetFunction.VLookup _
    (oCell, Range("A:B"), 2, False)
    On Error GoTo 0
    If Not strVal = "" Then
    MsgBox "This code will migrate to " & strVal, vbInformation
    End If
    Next oCell
    End If
    End Sub

    Here is a slightly different version that adds an input message to the cell using Data | Validation. The message will be displayed each time the cell is selected.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Dim strVal As String
    If Not Intersect(Target, Range("E:E")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("E:E")).Cells
    strVal = ""
    oCell.Validation.Delete
    On Error Resume Next
    strVal = Application.WorksheetFunction.VLookup _
    (oCell, Range("A:B"), 2, False)
    On Error GoTo 0
    If Not strVal = "" Then
    With oCell.Validation
    .Add xlValidateInputOnly, xlValidAlertInformation
    .InputTitle = "Notice"
    .InputMessage = "This code will migrate to " & strVal
    End With
    End If
    Next oCell
    End If
    End Sub

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Help with Message Box (Excel 2002/3)

    Hi Hans

    The second version wiil be perfect for my purpose .

    Thanks very much.

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Help with Message Box (Excel 2002/3)

    Hi Hans

    I am using this code that you very kindly supplied for me (modified to suit, I am having a little problem with the Data Validation version,

    I need to have the sheet protected, if I do the code gives an error.

    vate Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Dim strVal As String
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("B:B")).Cells
    strVal = ""
    oCell.Validation.Delete
    On Error Resume Next
    strVal = Application.WorksheetFunction.VLookup _
    (oCell, Range("CD:CE"), 2, False)
    On Error GoTo 0
    If Not strVal = "" Then
    With oCell.Validation
    .Add xlValidateInputOnly, xlValidAlertInformation <--(Code stops Here)
    .InputTitle = "Notice"
    .InputMessage = "This code will migrate to " & strVal
    End With
    End If
    Next oCell
    End If
    End Sub

    Can you help please.

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with Message Box (Excel 2002/3)

    You'll have to unprotect the sheet before changing validation, and protect it again afterwards.

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Help with Message Box (Excel 2002/3)

    Hi Hans

    I know how to unprotect and protect a sheet with code but I am not sure where to place it in your code.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with Message Box (Excel 2002/3)

    Put the code to unprotect the sheet at the beginning of the procedure, immediately below the first If ... Then line, and the code to protect the sheet again near the end, immediately above the last End If line.

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Help with Message Box (Excel 2002/3)

    Hi Hans

    Thanks very much.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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