Pass value to a double click procedure

[FryGirl]

If have a double click procedure that will add some new rows. As you can see the .Resize is hard coded right now, but I would like to grab a number from the user as they input their desired number rows.

How do I pass the value from the Sub back to the double click?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Application.ScreenUpdating = False
    If Target.Column = 1 Then
        On Error Resume Next
        Target.EntireRow.Copy
       
        Range("A" & Target.Row).Offset(1, 0).Resize(12).Insert Shift:=xlDown
        Range("A" & Target.Row).Offset(1, 0).Resize(12).Value = "Add"
    End If
    Application.ScreenUpdating = True
End Sub

Public Sub MyInputBox()

    Dim msg As String: msg = "Please enter a number in the box below for the number of records you would like to add"
    Dim MyInput As String: MyInput = InputBox(msg, "Adding additional records", "Enter your input number here!")
   
    If MyInput = "Enter your input number here!" Or MyInput = "" Then
      Exit Sub
    End If
   
End Sub

 

[dmt32]

Hi
you normally use a Function to return value to the calling procedure.

see if this update to your codes helps

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim NoRows      As Variant
    
    If Target.Column = 1 Then
        Cancel = True
        NoRows = GetInputBox
        If NoRows <> False Then
            Application.ScreenUpdating = False
            
            Target.EntireRow.Copy
            
            Range("A" & Target.Row).Offset(1, 0).Resize(NoRows).Insert Shift:=xlDown
            Range("A" & Target.Row).Offset(1, 0).Resize(NoRows).Value = "Add"
        End If
    End If
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub

Function GetInputBox() As Variant
    
    Const strPrompt     As String = "Please enter a number in the box below For the number of records you would Like To add"
    Const strDefault    As String = "Enter your input number here!"
    Const strTitle      As String = "Adding additional records"
    
    Do
        GetInputBox = InputBox(strPrompt, strTitle, strDefault)
        'cancel pressed
        If StrPtr(GetInputBox) = 0 Then GetInputBox = False: Exit Function
    Loop Until Val(GetInputBox) > 0
    
End Function

Dave

 

[FryGirl]

Thank you Dave, this works great.