VBA - InputBox to select a cell but also store the worksheet as a variable

[gmcgough600]

Hi,

I've got the following code which works but I need a way to also store which workbook the selected cell was on:

find_cell = Application.InputBox("Select first cell in list of data to find", Default:=Selection.Address, Type:=8).Address

I'm working across multiple sheets (and possibly workbooks), but this code only works if everything is on the same sheet.

Any suggestions? Thanks in advance.

 

[ranman256]

paste code into a module then run: FindMyWord

Sub FindMyWord()
Dim vRet
Dim ws As Worksheet

On Error GoTo errFind   'Resume Next

vRet = InputBox("Enter Find Word")
If vRet = "" Then Exit Sub

For Each ws In Sheets
   ws.Activate
  
       Cells.find(What:=vRet, After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

  MsgBox "found in " & ws.name & ":" & activecell.address
  Exit For
 
resumeFind:
Next
Set ws = Nothing
Exit Sub

errFind:
Resume resumeFind
End Sub

 

[gmcgough600]

Thanks but I'm not trying to find anything, I want the user to manually select a cell.