locked
Please help - the error checker is not working. Any ideas????? RRS feed

  • Question

  • Private Sub CommandButton1_Click()
        
    If TextBox1.Value = "" Then GoTo First

    Dim new_sheet_name
    Let new_sheet_name = TextBox1.Value

    On Error GoTo ErrorHandler:
      
    Sheets("Master").Select
    Sheets("Master").Copy Before:=Sheets(2)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = new_sheet_name

    With UserForm2
    Unload Me
    End With

    Exit Sub
       
    First:
    MsgBox "You have not entered the required Data"

    Exit Sub
     
    ErrorHandler:

    MsgBox "Name Already Exsists"

    With UserForm2
    Unload Me
    End With

    Exit Sub

    End Sub


    HWILLIAMS

    Thursday, March 8, 2012 6:42 PM

Answers

  • Hello HWilliams:

    Yes... from your code, I thought your goal was to enter a new worksheet name and  create a copy of the master using the new name.  It looks like you are OK from other posts once you clarified your goal.

    Best regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    • Marked as answer by Hayley s1 Friday, March 9, 2012 8:43 PM
    Friday, March 9, 2012 5:54 PM
  • If you want to delete sheets from a workbook without the user
    being prompted for any
    confirmations you can use this macro:
    Sub DeleteSheet(strSheetName As String)
    ' deletes a sheet named strSheetName in the active workbook
        Application.DisplayAlerts = False
        Sheets(strSheetName).Delete
        Application.DisplayAlerts = True
    End Sub
    

    Got it - its working, it's not exactly as planned but I get the end result.
    Thanks All...


    HWILLIAMS

    • Marked as answer by Hayley s1 Friday, March 9, 2012 8:43 PM
    Friday, March 9, 2012 1:36 PM

All replies

  • Which part is not working?

    Brian, ProcessIT- Hawke's Bay, New Zealand

    Thursday, March 8, 2012 7:53 PM
  • Hello HWilliams:

    I modified your code and tested the error routine.  It worked for me.  I used a divide by zero to test the error routine.

    Try the following code... it should work as intended.

    Option Explicit
    
    Private Sub CommandButton1_Click()
    Dim new_sheet_name As String
    Dim wkbThisWorkbook As Workbook
    Dim wksMaster As Worksheet
    Dim intTestError As Integer
    
    On Error GoTo ErrorHandler
    Set wkbThisWorkbook = ThisWorkbook
    Set wksMaster = wkbThisWorkbook.Sheets("Master")
    
    If TextBox1.Value = "" Then
        MsgBox ("You Have Not Entered The Required Data")
        Exit Sub
    End If
    
    ' intTestError = 1 / 0
    
    new_sheet_name = TextBox1.Value
    
    wksMaster.Copy Before:=wkbThisWorkbook.Sheets(2)
    wkbThisWorkbook.Sheets(2).Name = new_sheet_name
    
    Unload UserForm2
    Exit Sub
      
    ErrorHandler:
    MsgBox ("An Error Occurred.  Error Number = " & Err.Number & "  " & Err.Description)
    Unload UserForm2
    
    End Sub
    

    Regards,


    • Edited by RichLocus Friday, March 9, 2012 6:52 AM
    Friday, March 9, 2012 6:48 AM
  • Hi RichLocus, it was very close but it still creates a copy of the sheet. This is what I was trying to avoid, however as you can see from my code I am just a beginner.

    I did try another method which again was very close to what I wanted but didn't quite do it.

    I added a line which deletes the worksheet but comes up with the message box, delete or cancel. Is there a way of forcing a delete of a worksheet without a message box?

    Thanks for your reply so far, hope to hear from you soon.

    Private Sub CommandButton1_Click()
     

       
    If TextBox1.Value = "" Then GoTo First

    Dim new_sheet_name
    Let new_sheet_name = TextBox1.Value


    On Error GoTo ErrorHandler

      
    Sheets("Master").Select
    Sheets("Master").Copy Before:=Sheets(2)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = new_sheet_name

    With UserForm2
    Unload Me
    End With

    Exit Sub
       
    First:
    MsgBox "You have not entered the required Data"

    Exit Sub
     
    ErrorHandler:

    MsgBox "Name Already Exists"
    Sheets("Master (2)").Delete

    Sheets("Homepage").Select

    With UserForm2
    Unload Me
    End With

    Exit Sub

    End Sub

     

    HWILLIAMS

    Friday, March 9, 2012 1:00 PM
  • Hi Brian,

    The error is detected and the message displays, the down side is the worksheet is still copied and named master2. I do not want a copy of the worksheet if there is an error.

    Thanks for your reply.


    HWILLIAMS

    Friday, March 9, 2012 1:04 PM
  • If you want to delete sheets from a workbook without the user
    being prompted for any
    confirmations you can use this macro:
    Sub DeleteSheet(strSheetName As String)
    ' deletes a sheet named strSheetName in the active workbook
        Application.DisplayAlerts = False
        Sheets(strSheetName).Delete
        Application.DisplayAlerts = True
    End Sub
    

    Got it - its working, it's not exactly as planned but I get the end result.
    Thanks All...


    HWILLIAMS

    • Marked as answer by Hayley s1 Friday, March 9, 2012 8:43 PM
    Friday, March 9, 2012 1:36 PM
  • Hello HWilliams:

    Yes... from your code, I thought your goal was to enter a new worksheet name and  create a copy of the master using the new name.  It looks like you are OK from other posts once you clarified your goal.

    Best regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    • Marked as answer by Hayley s1 Friday, March 9, 2012 8:43 PM
    Friday, March 9, 2012 5:54 PM