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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
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:

fatchesta

New Member
Joined
Feb 5, 2016
Messages
2
Awesome Dave - The first option worked like a charm...Thanks so much for the prompt and effective help!! :)
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,682
Messages
5,637,764
Members
416,982
Latest member
lisam77

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
Top