Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
WHY THIS CLOSES EXCEL IF THERE´S NO SHEET NAMED AS...???
Hi, i´ve got a problem with this command button.
It deletes sheets named Tarea+nº and Acción+nº and works perfect when these sheets already exist, but once i´ve deleted them i-ve got an error when pussing again the button and excel gets closed asking me if i want to send the error report. I´ve already tried writing on error resume next everywhere (i´m a beginner and don´t know the properly use of this function) ANY IDEA??? THANKS A LOT!!!! Private Sub CommandButton12_Click() Dim wksH As Worksheet Dim mtr(), n For Each wksH In Worksheets If Left(wksH.Name, 6) = "Acción" Then n = n + 1 ReDim Preserve mtr(1 To n) mtr(n) = wksH.Name End If Next Worksheets(mtr()).Select Set wksH = Nothing For Each wksH In Worksheets If Left(wksH.Name, 5) = "Tarea" Then n = n + 1 ReDim Preserve mtr(1 To n) mtr(n) = wksH.Name End If Next Worksheets(mtr()).Select Set wksH = Nothing ActiveWindow.SelectedSheets.Delete On Error Resume Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
WHY THIS CLOSES EXCEL IF THERE´S NO SHEET NAMED AS...???
The problem seems to be that if you build an array of sheet names, and some
do not exist, none get selected. Just change the code to delete them as you go Private Sub CommandButton12_Click() On Error Resume Next Application.DisplayAlerts = False For Each wksH In Worksheets If Left(wksH.Name, 6) = "Acción" Or _ Left(wksH.Name, 5) = "Tarea" Then wksH.Delete End If Next Application.DisplayAlerts = True On Error GoTo 0 Set wksH = Nothing End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "cbm" wrote in message ups.com... Hi, i´ve got a problem with this command button. It deletes sheets named Tarea+nº and Acción+nº and works perfect when these sheets already exist, but once i´ve deleted them i-ve got an error when pussing again the button and excel gets closed asking me if i want to send the error report. I´ve already tried writing on error resume next everywhere (i´m a beginner and don´t know the properly use of this function) ANY IDEA??? THANKS A LOT!!!! Private Sub CommandButton12_Click() Dim wksH As Worksheet Dim mtr(), n For Each wksH In Worksheets If Left(wksH.Name, 6) = "Acción" Then n = n + 1 ReDim Preserve mtr(1 To n) mtr(n) = wksH.Name End If Next Worksheets(mtr()).Select Set wksH = Nothing For Each wksH In Worksheets If Left(wksH.Name, 5) = "Tarea" Then n = n + 1 ReDim Preserve mtr(1 To n) mtr(n) = wksH.Name End If Next Worksheets(mtr()).Select Set wksH = Nothing ActiveWindow.SelectedSheets.Delete On Error Resume Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
WHY THIS CLOSES EXCEL IF THERE´S NO SHEET NAMED AS...???
THAT WORKED GREAT!!!
Thanks a lot Bob, nice job Bob Phillips ha escrito: The problem seems to be that if you build an array of sheet names, and some do not exist, none get selected. Just change the code to delete them as you go Private Sub CommandButton12_Click() On Error Resume Next Application.DisplayAlerts = False For Each wksH In Worksheets If Left(wksH.Name, 6) = "Acción" Or _ Left(wksH.Name, 5) = "Tarea" Then wksH.Delete End If Next Application.DisplayAlerts = True On Error GoTo 0 Set wksH = Nothing End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "cbm" wrote in message ups.com... Hi, i´ve got a problem with this command button. It deletes sheets named Tarea+nº and Acción+nº and works perfect when these sheets already exist, but once i´ve deleted them i-ve got an error when pussing again the button and excel gets closed asking me if i want to send the error report. I´ve already tried writing on error resume next everywhere (i´m a beginner and don´t know the properly use of this function) ANY IDEA??? THANKS A LOT!!!! Private Sub CommandButton12_Click() Dim wksH As Worksheet Dim mtr(), n For Each wksH In Worksheets If Left(wksH.Name, 6) = "Acción" Then n = n + 1 ReDim Preserve mtr(1 To n) mtr(n) = wksH.Name End If Next Worksheets(mtr()).Select Set wksH = Nothing For Each wksH In Worksheets If Left(wksH.Name, 5) = "Tarea" Then n = n + 1 ReDim Preserve mtr(1 To n) mtr(n) = wksH.Name End If Next Worksheets(mtr()).Select Set wksH = Nothing ActiveWindow.SelectedSheets.Delete On Error Resume Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 SP3 Copy Worksheet with another sheet named range now # | Excel Discussion (Misc queries) | |||
Excel 2007 closes when e-mailing sheet | Excel Discussion (Misc queries) | |||
when i insert word art in excel sheet excel file closes automatica | Excel Discussion (Misc queries) | |||
ADO - recordset - closed excel workbook - know sheet name and cell name but no named ranges defined | Excel Programming | |||
Enabling Add-In closes current sheet | Excel Programming |