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

 

[t01te6]

Hey Sorry,

I figures out that the code I posted is the hyperlink part of the code

 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

There is no problem if run on a later Excel but on Excel 97 the line sht = Split(Taryget.Value, " ") (0) lights up as a problem, please does anyone know why.

Thanks

 

[Richard Schollar]

Hi

There's no native Split function in Excel97 (which uses VBA5) - you need VBA6 in xl2000 and later.

 

[t01te6]

Oh, That would be the problem them, ok thank you.

Out of interest is there anything that can be done to change the code to allow for this so it can work in Excel 97. I dont imagine my users plan to upgrade anytime soon.

Anybody else who lands on this when being searched later the search code I am using is -

Sub QuickSearch()
Dim c As Range, firstaddress As String, Data, wksht As Worksheet
Dim y As Integer
Sheets("Search").Columns(1).ClearContents        '
Data = InputBox("Get Data {Enter Data to Find)")
For Each wksht In ActiveWorkbook.Worksheets
If wksht.Name <> "Search" Then
With Worksheets(wksht.Name).UsedRange
    Set c = .Find(Data, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstaddress = c.Address
        y = y + 1
        Sheets("Search").Cells(y, "A").Value = _
            wksht.Name & " " & c.Address
        Do
           Set c = .FindNext(c)
              If c.Address <> firstaddress Then
                 y = y + 1
                   Sheets("Search").Cells(y, "A").Value = _
                      wksht.Name & " " & c.Address
             End If
 Loop While Not c Is Nothing And c.Address <> firstaddress  '----
    End If
End With
End If
Next wksht
End Sub

Thanks

 

[Richard Schollar]

MS Excel MVP Bob Phillips (aka Xld on this site) has written a useful UDF Split function and in the following post shows how to use Conditional Compilation to ensure it won't interfere with the native VBA function of VB6:

http://www.mrexcel.com/forum/showpost.php?p=1176798&postcount=23

 

[t01te6]

Thank you I really appreciate the help but I am nowhere near at the level of being able to understand where I am ment to put that in the code to allow it to work.

Would you possibly be able to look at it?

Thanks

 

[Richard Schollar]

Just copy all of it in that post into a standard module in the workbook containing your current code (include the #IF ... #END IF bits too). That's it! Your code will now function under xl97 (the UDF replaces the Split() function built into VBA6 only when the workbook is opened in xl97 - otherwise it does nothing). The replacement Split takes exactly the same arguments as the native VBA one, so you literally do not need to change anything else.

 

[t01te6]

last Question Promise

I did all that but it keeps coming up Sub or Function not defined and highlighting the bit

Function Split(Text As String, _ 
        Optional Delimiter As String = ",") As Variant

Also this line which has the word Replace highlighted in Blue

  Text = Replace(Text, vbNullChar, Delimiter)

Any Help with how to change this?

Thanks

 

[Richard Schollar]

Apologies i missed your reply.

Within a Standard module (not a worksheet/Thisworkbook module) did you paste exactly the following:

#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
    If Delimiter = vbNullChar Then
        Delimiter = Chr(7)
        Text = Replace(Text, vbNullChar, Delimiter)
    End If
    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

This is all you should need.

 

[t01te6]

I promise that is exactly what I did, opend VBA insert new module and paste, it goes to it but then highlights the lines I have said above, it highlights in yellow the function split bit and then highlights the replace word.

Emmm. I cant think of anyting else I may be doing wrong.

Thanks for your help