Messagebox after userform textbox match

[basbergie]

Good afternoon,

I'm looking for a vba code that based on a value of Textbox4 in a userform, looks for a match on Sheet2 Column A and then returns a messagebox with a comment that is given next to it on Column B.

If there is no comment, he does not have to give a message box

 

[DanteAmor]

Hi and welcome to MrExcel

Try this:

Private Sub CommandButton1_Click()
  Dim f As Range
  
  Set f = Sheets("Sheet2").Range("A:A").Find(TextBox4.Value, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    If Not f.Offset(, 1).Comment Is Nothing Then
      MsgBox "Comment: " & f.Offset(, 1).Comment.Text
    End If
  End If
End Sub

 

[basbergie]

I tried that code but when i push the button nothing happens.

 

[basbergie]

I tried also:

Private Sub CommandButton4_Click()
Dim F As Range
  
  Set F = Sheets("Sheet2").Range("A:A").Find(TextBox4.Value, , xlValues, xlWhole, , , False)
  If Not F Is Nothing Then
    If Not F.Offset(, 1).Comment Is Nothing Then
      MsgBox "test"
    End If
  End If
End Sub

That doesn't work either so it has nothing to do with the message section

 

[basbergie]

maybe it could be much simpler. but I have now managed to select the correct cell based on the textbox, but now I want the selected cell to appear as messagebox.
What code should I use for that?

Private Sub CommandButton4_Click()
Dim ws As Worksheet
Dim Rng As Range
Set ws = Sheets("Sheet2")   'Change the sheet name as per requirement
Set Rng = ws.Range("A:A").Find(Me.Textbox4.Value)
If Not Rng Is Nothing Then
    ws.Activate: 'this line makes sure that you are on the right sheet
    Rng.Select
    ActiveCell.Offset(0, 1).Select

    
    End If
End Sub

 

[basbergie]

ow wow that was simpler then i thought, it was:
MsgBox ActiveCell.Value
but now i need a code when theactive cell is empty then i dont need the messagebox

 

[DanteAmor]

Go to the sheet, activate the cell, activate the other cell, it does not seem to me to be simpler.

returns a messagebox with a comment

What do you mean by "comment", a "some text" in the cell or the comments that you can insert in the box comment:

If you mean some text, and I think it is, then use the following:

Private Sub CommandButton1_Click()
  Dim f As Range
  Set f = Sheets("Sheet2").Range("A:A").Find(TextBox4.Value, , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    If f.Offset(0, 1) <> "" Then MsgBox "test"
  End If
End Sub

Note:
Set f = Sheets("Sheet2").Range("A:A").Find(TextBox4.Value, , xlValues, xlWhole, , , False)
xlWhole in Find method, it means match must be exact, if you want partial search then change to xlPart