Warning Pop-Up

[bergsmat]

Hi I was wondering if there was anyway for a warning message to pop-up when a user tries to insert or delete rows from a workbook? Ideally I would like to have a simple message box appear telling them the dangers of adding/removing a row or column incorrectly. I would like it to give the user an option to continue (add/delete the row or column) or cancel (do nothing).

Any help would be great!

Tyler

 

[tweedle]

Something like this may be a start....

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    Dim retval As Integer
    If Target.Rows.Count = Sh.Rows.Count Or _
    Target.Columns.Count = Sh.Columns.Count Then
    'Acting on Column(s) or _
    'Acting on Rows(s)
    Msg001 = "Be warned, you are acting on items that will potentially destroy the operation of this workbook."
        retval = MsgBox(Msg001, vbOKCancel, "Warning!")
        If retval <> vbOK Then 'They Canceled
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
        Else 'They OKed
            'Do nothing
        End If
    End If
End Sub

 

[bergsmat]

That worked great! Is there code that could be added to this that has the same effect if the rows or columns are trying to be hidden?

Tyler

 

[tweedle]

Technically, I think hiding and unhiding are formatting changes that do not initiate events.
The best I could see was when the Sheet was deactivated (i.e. Select another sheet); then check and alert

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Dim retval As Integer
    Dim col As Range, rw As Range
    For Each col In Sh.UsedRange.Columns
        'Check Columns
        If col.Hidden Then
                    msg = "There are hidden columns in the worksheet (" & Sh.Name & ").  Unhide?"
                    retval = MsgBox(msg, vbYesNo, "Warning")
                    If retval = vbYes Then 'They OKd it
                        Application.EnableEvents = False
                            Sh.UsedRange.Columns.Hidden = False
                            Sh.UsedRange.Rows.Hidden = False
                        Application.EnableEvents = True
                    Else    'They didnt OK it
                        'Do nothing
                    End If
                    
        Else
            'Check Rows
            For Each rw In Sh.UsedRange.Rows
                If rw.Hidden Then
                    msg = "There are hidden rows in the worksheet (" & Sh.Name & ").  Unhide?"
                    retval = MsgBox(msg, vbYesNo, "Warning")
                    If retval = vbYes Then 'They OKd it
                        Application.EnableEvents = False
                            Sh.UsedRange.Rows.Hidden = False
                        Application.EnableEvents = True
                    Else    'They didnt OK it
                        'Do nothing
                    End If
                End If
            Next
        End If
    Next
   
End Sub

 

[bergsmat]

That works as well as I need it to! Thanks a lot, I appreciate it.