none
how to generate a message box when i selct a value in acell and if i click ok it should dissaper RRS feed

  • Question

  • when a value "RPM" is entered in cell K9 in sheet "request" a message box should appear and when we click ok on that it should disappear

    K9=sheet("welcome!I8)

    sheet "request " is generated when we click a shape in sheet"welcome"

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      
        
        Select Case Range("$K9").Value
                Case Is = "RPM    - Repairs from PM"
       
       
            MsgBox "Please select the Equipment" & Target.Address
           
        End Select
       
    End Sub

    Monday, November 13, 2017 5:58 AM

Answers

  • D,
    re:  show a message box when...

    The request sheet that is generated should already contain the code.
    You can do this by creating the sheet and hiding it. When needed you can then copy it.
    The code should be in the "Worksheet_Change" event sub as follows...
    '---
    Option Explicit
    Option Compare Text

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Me.Range("K9").Address Then
      If Target.Value = "rpm" Then
        Target.Select
        MsgBox "Please select the Equipment   ", vbInformation, "Title Goes Here"
      End If
    End If
    End Sub
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by DEEPU1980 Thursday, November 16, 2017 8:39 AM
    Tuesday, November 14, 2017 4:14 PM

All replies

  • D,
    re:  show a message box when...

    The request sheet that is generated should already contain the code.
    You can do this by creating the sheet and hiding it. When needed you can then copy it.
    The code should be in the "Worksheet_Change" event sub as follows...
    '---
    Option Explicit
    Option Compare Text

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Me.Range("K9").Address Then
      If Target.Value = "rpm" Then
        Target.Select
        MsgBox "Please select the Equipment   ", vbInformation, "Title Goes Here"
      End If
    End If
    End Sub
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by DEEPU1980 Thursday, November 16, 2017 8:39 AM
    Tuesday, November 14, 2017 4:14 PM
  • good its working fine when we manually input the value "rpm" since in my worksheet iam copyinh this value from another sheet("welcome") so its not working , is there any code so that when this sheet("Request") is unhide the value of "K9"=(Welcome!18)
    Thursday, November 16, 2017 5:03 AM
  • one more doubt can we change the position of the display message
    Thursday, November 16, 2017 5:05 AM
  • D,
    re:  "not working"

    It does work for me when pasting. 
      You may have ignored the "Option Compare Text" part of the code?
      Also, your paste may be including spaces or other hidden characters?

    If you replace this code line...
    '---
      "If Target.Value = "rpm" Then"
    With...
      "If VBA.InStr(1, Target.Value, "rpm", vbTextCompare) > 0 Then"
    '---
    you will have more flexibility on what is pasted into cell K9.
    It will still display the message even if you were to paste "the car is idling at 900 rpm"

    Jim Cone
    • Edited by James Cone Thursday, November 16, 2017 11:03 AM line spacing
    Thursday, November 16, 2017 10:54 AM
  • D,
    re:  change message position

    Possible, but requires much more effort (designing a user form), and can result in an approximate position.  If you want to go this route then start a new question.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)
    Thursday, November 16, 2017 11:02 AM