search for a string in a column on a different worksheet

[vbxingsang86]

Greetings everybody,

i got a problem with a form of my workbook. i stored the text of a shape into the column "p" of a different worksheet. When i try to find this text, it always founds no match. Would be glad if somebody can help me. I know there are several solutions for this topic, but they didnt work for me either.


Private Sub CommandButton1_Click()
Unload workingstatus_neu
Dim rng1 As Range
Dim strSearch As String
strSearch = " " & ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
Set rng1 = Worksheets("Data").Range("P:P").Find(strSearch, , xlValues, xlWhole)
If Not rng1 Is Nothing Then
MsgBox "Find has matched " & strSearch & vbNewLine & "corresponding cell is " & rng1.Offset(0, 1)
Else
MsgBox strSearch & " not found"
End If
End Sub




Many Thanks in advance!

[vanhunk]

@vbxingsang86:
Without going into detail of your code. I would suggest you put your code in a normal module and call it from the private module in the sheet the button is on.
It would help if you upload your file.

Regards,
vanhunk