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]

This is how it could work.
1. A separate sheet would be used to enter the designated ID and Message. e.g. ID in column A and message in column B.
2. A Worksheet_Change event macro would then be entered into the sheet code module for the sheet to be populated.
3. The macro would search the separate sheet for the ID number and if found, display a message box with the message from column B of the separate sheet.
4. The separate sheet would require manual control to add and delete the ID and messages.
Here is an exmple of the code, assuming sheets 1 and 2.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or Target = "" Then Exit Sub
Dim fn As Range, msg As String
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Set fn = Sheets(2).Range("A:A").Find(Target.Value, , xlValues, xlWhole) 'Use the actual sheet name instead of index 2.
            If Not fn Is Nothing Then
                msg = fn.Offset(, 1).Value
                MsgBox msg
            End If
    End If
End Sub

The only modification needed is to change Sheets(2) to the actual sheet name, e.g. Sheets("Sheet2"), or whatever it might be.

 

[Matt New]

That's awesome! works exactly how I want it to! thanks a lot

Do you think there would be a way to make that msgbox an input box, where that same ID needs to be entered again to dismiss it? or am I asking for too much?

 

[cedricthecat]

or am I asking for too much?

Probably! (Just kidding!)

Here's a re-working of JLG's code above, which I've taken the liberty of adding to!

This does what you ask, as well as popping the user's name into the title of the form. It's not pretty, and I'd prefer to have done it without jumping back to the top (this will cause a fatal loop if the correct ID is not entered), but just ordered a Chinese and have to stop messing about now. Perhaps it'll help and you can modify it further?

Private Sub Worksheet_Change(ByVal Target As Range)
Comments:
If Target.Cells.Count > 1 Or Target = "" Then Exit Sub
Dim fn As Range, msg As String, UserID As Variant, UserName As String, UserCheck As String
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Set fn = Sheets("Comment Sheet").Range("A:A").Find(Target.Value, , xlValues, xlWhole) 'Use the actual sheet name instead of index 2.
            If Not fn Is Nothing Then
                msg = fn.Offset(, 2).Value
                UserName = fn.Offset(, 1).Value
                UserCheck = fn.Value
                UserID = InputBox(msg & _
                vbNewLine & _
                vbNewLine & "(Enter your ID Number to proceed)", UserName)
                If UserID <> UserCheck Then
                MsgBox "You have entered an incorrect code", vbExclamation, "Check ID Number"
                UserID = ""
                GoTo Comments
                End If
              End If
    End If
End Sub

 

[Matt New]

Cedric, your code has the exact function I am looking for!

However, there is one little hiccup. the message from "Message Sheet" populates in the title of the box, instead of in the text portion. I tried playing around with it, but apparently VBA goes much further over my head than I thought. I just broke it. Could you debug for me?

Thanks again, you guys rock

 

[cedricthecat]

Not sure what's happening as is should have the user's name in the title and the message to them in the body?

Btw, in your example there is no "Message Sheet" - the data called is from "Comment Sheet"

 

[JLGWhiz]

Btw, in your example there is no "Message Sheet" - the data called is from "Comment Sheet

@cedricthecat, see my post # 11.

 

[Matt New]

I tried making sense of it and gave myself a headache

Here is a screenshot of the result from the code provided by Cedric.
https://www.dropbox.com/s/dxmt4vl04fssvi9/popup box issue.png?dl=0

In essence, the message/comment occupies the title bar, the box itself only contains the "(Enter your ID Number to proceed)", without the user's name. From reading the code, it looks like it should work. but as mentioned, I'm below a rookie with these things, and any attempt I've made at moving things around either give an error, or just doesn't change anything.

 

[Matt New]

I tried changing the code a little bit, looks like this

Private Sub Worksheet_Change(ByVal Target As Range)Comments:
If Target.Cells.Count > 1 Or Target = "" Then Exit Sub
Dim fn As Range, msg As String, UserID As Variant, UserName As String, UserCheck As String
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Set fn = Sheets("Comment Sheet").Range("A:A").Find(Target.Value, , xlValues, xlWhole) 'Use the actual sheet name instead of index 2.
            If Not fn Is Nothing Then
                msg = fn.Offset(, 2).Value
                UserName = fn.Offset(, 1).Value
                UserCheck = fn.Value
                UserID = InputBox(msg)
                If UserID <> UserCheck Then
                MsgBox "You have entered an incorrect code", vbExclamation, "Check ID Number"
                UserID = ""
                GoTo Comments
                End If
              End If
    End If
End Sub

From what I understand, this should cause what ever comment is in place to populate in the box, with no other text. However, this does not happen. The Input Box pops up, but with no text or title. It does require the correct ID to clear out. And, as originally expected, any ID not stored in the 'Comment Sheet' does not trigger an input box.

I'm wondering if this is because of the variable type, or if the text needs to have specific formatting?

 

[cedricthecat]

@cedricthecat, see my post # 11.

Indeed! Matt referred to "Message Sheet" whereas you correctly advised to change the index to the sheet name! In his sample sheet however, this sheet is called "Comment Sheet"!

 

[cedricthecat]

Matt, your Input Box should look like this.

See here

I'll upload a working copy of your original, so you can see it exactly as I've done it!