| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
error 400...
I get an error Visual Basic "400" message when this code is ran:
Sub DeleteSheets() Dim wks As Worksheet Application.DisplayAlerts = False For Each wks In Worksheets If InStr("Bill (", wks.Name) 0 Then wks.Delete Next wks Application.DisplayAlerts = True End Sub How can I fix this? Thank you, Thomas |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error 400...
My fault on your last thread... I just posted the response...
Sub DeleteSheets() Dim wks As Worksheet On Error GoTo ErrorHandler Application.DisplayAlerts = False For Each wks In Worksheets If InStr(UCase(wks.Name), "BILL") 0 Then wks.Delete Next wks ErrorHandler: Application.DisplayAlerts = True End Sub -- HTH... Jim Thomlinson "thomas" wrote: I get an error Visual Basic "400" message when this code is ran: Sub DeleteSheets() Dim wks As Worksheet Application.DisplayAlerts = False For Each wks In Worksheets If InStr("Bill (", wks.Name) 0 Then wks.Delete Next wks Application.DisplayAlerts = True End Sub How can I fix this? Thank you, Thomas |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error 400...
I must be doing something wrong because the code has no response. Perhaps
it's how I call the DeleteSheets(). Can you give me an example of how to call it. Thank you, Thomas "Jim Thomlinson" wrote: My fault on your last thread... I just posted the response... Sub DeleteSheets() Dim wks As Worksheet On Error GoTo ErrorHandler Application.DisplayAlerts = False For Each wks In Worksheets If InStr(UCase(wks.Name), "BILL") 0 Then wks.Delete Next wks ErrorHandler: Application.DisplayAlerts = True End Sub -- HTH... Jim Thomlinson "thomas" wrote: I get an error Visual Basic "400" message when this code is ran: Sub DeleteSheets() Dim wks As Worksheet Application.DisplayAlerts = False For Each wks In Worksheets If InStr("Bill (", wks.Name) 0 Then wks.Delete Next wks Application.DisplayAlerts = True End Sub How can I fix this? Thank you, Thomas |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error 400...
Here is the code with a couple of message boxes. Let me know if when the code
is run if any message boxes pop up or not... Sub DeleteSheets() Dim wks As Worksheet On Error GoTo ErrorHandler msgbox "Let the deleteing begin..." Application.DisplayAlerts = False For Each wks In Worksheets If InStr(UCase(wks.Name), "BILL") 0 Then msgbox wks.name & _ " Delete" If InStr(UCase(wks.Name), "BILL") 0 Then wks.Delete Next wks msgbox "All Done..." ErrorHandler: Application.DisplayAlerts = True End Sub -- HTH... Jim Thomlinson "thomas" wrote: I must be doing something wrong because the code has no response. Perhaps it's how I call the DeleteSheets(). Can you give me an example of how to call it. Thank you, Thomas "Jim Thomlinson" wrote: My fault on your last thread... I just posted the response... Sub DeleteSheets() Dim wks As Worksheet On Error GoTo ErrorHandler Application.DisplayAlerts = False For Each wks In Worksheets If InStr(UCase(wks.Name), "BILL") 0 Then wks.Delete Next wks ErrorHandler: Application.DisplayAlerts = True End Sub -- HTH... Jim Thomlinson "thomas" wrote: I get an error Visual Basic "400" message when this code is ran: Sub DeleteSheets() Dim wks As Worksheet Application.DisplayAlerts = False For Each wks In Worksheets If InStr("Bill (", wks.Name) 0 Then wks.Delete Next wks Application.DisplayAlerts = True End Sub How can I fix this? Thank you, Thomas |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error 400...
I got to remember to use msgbox to help solve problems. It's not going
through the code because there is no worksheet of "Bill001" Which is the second (also is the last) msgbox which says "Bill001 Delete". Can't delete because it's not there so it breaks out of the code, I guess. Any ideas on how to fix. Thank you, Thomas "Jim Thomlinson" wrote: Here is the code with a couple of message boxes. Let me know if when the code is run if any message boxes pop up or not... Sub DeleteSheets() Dim wks As Worksheet On Error GoTo ErrorHandler msgbox "Let the deleteing begin..." Application.DisplayAlerts = False For Each wks In Worksheets If InStr(UCase(wks.Name), "BILL") 0 Then msgbox wks.name & _ " Delete" If InStr(UCase(wks.Name), "BILL") 0 Then wks.Delete Next wks msgbox "All Done..." ErrorHandler: Application.DisplayAlerts = True End Sub -- HTH... Jim Thomlinson "thomas" wrote: I must be doing something wrong because the code has no response. Perhaps it's how I call the DeleteSheets(). Can you give me an example of how to call it. Thank you, Thomas "Jim Thomlinson" wrote: My fault on your last thread... I just posted the response... Sub DeleteSheets() Dim wks As Worksheet On Error GoTo ErrorHandler Application.DisplayAlerts = False For Each wks In Worksheets If InStr(UCase(wks.Name), "BILL") 0 Then wks.Delete Next wks ErrorHandler: Application.DisplayAlerts = True End Sub -- HTH... Jim Thomlinson "thomas" wrote: I get an error Visual Basic "400" message when this code is ran: Sub DeleteSheets() Dim wks As Worksheet Application.DisplayAlerts = False For Each wks In Worksheets If InStr("Bill (", wks.Name) 0 Then wks.Delete Next wks Application.DisplayAlerts = True End Sub How can I fix this? Thank you, Thomas |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error 400...
Ok you have lost me. You can only have one sheet named Bill001, but you are
saying that you get two message boxes saying "Bill001 Delete"? -- HTH... Jim Thomlinson "thomas" wrote: I got to remember to use msgbox to help solve problems. It's not going through the code because there is no worksheet of "Bill001" Which is the second (also is the last) msgbox which says "Bill001 Delete". Can't delete because it's not there so it breaks out of the code, I guess. Any ideas on how to fix. Thank you, Thomas "Jim Thomlinson" wrote: Here is the code with a couple of message boxes. Let me know if when the code is run if any message boxes pop up or not... Sub DeleteSheets() Dim wks As Worksheet On Error GoTo ErrorHandler msgbox "Let the deleteing begin..." Application.DisplayAlerts = False For Each wks In Worksheets If InStr(UCase(wks.Name), "BILL") 0 Then msgbox wks.name & _ " Delete" If InStr(UCase(wks.Name), "BILL") 0 Then wks.Delete Next wks msgbox "All Done..." ErrorHandler: Application.DisplayAlerts = True End Sub -- HTH... Jim Thomlinson "thomas" wrote: I must be doing something wrong because the code has no response. Perhaps it's how I call the DeleteSheets(). Can you give me an example of how to call it. Thank you, Thomas "Jim Thomlinson" wrote: My fault on your last thread... I just posted the response... Sub DeleteSheets() Dim wks As Worksheet On Error GoTo ErrorHandler Application.DisplayAlerts = False For Each wks In Worksheets If InStr(UCase(wks.Name), "BILL") 0 Then wks.Delete Next wks ErrorHandler: Application.DisplayAlerts = True End Sub -- HTH... Jim Thomlinson "thomas" wrote: I get an error Visual Basic "400" message when this code is ran: Sub DeleteSheets() Dim wks As Worksheet Application.DisplayAlerts = False For Each wks In Worksheets If InStr("Bill (", wks.Name) 0 Then wks.Delete Next wks Application.DisplayAlerts = True End Sub How can I fix this? Thank you, Thomas |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error 400...
just a thought...I guess there might be a "Bill001" because of the code I
used to make copies of a Worksheet: Option Explicit Function AddSheets() Dim ws As Worksheet Set ws = Worksheets("Bill") ws.Copy Worksheets(1) SetSheetName Worksheets(1) End Function Private Sub SetSheetName(ws As Worksheet) On Error Resume Next Dim sname As String Dim index As Long index = 0 Do Err.Clear index = index + 1 sname = "Bill" & Format$(index, "000") ws.Name = sname Loop While Err.Number < 0 End Sub But the sheet's name in Excel reads "Bill (2)". Is there a way to work around this? "Jim Thomlinson" wrote: Here is the code with a couple of message boxes. Let me know if when the code is run if any message boxes pop up or not... Sub DeleteSheets() Dim wks As Worksheet On Error GoTo ErrorHandler msgbox "Let the deleteing begin..." Application.DisplayAlerts = False For Each wks In Worksheets If InStr(UCase(wks.Name), "BILL") 0 Then msgbox wks.name & _ " Delete" If InStr(UCase(wks.Name), "BILL") 0 Then wks.Delete Next wks msgbox "All Done..." ErrorHandler: Application.DisplayAlerts = True End Sub -- HTH... Jim Thomlinson "thomas" wrote: I must be doing something wrong because the code has no response. Perhaps it's how I call the DeleteSheets(). Can you give me an example of how to call it. Thank you, Thomas "Jim Thomlinson" wrote: My fault on your last thread... I just posted the response... Sub DeleteSheets() Dim wks As Worksheet On Error GoTo ErrorHandler Application.DisplayAlerts = False For Each wks In Worksheets If InStr(UCase(wks.Name), "BILL") 0 Then wks.Delete Next wks ErrorHandler: Application.DisplayAlerts = True End Sub -- HTH... Jim Thomlinson "thomas" wrote: I get an error Visual Basic "400" message when this code is ran: Sub DeleteSheets() Dim wks As Worksheet Application.DisplayAlerts = False For Each wks In Worksheets If InStr("Bill (", wks.Name) 0 Then wks.Delete Next wks Application.DisplayAlerts = True End Sub How can I fix this? Thank you, Thomas |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
| I have Error 1919 Error Configuring ODBC dataSource Database | Excel Discussion (Misc queries) | |||
| run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
| Automation Error, Unknown Error. Error value - 440 | Excel Programming | |||
Hybrid Mode
