Macro Won't Run From Ribbon

[BrianExcel]

Hello -

I created a macro that works perfectly from a button inserted onto the same sheet where the code will run.

This is the macro:

Sub SISearch(control As IRibbonControl)Dim r As Range, rAll As Range
Dim sTerm As String
    
    sTerm = Application.InputBox(Prompt:="Enter the text you wish to search for.", Title:="InputBox Method", Type:=2)
    Set rAll = Application.InputBox(Prompt:="Select a Range", Title:="InputBox Method", Type:=8)
 
    If rAll Is Nothing Then
        MsgBox "No Range Selected"
    Else
        For Each r In rAll
            If InStr(UCase(r), UCase(sTerm)) Then
                r.Offset(0, 1) = "Text located"
            End If
        Next r
    End If
End Sub

When I try and run this same macro from the Ribbon, I get the following error message:

"Cannot run the macro 'SISearch'. The macro may not be available in this workbook or all macros may be disabled.

I can't understand why it won't run. I have confirmed macros are enabled, and if I try and run other commands such as opening a userform from the ribbon, it works fine - it's only this one.

Can anyone advise why it won't work?

 

[RoryA]

Which module is that code in and how did you add the button to the ribbon?

 

[BrianExcel]

Dear God,

Why do I take up space on these forums with questions that have simple answers?

Rory,

Thanks so much. Ironically enough, your question game me the answer. I had the code u a worksheet. As soon as I modified with a module, it worked.

THanks so much.

One final question though - since I'm using application.inputbox instead of just inputbox, where and how do I program it so if the user selects "Cancel" it exits the sub? Right now if I try and select cancel it gives an error message.