Prompt Box

[markpedro]

I am attempting to create a prompt box (if available) that would prompt a user to enter a specific row to delete and move to another worksheet?

Thanks

 

[Smitty]

Assign the code that I posted to a button. "InputBox" will create a box for you.

Alternatively, MsgBox "Displays a message in a dialog box, waits for the user to click a button, and returns an Integer indicating which button the user clicked." But it will not accept user input.

See the VBA help file for "msgbox" & "input box". Both will giveyou detailed explanations.

Hope that helps,

Smitty

EDIT: Here's an example of both -

Sub CutMe()
    ans = MsgBox("Would you like to cut the information and paste it to a different worksheet?", _
        vbQuestion + vbYesNo, "Let's get on with it!")
    Select Case ans
        Case vbNo
            Exit Sub
        Case vbYes
            ans1 = InputBox("Please enter the row number to cut")
                 Rows(ans1 & ":" & ans1).Cut
             ans2 = InputBox("Please enter the sheet to copy to...")
                 Sheets(ans2).Select
                 ActiveSheet.Paste
   End Select
End Sub

 

[SlinkRN]

Hi MarkPedro, I'll get back to you later tonight - I have an assignment to finish right now, but I'd love to walk you through it if you still need help when I'm done. Slink

 

[SlinkRN]

MarkPedro, I just tried out pennysaver's code and it works wonderfully! I was going to walk you through making a userform, but pennysaver's way is SO much easier. Just copy and paste pennysaver's code to a macro and then make a button by going to View - Forms. Then right click on the button and assign the macro that has the code in it.
If you didn't want to paste the deleted row anywhere just replace the cut part of the code with .entirerowdelete
Slink

PS: Thanks pennysaver, I had forgotten about the input box

 

[markpedro]

One last thing....What is the code that specifies to place the row into the next available row in the new sheet.....

 

[Smitty]

Try this modification:

Sub CutMe()
    ans = MsgBox("Would you like to cut the information and paste it to a different worksheet?", _
        vbQuestion + vbYesNo, "Let's get on with it!")
    Select Case ans
        Case vbNo
            Exit Sub
        Case vbYes
            ans1 = InputBox("Please enter the row number to cut", "Source Row")
                Rows(ans1 & ":" & ans1).Cut
            ans2 = InputBox("Please enter the sheet to copy to...", "Destination Sheet")
                ActiveSheet.Paste _
            Destination:=Worksheets(ans2).Range("A65536").End(xlUp).Offset(1, 0)
    End Select
End Sub

Note that this assumes that your data range begins in column A.

Hope that helps,

Smitty