Cancelling a Pop Up Box error

[fatchesta]

Hi Team,

I am getting - Runtime error '13' Type Mismatch when I click cancel or ok without entering a number on the pop up attached to the following macro for copying and inserting the current row X times below itself. Can somebody suggest a modification to my code that will only allow the pop up to close if a number other than zero is entered in which case the Macro progresses or if Cancel is clicked which will stop the Macro and exit the pop up.

Sub Action_1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+a
'
n = InputBox("How Many additional Actions?")
ActiveSheet.Unprotect ("master")
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(n, 0)).Select
Selection.EntireRow.Insert
ActiveCell.Offset(0, 8).Resize(n, 5).Select
Selection.ClearContents
ActiveSheet.Rows.AutoFit
ActiveCell.Offset(n, -8).Range("A1").Select
ActiveSheet.Protect ("master"), DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Cheers

 

[dmt32]

Hi
see if this update to your code helps:

Sub Action_1()
    '
    ' Macro1 Macro
    '
    ' Keyboard Shortcut: Ctrl+a
    '
    Dim n As Variant
    Do
    n = InputBox("How Many additional Actions?")
    'cancel pressed
    If StrPtr(n) = 0 Then Exit Sub
    Loop Until IsNumeric(n) And n > 0
    
    



    ActiveSheet.Unprotect ("master")
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Copy
    Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(n, 0)).Select
    Selection.EntireRow.Insert
    ActiveCell.Offset(0, 8).Resize(n, 5).Select
    Selection.ClearContents
    ActiveSheet.Rows.AutoFit
    ActiveCell.Offset(n, -8).Range("A1").Select
    ActiveSheet.Protect ("master"), DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

or you could try using the InputBox method where you can specify data type:

    Dim n As Variant
    n = Application.InputBox(prompt:="How Many additional Actions?", Type:=2)
    'cancel pressed
    If n = False Then Exit Sub

where Type 2 is numeric

Dave

 

[fatchesta]

Awesome Dave - The first option worked like a charm...Thanks so much for the prompt and effective help!!

 

[dmt32]

Awesome Dave - The first option worked like a charm...Thanks so much for the prompt and effective help!!

Most welcome - many thanks for feedback

Dave