find worksheet by FindMethod

  • Thread starter Thread starter CG Rosén
  • Start date Start date
C

CG Rosén

Good Day Group,

Trying to use the FindMethod to lookup if a Worksheet is present.
An effort to code as below. Seems I need further advise. Thankkful
for any help.

Brgds

CG Rosén


Dim nme As String
nme = TextBox1.Text & TextBox2.Text
Dim wk As Worksheets

Dim k
With ActiveWorkbook.Worksheets
Set k = .Find(????, LookIn:=????)

If Not k Is Nothing Then

code.....

End If
End With
 
Don't use FInd, it' a wrong tool for the task

I would do this:

Function IsPresent (ws_name as string, wb_name as string) as boolean
Dim ws as worksheet
Dim wb as Workbook

set wb=application.workbooks(wb_name)

IsPresent = false ' assume there is no such ws in this wb

for each ws in wb
if ws.name = ws_name then
isPresent=True
exit function
end if
next ws
end function

To use this function, just type

if IsPresent("MySheet_name", "MyBook_name") then ....

Cheers,

RADO
 
Dim nme As String
nme = TextBox1.Text & TextBox2.Text
Dim wk As Worksheet

On Error Resume Next
set wk = Worksheets(nme")
On Error Goto 0

if not wk is Nothing then
' worksheet exists (wk holds a reference to it)
 
set wk = Worksheets(nme")
shouldn't have the doublequote

set wk = Worksheets(nme)
 
Back
Top