On Error GoTo .... error
I have the following code which is supposed to enter a new worksheet,
rename it "Working Template" and do other stuff. There is an error check that is supposed to flag the error that occurs if a "Working Template" sheet already exists - then delete the current (newly added) sheet and the "Working Template", then go back to the beginning. For some reason, though, it's just not working at all. I wrote the code on another computer and had no problems, but on this machine it refuses to catch it. I could rewrite it to circumvent the problem, but it is happening elsewhere too, so i would like to get a better understanding of why. Any advice or recommendations would be greatly appreciated. Function () beginning: Sheets.Add After:=Sheets(6) On Error GoTo error_occured Sheets(7).Name = "Working Template" Sheets(6).Cells.Copy Sheets(7).Select With Selection .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats End With Cells.UnMerge Exit Function error_occured: Application.DisplayAlerts = False ActiveSheet.Delete Sheets("Working Template").Delete Application.DisplayAlerts = True Resume beginning End Function Thanks, Thedude |
On Error GoTo .... error
"it's just not working at all"
The first thing to do is give the function a name. From... Function() To... Function Randolph_Scott() -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) wrote in message I have the following code which is supposed to enter a new worksheet, rename it "Working Template" and do other stuff. There is an error check that is supposed to flag the error that occurs if a "Working Template" sheet already exists - then delete the current (newly added) sheet and the "Working Template", then go back to the beginning. For some reason, though, it's just not working at all. I wrote the code on another computer and had no problems, but on this machine it refuses to catch it. I could rewrite it to circumvent the problem, but it is happening elsewhere too, so i would like to get a better understanding of why. Any advice or recommendations would be greatly appreciated. Function () beginning: Sheets.Add After:=Sheets(6) On Error GoTo error_occured Sheets(7).Name = "Working Template" Sheets(6).Cells.Copy Sheets(7).Select With Selection .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats End With Cells.UnMerge Exit Function error_occured: Application.DisplayAlerts = False ActiveSheet.Delete Sheets("Working Template").Delete Application.DisplayAlerts = True Resume beginning End Function Thanks, Thedude |
On Error GoTo .... error
I'd just loop through all the sheets first to see if it exists:
Function myfunction() Dim sh As Worksheet Application.DisplayAlerts = False For Each sh In ThisWorkbook.Worksheets If sh.Name = "Working Template" Then sh.Delete Exit For End If Next sh ' do stuff Application.DisplayAlerts = True End Function -- Dan On Jan 3, 12:00*pm, wrote: I have the following code which is supposed to enter a new worksheet, rename it "Working Template" and do other stuff. *There is an error check that is supposed to flag the error that occurs if a "Working Template" sheet already exists - then delete the current (newly added) sheet and the "Working Template", then go back to the beginning. *For some reason, though, it's just not working at all. *I wrote the code on another computer and had no problems, but on this machine it refuses to catch it. *I could rewrite it to circumvent the problem, but it is happening elsewhere too, so i would like to get a better understanding of why. Any advice or recommendations would be greatly appreciated. Function () beginning: * * Sheets.Add After:=Sheets(6) * * On Error GoTo error_occured * * Sheets(7).Name = "Working Template" * * Sheets(6).Cells.Copy * * Sheets(7).Select * * With Selection * * * * .PasteSpecial Paste:=xlPasteValues * * * * .PasteSpecial Paste:=xlPasteFormats * * End With * * Cells.UnMerge Exit Function error_occured: * * * * Application.DisplayAlerts = False * * * * * * ActiveSheet.Delete * * * * * * Sheets("Working Template").Delete * * * * Application.DisplayAlerts = True * * * * Resume beginning End Function Thanks, Thedude |
On Error GoTo .... error
Thank you everyone for your quick replies. To answer your questions:
Jim: Haha, the function is named. I missed it when i was copying over and instead of typing out Function create_working_template_sheet(), i took the lazy man's road and just wrote function(). It came back to bite me in the end, but hindsight is always 20/20 :). Dan: That's an excellent suggestion and one i will more than likely implement. Thank you. However, i'm still curious as to why the On Error statement isn't catching the error. This is the first time i've attempted to use error checking and not very familiar with how it works. It seemed fairly straightforward, but maybe not? Don: I'm not sure how that is different from what i've done - except the order of the process. At the end of the macro, the "Working Template" will be deleted, and this subroutine is set up to delete the "Working Template" that could remain if the macro was interrupted for some reason. It seems as though multiple Template(n) sheets could be left, so in this situation, i don't think that would work. |
On Error GoTo .... error
I don't know why the error isn't getting flagged, but it occurs to me
that there might be a better way tpo spot your problem. Each of the following three pages provides a different way to check if a Sheet Exists: http://tinyurl.com/2fm4tl http://www.dailydoseofexcel.com/arch...8/sheetexists/ http://j-walk.com/ss/Excel/tips/tip54.htm So, you could add one of those functions to a code module, and change your code to to something like (untested): Function WhateverItsCalled() If SheetExists("Working Template") then Application.DisplayAlerts = False Sheets("Working Template").Delete Application.DisplayAlerts = True end if Sheets.Add After:=Sheets(6) Sheets(7).Name = "Working Template" Sheets(6).Cells.Copy Sheets(7).Select With Selection .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats End With Cells.UnMerge end function wrote: I have the following code which is supposed to enter a new worksheet, rename it "Working Template" and do other stuff. There is an error check that is supposed to flag the error that occurs if a "Working Template" sheet already exists - then delete the current (newly added) sheet and the "Working Template", then go back to the beginning. For some reason, though, it's just not working at all. I wrote the code on another computer and had no problems, but on this machine it refuses to catch it. I could rewrite it to circumvent the problem, but it is happening elsewhere too, so i would like to get a better understanding of why. Any advice or recommendations would be greatly appreciated. Function () beginning: Sheets.Add After:=Sheets(6) On Error GoTo error_occured Sheets(7).Name = "Working Template" Sheets(6).Cells.Copy Sheets(7).Select With Selection .PasteSpecial Paste:=xlPasteValues .PasteSpecial Paste:=xlPasteFormats End With Cells.UnMerge Exit Function error_occured: Application.DisplayAlerts = False ActiveSheet.Delete Sheets("Working Template").Delete Application.DisplayAlerts = True Resume beginning End Function Thanks, Thedude |
On Error GoTo .... error
Thanks for the suggestions, darren. It seems like i find a new
function (SheetExists) everyday... Going back to the original problem, here's another situation where the error isn't catching. This function is allowing the user to select the master file. If the user accidentally clicks a file that is already open, the message saying its open and asking if the user wants to replace it pops up. If 'NO' is selected, the error occurs. It SHOULD be flagged and just restart the routine, but, like before.... nothing. Private Sub cmb_OpenProjectFile_Click() Dim pfilename As Variant On Error Resume Next beginning: original_workbook = ActiveWorkbook.Name pfilename = Application.GetOpenFilename _ ("Excel File (*.xls),*.xls", , "Select the file to merge") If pfilename < False Then If pfilename < ActiveWorkbook.Name Then On Error Resume Next Application.Workbooks.Open (pfilename) If ActiveWorkbook.Name = original_workbook Then _ GoTo beginning usf_MergeData.cbo_selectProjectFile.Text = ActiveWorkbook.Name usf_MergeData.cbo_selectProjectFile.AddItem ActiveWorkbook.Name usf_MergeData.cbo_selectProjectFile.BoundColumn = 0 End If End If End Sub |
On Error GoTo .... error
Well, i've got a workaround done, but if anybody has any clues as to
why the On Error functions aren't working, feel free to chime in here. Private Sub cmb_OpenProjectFile_Click() Dim pfilename As Variant On Error Resume Next beginning: original_workbook = ActiveWorkbook.Name pfilename = Application.GetOpenFilename("Excel File (*.xls),*.xls", , "Select the file to merge") slashloc = InStr(1, pfilename, "\", vbTextCompare) Do Until slashloc = 0 slash_loc = slashloc slashloc = InStr(slashloc + 1, pfilename, "\", vbTextCompare) Loop Filename = Right(pfilename, Len(pfilename) - slash_loc) If pfilename < False Then If Filename < ActiveWorkbook.Name Then On Error GoTo beginning Application.Workbooks.Open (pfilename) If ActiveWorkbook.Name = original_workbook Then _ GoTo beginning usf_MergeData.cbo_selectProjectFile.Text = ActiveWorkbook.Name usf_MergeData.cbo_selectProjectFile.AddItem ActiveWorkbook.Name usf_MergeData.cbo_selectProjectFile.BoundColumn = 0 End If End If End Sub Regards, Thedude |
On Error GoTo .... error
UYou have an On Error Resume Next at the start of your code. Should that
be there? It tells excel to ignore errors. wrote: Well, i've got a workaround done, but if anybody has any clues as to why the On Error functions aren't working, feel free to chime in here. Private Sub cmb_OpenProjectFile_Click() Dim pfilename As Variant On Error Resume Next beginning: original_workbook = ActiveWorkbook.Name pfilename = Application.GetOpenFilename("Excel File (*.xls),*.xls", , "Select the file to merge") slashloc = InStr(1, pfilename, "\", vbTextCompare) Do Until slashloc = 0 slash_loc = slashloc slashloc = InStr(slashloc + 1, pfilename, "\", vbTextCompare) Loop Filename = Right(pfilename, Len(pfilename) - slash_loc) If pfilename < False Then If Filename < ActiveWorkbook.Name Then On Error GoTo beginning Application.Workbooks.Open (pfilename) If ActiveWorkbook.Name = original_workbook Then _ GoTo beginning usf_MergeData.cbo_selectProjectFile.Text = ActiveWorkbook.Name usf_MergeData.cbo_selectProjectFile.AddItem ActiveWorkbook.Name usf_MergeData.cbo_selectProjectFile.BoundColumn = 0 End If End If End Sub Regards, Thedude |
On Error GoTo .... error
On Jan 3, 11:31 pm, Darren Hill wrote:
UYou have an On Error Resume Next at the start of your code. Should that be there? It tells excel to ignore errors. Darren, I tried different variations of the On Error - the other being On Error GoTo Beginning. My thinking with the Resume Next was that if the file is already open, it would be the ActiveWorkbook. The error would be skipped and the If statement following the On Error would catch it and send it back to the beginning (because the On Error GoTo Beginning wasn't working). On second thought, though, it seems that checking it against all open workbooks would be the most robust method. That link you provided should work nicely for that. As a side note, after playing with it a bit more i'm still at a loss as to why the error checking isn't working. Again - any ideas would be welcomed.... anybody? Regards, Thedude |
On Error GoTo .... error
This has to be a settings issue. Even the Workbookisopen() function
from the link you provided is erroring out. I checked it on another machine and it works fine... And i'm an idiot. For the next guy that has this issue, go to Tools =Options = General. Make sure that "Break on Every Error" is not selected in the "Error Trapping" portion. Thanks for your help everyone. Regards, Thedude |
All times are GMT +1. The time now is 12:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com