if sheet there delete it, else.. did I do it right ?

R

RompStar

Well-known Member
Joined
Mar 25, 2005
Messages
1,200
basically I want to check if a sheet existed, and if not it would goto a different part in the procedure... But currently I get an error, so I am thinking = True is not used right...

But basically I wanted to say, if Sheet ("ALL_test") exists, delete it, if not go to the import procedure..

any ideas ?


If Sheets("ALL_test") = True Then
Sheets("ALL_test").Select ' select the sheet if it exists
ActiveWindow.SelectedSheets.Delete ' and delete the sheet
Else
GoTo StartImport
End If
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
If this is all there is to what you're doing, you don't really need to determine if the sheet exists or not (because you don't need to select it to delete it.)
You should be able to use something like:
Code: 
On Error Resume Next
Sheets("ALL_test").Delete
Err.Clear
GoTo StartImport
Dan
 
Upvote 0
hmmm, good tricks :- )

but just for learning sake, would it have been possible to do it like I was trying to ?

I guess it takes time to learn all this :>- ) I find Excel VBA Programming for Dummies series a great book, all of the complicated things, don't seem so complicated after a while, learning arrays..

Excel supports arrays up to 60 dimensions ?

how the heck do you visulize that ? :eek:
 
Upvote 0
Hi RompStar,

One way to adopt your approach would be to create a UDF that returns a boolean indicator of whether the sheets exists.

Like this:
Code: 
Sub Test()
    If SheetExists("ALL_test") = True Then
        Application.DisplayAlerts = False
        Sheets("ALL_test").Delete
        Application.DisplayAlerts = True
    Else
        'something else : GoTo StartImport
    End If
End Sub

Function SheetExists(strWSName As String, Optional wbk As Workbook) As Boolean
    Dim ws As Worksheet
    If wbk Is Nothing Then Set wbk = ActiveWorkbook
    On Error Resume Next
    Set ws = wbk.Worksheets(strWSName)
    On Error GoTo 0
    If Not ws Is Nothing Then
        SheetExists = True
    Else
        SheetExists = False
    End If
End Function
HTH
 
Upvote 0
but just for learning sake, would it have been possible to do it like I was trying to ?
Click to expand...
Yes, thanks to a function that Nimrod wrote the method you were trying to use is possible. If you put this in the same module as your existing sub:
Code: 
Private Function SheetExists(sname) As Boolean
    Dim x As Object
    On Error Resume Next
    Set x = ActiveWorkbook.Sheets(sname)
    If Err = 0 Then SheetExists = True _
        Else SheetExists = False
End Function
Then you could give a minor tweak to your code and make it work.
Code: 
If SheetExists("ALL_test") Then
    Sheets("ALL_test").Select
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
  Else
    GoTo StartImport
End If
Prett slick, huh? :cool:

Hope it helps,
Dan
 
Upvote 0
cool, thanks guys, I post into both web site, they are both so good.. Thanks.

Yes, pretty slik that's for sure, I like VBA :- ) I read books everyday, it attracks my attention, it's pretty cool..

Once I get good at VBA for Excel, I will try VBA for Access and then VB by it's self.. Thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JohnPoole
  • Solved
Delete rows based on unique values
2
Replies
17
Views
916
kevin9999
K
P
  • Question
Consolodate data to matching columns
Replies
0
Views
308
PippaThePointer
P
S
  • Solved
Insert picture JPG in footer
Replies
6
Views
288
sm789
S
B
  • Solved
Delete Named Ranges That Referring to Another Workbook
Replies
4
Views
977
kevin9999
K
B
  • Question
Delete sheets on password expiry
Replies
3
Views
357
bptaw
B

Forum statistics

Threads
1,215,647
Messages
6,126,005
Members
449,279
Latest member
Faraz5023

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top