How to add OnError vba on my current subroutine?

[eshnider21]

I tried adding OnError on my current sub. I success adding it but it always shows the Error message even there is no error. How to add the OnError correctly?


Public Sub Searcher()
Dim wss As Integer
wss = Application.InputBox("Please enter Sheet Number", "Sheet Select", Type:=1)
With ThisWorkbook.Worksheets(wss)
.Select
.Activate
End With
End Sub

 

[dmt32]

Hi welcome to forum

try

Public Sub Searcher()
    Dim wss         As Variant
    
    On Error GoTo myerror
    wss = Application.InputBox("Please enter Sheet Number", "Sheet Select", Type:=1)
    'cancel pressed
    If VarType(wss) = vbBoolean Then Exit Sub
    
    'your code
    
myerror:
If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

when using an InputBox, your code should include handling user pressing Cancel button

Dave

 

[Joe4]

Welcome to the Board!

If I enter a valid worksheet number, it works fine for me, without any errors.
What exactly are you entering in?

Realize that code is going by the the worksheet index number, not the name.

 

[eshnider21]

Welcome to the Board!

If I enter a valid worksheet number, it works fine for me, without any errors.
What exactly are you entering in?

Realize that code is going by the the worksheet index number, not the name.

Actually the macro is fine. I was just avoiding users to mishandle the sub so I was trying to put OnError on it.

Thanks for greetings by the way.

 

[Joe4]

OK, but I thought you said:

it but it always shows the Error message even there is no error.

but I cannot create that scenario.

Is that really an issue, or no?

 

[eshnider21]

Actually the macro is fine. I was just avoiding users to mishandle the sub so I was trying to put OnError on it.

Thanks for greetings by the way.

I get

Welcome to the Board!

If I enter a valid worksheet number, it works fine for me, without any errors.
What exactly are you entering in?

Realize that code is going by the the worksheet index number, not the name.

I get it now. Now I know why the message still pops up even there is no error..I forgot to exit sub.

I am still confused using the right variables for my DIM. Originally my dim fof wss is Integer. and never used Boolean

 

[eshnider21]

OK, but I thought you said:

but I cannot create that scenario.

Is that really an issue, or no?

It is working fine now.Thank you..

 

[eshnider21]

I think I just put my OnError code on the wrong lines.

 

[Joe4]

I get it now. Now I know why the message still pops up even there is no error..I forgot to exit sub.

There isn't any error handling code or messages in your original post, which is why I am so confused.
Maybe you had it in at one time, but didn't include it in your original post?

 

[eshnider21]

There isn't any error handling code or messages in your original post, which is why I am so confused.
Maybe you had it in at one time, but didn't include it in your original post?

Yes I removed it after my trial and error. I was so frustrated I removed the OnError before I posted this on the forums. But anyways it is complete now.Thank you very much


'Public Sub Searcher()
Public Sub Searcher()
Dim wss As Variant
On Error GoTo myerror
wss = Application.InputBox("Please enter Sheet Number", "Sheet Select", Type:=1)
'cancel pressed
If VarType(wss) = vbBoolean Then Exit Sub
'your code
With ThisWorkbook.Worksheets(wss)
.Select
.Activate
End With
Call DeleteRows

myerror:
If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Sub DeleteRows()
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
DeleteStr = Application.InputBox("Delete Text", xTitleId, Type:=2)
For Each rng In InputRng
If rng.Value = DeleteStr Then
If DeleteRng Is Nothing Then
Set DeleteRng = rng
Else
Set DeleteRng = Application.Union(DeleteRng, rng)
End If
End If
Next
DeleteRng.EntireRow.Delete
End Sub