MsgBox from a range of values

[Matt New]

Hi everyone! First timer on the boards, please don't bite

I have a spreadsheet I'm using for "sign-in" function, using a barcode scanner. I'm not worried about that part, I have it setup and ready to go.

However, I'd like to have a sheet I can use to have pop-up messages when specific users sign-in. Ie, user 10001 logs in, a specific message pops up. User 10002 logs in, no message. the next day, user 10001 may not have a message, but 10002 does.

I'd like to be able to have a table where I (or another admin, non-excel-person) can essentially enter IDs and the appropriate message on a spreadsheet, and have the code reference that sheet. Is that possible?

I don't have any real VBA experience, just borrowing code where I find them to fit my needs. Let me know if you need more details on how the sheet is setup!

 

[JLGWhiz]

Let's say you name a sheet "Control" and set up a table starting on row 2 where you would enter the employee code, and on row 3 you would list the sheet name assigned for that employee, and in row 4 you would enter text for any comments. This version would only allow one sheet and one comment per user. I don't know how many employees might be involved but lets limit the example to 3 sheets. So range A2:C4 will be our table range.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Range("A2:C2"), Target) Is Nothing Then
        MsgBox "Employee " & Target.Value & " Assignment is Sheets(""" & Target(2) & """).  " & Target(3).Value
    End If
End Sub

this is worksheet event code and should be copied to the worksheet code module for the sheet which the employee code number will be entered into. This should give you an idea of how your objective could be accomplished.

 

[Matt New]

sorry for the delay - been crazy 'round here and didn't get a chance to log back in.

that unfortunately won't work for me - I have 130+ employees.

Essentially, Sheet 1 has a running log. That sheet will be displayed. When an employee scans in, the ID tag number is entered into column A (starting at A2). columns 3-5 auto-update based on the ID tag scanned. So, A2 would have the ID number, A3 their title, A4 the name, and A5 the time. The scanner then automatically hits "enter" from cell A2, sending it to A3, and the cycle repeats when the next employee signs in.

What I'd like to do is have a sheet, say Sheet 2, with a list of Employee IDs in column A and a comment in column B. I'm hoping I can have excel automatically search the ID scanned, look in Sheet 2 for that ID and display the message next to it. To dismiss the message, the employee would scan their ID again.

thanks!

 

[Matt New]

sorry for the delay - been crazy 'round here and didn't get a chance to log back in.
A2 would have the ID number, A3 their title, A4 the name, and A5 the time. The scanner then automatically hits "enter" from cell A2, sending it to A3, and the cycle repeats when the next employee signs in.

I'm an idiot - probably confused people trying to help me... A2 shows the ID number, B2 the title, C2 the name, and D2 the time stamp. Then it moves to A3, and repeats on the next scan.

 

[JLGWhiz]

Obviously, Column A is the column for Employee ID. To get a message pop-up for a specific employee on a selective basis, you would need to use event code and the code would need an algorithm that allows it to discern which employees have messasges and then initiate a method for displaying the message. The message part of your plan is vague because it does not state if the message content will be random information or a fixed statement for each employee. So it is difficult to visualize how the message would be entered and stored in a fashion that is readily retrievable by VBA for display to the employee. Also, not having worked with scanners personally, I am not sure that a scanned entry will even trigger the change event in Excel which would be the recommended way to run a macro. But the concept seems feasible enough. It would just be a matter of developing the algorithm to isolate the employee to a message which would need to be entered by someone into an area of the file that can be retrieved and displayed.

 

[Matt New]

to retrieve the info, I'd simply put the employee code in column A and the message in column B.

in terms of triggering the event - can a macro monitor a column and every time a new entry is added, use that new entry to cross-check on another sheet?

 

[JLGWhiz]

to retrieve the info, I'd simply put the employee code in column A and the message in column B.

in terms of triggering the event - can a macro monitor a column and every time a new entry is added, use that new entry to cross-check on another sheet?

If the database is logically organized within a file, the code can manipulate within a sheet, between sheets and between workbooks to add, delete, modify, retrieve and calculate data. But the code writer has to understand the logical arrangement of the files and the user's objectives to make those things happen. Contrary to popular opinion, computers are pretty dumb. They just do what they are told to do very quickly so long as they are given logical instructions.

 

[Matt New]

ok, so what information would I need to provide to have someone on here help me write the code? as mentioned, I pretty much don't know VBA at all, so I'm essentially looking for someone willing to write it for me and explain it to me - so I can learn from it and modify as needed.

 

[JLGWhiz]

ok, so what information would I need to provide to have someone on here help me write the code? as mentioned, I pretty much don't know VBA at all, so I'm essentially looking for someone willing to write it for me and explain it to me - so I can learn from it and modify as needed.

It helps to post a screen shot of your work sheet(s) or a link to a share drive where your file can be reviewed, along with a clear explanation of what you want to do. Remember, the people who respond here are not necessarily consultants, but will assist with code debugging and formula problems. It is expected that the bulk of the work be done by those seeking assistance.

 

[Matt New]

you know, that would make sense. here is the file
https://www.dropbox.com/s/f8sfimmih...eet (work in progress - dont touch).xlsx?dl=0

you can ignore the "work in progress - dont touch" part. that is a copy of the original, feel free to try a few things.

entering a user ID in the first sheet, in A2 will populate the info that I need. I could (very) easily make it do what I want with a VLOOKUP function, but I want the information to pop up on the screen, not have to depend on people watching for their name and looking in another cell for the information.

thanks again