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
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
Code:
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
Last edited by a moderator: