Cancelling a Pop Up Box error

fatchesta

New Member
Joined
Feb 5, 2016
Messages
2
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi
see if this update to your code helps:

Code:
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:

Code:
    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
 
Last edited:
Upvote 0
Awesome Dave - The first option worked like a charm...Thanks so much for the prompt and effective help!! :)
 
Upvote 0

Forum statistics

Threads
1,211,772
Messages
6,103,876
Members
447,882
Latest member
LORENA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top