Delete Excel.Worksheet without stupid prompt?

[Dave Sell]

Hi,

Lookin to delete sheets in the Excel.Workbook without the user being prompted fro EVERY sheet I delete in code.

ie..

Dim sheet As Excel.Worksheet
    Set sheet = New Excel.Worksheet
    sheet.Delete ' <-- This always prompts the user!! ARGH!

Dave

[si_the_geek]

Dim sheet As Excel.Worksheet
    Set sheet = New Excel.Worksheet
    Application.DisplayAlerts = False
    sheet.Delete 
    Application.DisplayAlerts = True

[Dave Sell]

A) How the heck did you know that?

B) WOO-HOO!!!!!!!

[Ecniv]

dim blnAlertStatus as boolean
 
'---- Turn off alerts
    blnAlertStatus = Application.DisplayAlerts
    Application.DisplayAlerts = false
 
'---- do processing
 
'---- Turn on alerts
    Application.DisplayAlerts = blnDisplayAlerts

There is another to freeze the sheet display, can't find it tho.


Vince

[si_the_geek]

That would be:

Application.ScreenUpdating = False
'do stuff
Application.ScreenUpdating = True

It is a bit strange when you arent showing something else (and Excel is visible), as it looks like Excel has crashed. If your code crashes before you reset it then Excel can stay like it the next time it is opened too

[svibuk]

i am having total of 4 sheets of which sheet 1 & sheet 4 r compulsory
i am using datatable to fill the sheet with data
if dt1.rows.count<>0
-- write data to excel cells
else
oWB1.Application.DisplayAlerts = False
oWB1.Sheets("ABC").Delete
oWB1.Application.DisplayAlerts = True
end if

if dt2.rows.count<>0
-- write data to excel cells
else
oWB1.Application.DisplayAlerts = False
oWB1.Sheets("XYZ").Delete
oWB1.Application.DisplayAlerts = True
end if

i get error when it goes to else part of dt1 ie on the line
oWB1.Sheets("ABC").Delete

but if dt2 count is 0 the else part gets executed sucessfully ie the sheet 3 gets deleted

[vbfbryce]

what is the error at that line?

[svibuk]

what is the error at that line?

error
Public member 'Worksheets' on type 'Worksheet' not found

[vbfbryce]

Does worksheet "ABC" exist in that workbook?

[svibuk]

yes it does

[vbfbryce]

Can you post the whole code, including the variable definitions?