• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Use Input Message Box to display Vlookup Result

911Kid

New Member
Hello,

I was wondering if it is at all possible to use the Input message box that shows up with any cell that is clicked part of the Data Validation to do a vlookup on another sheet in the same workbook to return the name and last name of the employee id in the selected cell..

Just to give a general overview of the request, I work on a monthly bonus adjustment file which has many worksheets and each worksheet I want to be able to click on a cell that has an EmpID, which is always a 6 digit number formatted as text such as 612542 or 001245, etc..., and when I click on the cell, I want the input message box to pop up and display the name and last name of the selected cells EmpID by doing a look up from another worksheet in the same workbook. The input message will show until I click on another part of the sheet which will make the pop up input message to go away.
 

Attachments

  • Input Message VLOOKUP function.xlsx
    34.6 KB · Views: 17
Hi ,

I doubt that what you want can be done using only Data Validation.

It can be done using VBA. If this is acceptable , upload a workbook with data in it , and indicate which cells in which worksheet need this feature.

Narayan
 
Here is the file that shows what I'm trying to accomplish... Thank you for all the support.. :)
 

Attachments

  • Input Message VLOOKUP function sample file.xlsx
    130.6 KB · Views: 15
Hi ,

This is a very straightforward technique ; however , each time the message box is displayed , you will have to either click on OK or press the ESC key to exit the message box.

If this is inconvenient , then more complicated techniques will have to be used , which will take time to code.

Narayan
 

Attachments

  • Input Message VLOOKUP function sample file.xlsm
    133.6 KB · Views: 52
Hi Narayan,

Thank you so much for the quick turnaround, and getting this resolved. I just wanted to see if this code could be written in a way that allows the user to click on any 6 digit employee id on the sheet to get the info box to pop up, and not limited to the highlighted cells. Through out the month we add more tables to the sheet so there could be more cells with the employee id. Also, could this be set up to activate with the right click instead of the regular?
 
Hi ,

For the first question , the solution is fairly simple.

A named range called EmployeeIDs has been defined , and in the Refers To box , you will see the following :

='Doe,John #612996'!$A$4:$A$5,'Doe,John #612996'!$A$1,'Doe,John #612996'!$A$10:$A$19,'Doe,John #612996'!$F$10:$F$19

As and when you wish to include more cells , just add their cell references to the named range. Nothing else needs to be done.

For the second requirement , I am not sure how it will work , since before you do a right-click with the mouse , the cursor should be placed in a worksheet cell ; the existing macro works on the basis of an event called Selection Change , which means when ever you move the cursor to a different cell , the macro will be executed , and if the cursor happens to be on a cell within the definition of the above named range , the message box will be displayed. If the cursor is in any other cell , no action will be taken.

This cursor movement can be through the mouse or the cursor control keys on the keyboard.

So though this macro appears to run with the left-click , it actually runs because the cursor has been moved , and not because of the mouse click.

Narayan
 
For your 2nd, you can technically do it by placing Narayan's code in Before_RightClick event. But note that right click menu will appear every time after the message box.

You can cancel out of showing right click menu on those specific cells... but that may make editing those cells tedious.

See sample code below.

Code:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
            If Application.Intersect(Target, [EmployeeIDs]) Is Nothing Then Exit Sub
           
            If Target.CountLarge > 1 Then Exit Sub
                Cancel = True 'This line for cancelling out of showing right click menu
            If VBA.IsError(Application.Evaluate("=VLOOKUP(" & Target.Address & ",HRDATA,1,FALSE)")) Then
              MsgBox "Match not found"
            Else
              EmpName = Application.Evaluate("=VLOOKUP(" & Target.Address & ",HRDATA,2,FALSE)")
              JobFunction = Application.Evaluate("=VLOOKUP(" & Target.Address & ",HRDATA,3,FALSE)")
              Team = Application.Evaluate("=VLOOKUP(" & Target.Address & ",HRDATA,4,FALSE)")
              DistrictName = Application.Evaluate("=VLOOKUP(" & Target.Address & ",HRDATA,5,FALSE)")
             
              MsgBox "Employee Name :  " & EmpName & vbCrLf & _
                      "Job Function      :  " & JobFunction & vbCrLf & _
                      "Team                    :  " & Team & vbCrLf & _
                      "District Name      :  " & DistrictName
            End If
End Sub
 
Hi Narayan,

Regarding your suggestion of adding more cells manually as needed is not a possible solution as the workbook that I'm planning on implementing this code, has over 100 sheets on average every month, and each worksheet could have the 6 digit Employee ID's in various parts of the page which would defeat the whole purpose of saving time and manual work.
 
Hi ,

First , if the same concept is to be used across multiple worksheets in a workbook , then the macro which has been suggested will need to be rewritten slightly differently. You could have mentioned this earlier.

Regarding the problem of including multiple range references in the definition of the named range , will the 6-digit numbers occur only within the Payee ID columns / cells ? Can we use this to check whether the message box should be displayed or not ?

Narayan
 
Thank you and I apologize for not letting you know about the multiple sheets parts earlier.. and the 6 digit numbers would always be listed under columns with either PayeeID or SupervisorID headings..
 
Here, based on Narayan's code. Slightly modified to not use Named Range for PayeeID and SupervisorID (Used lRow & Union). This assumed Column positions are always A & F and that first data row starts at 10.

Instead of in WorkSheet module, this code goes in Workbook module.
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim lRow As Long
    lRow = Sh.Cells(Rows.Count, 1).End(xlUp).Row
            If Intersect(Target, Union(Range("A10:A" & lRow), Range("F10:F" & lRow))) Is Nothing Then Exit Sub
           
            If Target.CountLarge > 1 Then Exit Sub
           
            If VBA.IsError(Application.Evaluate("=VLOOKUP(" & Target.Address & ",HRDATA,1,FALSE)")) Then
              MsgBox "Match not found"
            Else
              EmpName = Application.Evaluate("=VLOOKUP(" & Target.Address & ",HRDATA,2,FALSE)")
              JobFunction = Application.Evaluate("=VLOOKUP(" & Target.Address & ",HRDATA,3,FALSE)")
              Team = Application.Evaluate("=VLOOKUP(" & Target.Address & ",HRDATA,4,FALSE)")
              DistrictName = Application.Evaluate("=VLOOKUP(" & Target.Address & ",HRDATA,5,FALSE)")
             
              MsgBox "Employee Name :  " & EmpName & vbCrLf & _
                      "Job Function      :  " & JobFunction & vbCrLf & _
                      "Team                    :  " & Team & vbCrLf & _
                      "District Name      :  " & DistrictName
            End If
End Sub
 
Back
Top