Good Search Code But Help Wanted to Fix one error

[t01te6]

Hey

Here is a good search code, that I have picked up, the problem is that when other colleagues press on column A by mistake it brings up an error which opens Visual Basic, I cant think why it is doing this if you press on column A at any point before of after a search.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Found As Range, ws As Worksheet
Dim sht As String, oAd As String
If Not Target.Column = 1 Then Exit Sub
Set Found = Target.Find(What:="$")
   If Found Is Nothing Then
    MsgBox "Invalid Address", vbExclamation
Else
    sht = Split(Target.Value, " ")(0)
        oAd = Split(Target.Value, " ")(1)
            Sheets(sht).Select
                ActiveSheet.Range(oAd).Select
    End If
End Sub

My users are not great excel users and are getting confused when VB opens.

Does anyone know how I can stop this?

Thanks

 

[Richard Schollar]

When I have access to xl97 (tonight at the earliest) I will try the split code and ensure that it works as expected (or what might be wrong if not). i'll report back tomorrow.

 

[Richard Schollar]

OK, try using this code and retrying (VBA5 definitely doesn't like the Replace function - maybe I should tell xld his function needs work?)

#If VBA6 Then
#Else
'-----------------------------¬¬-----------------------------¬-¬------
Function Split(Text As String, _
        Optional Delimiter As String = " ") As Variant
'-----------------------------¬¬-----------------------------¬-¬------
Dim i As Long
Dim sFormula As String
Dim aryEval
Dim aryValues
    sFormula = "{""" & Application.Substitute(Text, Delimiter, """,""") & _
        """}"
    aryEval = Evaluate(sFormula)
    ReDim aryValues(0 To UBound(aryEval) - 1)
    For i = 0 To UBound(aryValues)
            aryValues(i) = aryEval(i + 1)
    Next
    Split = aryValues
End Function
#End If