jaydee
08-06-2013, 05:04 PM
Hi guys,
The workbook attached has bank activity which my company uses to track our balance. Everyday I download the bank transactions and paste the information into the worksheet.
I'm having trouble with the application.inputbox function as I never really used it.
When I run my macro, it asks what line the data should be pasted to on the "Bank Activity" worksheet.
The problem is, I only want users to enter numbers. If the person enters letters or presses cancel, I would like a message that says "You must enter a number" or "Operation Cancelled".
I thought the code would be something like the following, but I can't get it to work right.
response = InputBox(Prompt:="Enter row where data will be pasted", Title:="Daily Bank")
If WorksheetFunction.IsNumber(response) Then
Worksheets("Extracted").Range("E1:H1").Copy
Worksheets("Bank Activity").Range("E" & response).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Else
MsgBox "You must enter a number. Operation Cancelled"
Exit Sub
End If
Also, if the row where the data is about to be pasted to already contains information, is there a way to alert the user so he can abort it? i.e. user accidently enters 10 in the input box, when he really meant to put 100.
Can someone please take a look and help me? Thank you.
The workbook attached has bank activity which my company uses to track our balance. Everyday I download the bank transactions and paste the information into the worksheet.
I'm having trouble with the application.inputbox function as I never really used it.
When I run my macro, it asks what line the data should be pasted to on the "Bank Activity" worksheet.
The problem is, I only want users to enter numbers. If the person enters letters or presses cancel, I would like a message that says "You must enter a number" or "Operation Cancelled".
I thought the code would be something like the following, but I can't get it to work right.
response = InputBox(Prompt:="Enter row where data will be pasted", Title:="Daily Bank")
If WorksheetFunction.IsNumber(response) Then
Worksheets("Extracted").Range("E1:H1").Copy
Worksheets("Bank Activity").Range("E" & response).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Else
MsgBox "You must enter a number. Operation Cancelled"
Exit Sub
End If
Also, if the row where the data is about to be pasted to already contains information, is there a way to alert the user so he can abort it? i.e. user accidently enters 10 in the input box, when he really meant to put 100.
Can someone please take a look and help me? Thank you.