Announcement

Collapse
No announcement yet.

Stop the command in case of error

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Stop the command in case of error



    Hi All,

    I have a simple VBA function to change the sheet and graph names according to the name provided in a specific cell. Everything is working fine, however, I would like to have an error check which would stop the command and display a message box. I manage to display message box but the sheet name still changes. I'm providing my function below. Where do I need to change it to work properly? Thank you in advance!

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("B30")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing Then
    On Error GoTo ErrorHandler

    With ActiveSheet
    .ListObjects(1).Name = Range("B30")
    End With

    ActiveSheet.Name = "Pr_" & Range("B30").Value

    ErrorHandler:
    If Err Then
    MsgBox "Name not valid!"
    Err.Clear
    End If

    End If

    End Sub

  • #2
    Can't be 100% sure what's going on to cause the error, so maybe try:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim originalGraphName As String
        Dim originalSheetName As String
        Dim KeyCells As Range
        Set KeyCells = Range("B30")
        
        If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
            On Error GoTo ErrorHandler
            
            With ActiveSheet
                originalGraphName = .ListObjects(1).Name
                originalSheetName = .Name
                .ListObjects(1).Name = Range("B30")
                .Name = "Pr_" & Range("B30").Value
            End With
        End If
        Exit Sub
        
    ErrorHandler:
        If Err Then
            With ActiveSheet
                .ListObjects(1).Name = originalGraphName
                .Name = originalSheetName
            End With
            MsgBox "Name not valid!"
            Err.Clear
        End If
    End Sub

    Comment


    • #3
      That does the trick! Thanks!
      Just one more request. In case of error, I need the cell B30 to be unchanged as well.

      Comment


      • #4
        slightly more involved, but:

        Code:
        Private Sub Worksheet_Change(ByVal Target As Range)
            Dim originalGraphName As String
            Dim originalSheetName As String
            Dim originalValue As Variant
            Dim changedValue As Variant
            Dim KeyCells As Range
            Set KeyCells = Range("B30")
            
            If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
                changedValue = Target.Value
                Application.EnableEvents = False
                Application.Undo
                originalValue = Target.Value
                Target.Value = changedValue
                Application.EnableEvents = True
                On Error GoTo ErrorHandler
                
                With ActiveSheet
                    originalSheetName = .Name
                    originalGraphName = .ListObjects(1).Name
                    .ListObjects(1).Name = Range("B30")
                    .Name = "Pr_" & Range("B30").Value
                End With
            End If
            Exit Sub
            
        ErrorHandler:
            If Err Then
                With ActiveSheet
                    If .ListObjects.Count > 0 Then .ListObjects(1).Name = originalGraphName
                    .Name = originalSheetName
                End With
                Application.EnableEvents = False
                Target.Value = originalValue
                Application.EnableEvents = True
                MsgBox "Name not valid!"
                Err.Clear
            End If
        End Sub

        Comment


        • #5


          Works great! Thank you!

          Comment

          Working...
          X