vba - check worksheet existence

[davidhasselhoff]

Hello!

I got a listbox which chooses from which worksheet a value should be copied.

Now If there is a value in the listbox, where there is no corresponding worksheet, I get an "out of range" error.

What I need is a way to check if there is a worksheet called as the choice the user makes to produce an error/cancel/go back message if there isn't.

Here is the code i use:

Cells(11, 3) = ThisWorkbook.Worksheets(UserFormsea.ListBox3.Text).Range("b29")

Any help would be greatly appreciated!

 

[Jonmo1]

Here's How I do it

Dim MySheet as WorkSheet
On Error Resume Next
Set MySheet = ThisWorkbook.Worksheets(UserFormsea.ListBox3.Text)
On Error Goto 0
If MySheet is Nothing Then
    Msgbox "Sheet Doesn't exist"
    Exit Sub
End If
Cells(11, 3) = MySheet.Range("b29")

Hope that helps...

 

[davidhasselhoff]

Thank you very much, I think this will do the trick!