vba/code to bring up msg box when critaria is met that contains data from comment box

what I have is a VERY large members data base on one sheet.. on another sheet(or sign in sheet) I use the Vlookup to pull data from the members list via scan-able cards (like the ones you see at supermarket key chains) when data is populated in the cells (on the sign in sheet), sometimes a msg pops up in the H column saying "see not on MM list"
what normally happens is my staff has to go to the members data base find the member (or members number) and find the note..
what i would like to happen is when the "see note on MM list" appears, a Message Box pops up and delivers the data from the note that is on the members data base.
the notes are always on the A,B or C column of the data base. and the "see note on MM list" always is in the H column of the sign in sheet.
is this possible.
thanks in advance
Jason

Here you go.. thanks for taking a look..

yes the note is in the combined members list.. in the example that i uploded i used member 16601 which was entered on the A column on the "blank sign in sheet".. then in columns B thru G populates the members info.. AUSTIN , JESSICA, (START DATE) (HIS DOB) (HER DOB) ... then on the H column a message is produced "see note on mm list".. at this point i have to go to the "combined members list". search my list for "16601" and manually find the note which is in the B column.. i would like a pop message box with what the note has in it corrisponding to the member to pop up when the words "see note on mm list" pops up in the H column.. in this example.. the note says "return 110.00" so the pop up should read "return 110.00" i can click OK and get rid of the pop up box..
hope this helps.. sorry if i wasn't clear..

Im confused. i put this into the sign in sheet.. and nothing happened .. is there a special way of inserting this.. ??

Originally Posted by xladept

Hi,

This combination pasted into the Sign In Sheet module will accomplish much of what you want:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 8 Then
Dim wc As Worksheet: Set wc = ActiveWorkbook.Worksheets("COMBINED MEMBERS")
Dim WF As WorksheetFunction: Set WF = WorksheetFunction
MsgBox (GetComment(WF.Match(Range("A" & Target.Row), wc.Range("A:A"), 0)))
SendKeys ("~"): End If: End Sub


Function GetComment(r As Long) As String: Dim C As Range, T As Comment 'XWarlock
Dim wc As Worksheet, ws As Worksheet, S As String
Set wc = ActiveWorkbook.Worksheets("COMBINED MEMBERS")
Set ws = ActiveWorkbook.Worksheets("BLANK SIGN IN SHEET")
For Each C In wc.Range("A" & r & ":C" & r)
If Not C.Comment Is Nothing Then
Set T = C.Comment
S = T.Text: S = Mid(S, InStr(1, S, ":") + 1, Len(S))
S = Right(S, Len(S) - 1): S = Left(S, Len(S) - 1)
Exit For: End If: Next
GetComment = Trim(S): End Function

I get a "compile error" "expected end function".. Pop up

i got it work .. thanks soooo much..

no. dummie me .. when i copied and paste.. i left out a small section.. so it wasn't working.. lol.. thats what i get for not paying attention.. lol