Search Function

[pzamory]

Good morning.

I am able to copy/paste this search function (see below) into a module. I assign a Search "button" to access this module and it seems to work just fine. My aim is to be able to search for text throughout multiple workbooks in an Excel sheet.

The problem is that when I exit out of the Excel sheet (Book1) and open it up again, I get an error message and the search function no longer works. Looking for any direction and/or guidance. Thanks very much.

Error message: Cannot run the macro 'Book1.xlsm!Findmesheet! - The macro may not be available in this workbook or all macros may be disabled

Option Explicit

Sub FindMeSheet()

Dim CheckNum$
Dim varSheets As Variant
Dim i As Long
Dim c As Range

    CheckNum = InputBox("Enter your word/number.", "Sheet (??) Chaser")
    If CheckNum = vbNullString Then Exit Sub

varSheets = Array("Sheet2", "Sheet3", "Sheet4")

For i = LBound(varSheets) To UBound(varSheets)
   With Sheets(varSheets(i))
   
      Set c = .UsedRange.Find(What:=CheckNum, LookIn:=xlValues)
      
      If Not c Is Nothing Then
         MsgBox c.Address & " On " & Sheets(varSheets(i)).Name
      End If
      
   End With
Next
End Sub

 

[RoryA]

A few questions:

Is the workbook called Book1.xlsm?
Did you enable macros?
Which module is the code in?

 

[pzamory]

Module Name: Findmesheet
Workbook is called Book1.xlsm
I saved the file as "macro enabled". Is there another step that I am missing for enabling macros?

Thanks Rory