Need help with Inputbox function and If Statement

[jaydee]

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.

[SamT]

Forget Asking for the Row number, This Code will always return the Row number of the Row just under the last entry in Column "A"

Dim PasteRow As Long
PasteRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

[jaydee]

Thanks Sam, I didn't really think of that because I cropped out some of the data, but at the end of each month there is a total row. But, I can use Cells(rows.count, 5).end(xlup).end(xlup) and that works fine.

Do you know the answer to my questions anyway, just for my own knowledge? The code looks okay to me, but I can't figure out what I'm missing to make it work.

Anyway, thanks again!

[SamT]

If you paste this code into the "Bank" sheets' code page, it will show Excel's simplest Data Entry Form each time the sheet is viewed.

Sub WorkSheet_Activate()
Range("A1:H1").Select
ActiveSheet.ShowDataForm
End Sub

If you wanted to, you can build your own Data Entry Form that has drop down lists for the most common values of any data. You can allow the entry of any currency and the form's code can convert it to any other currency before saving the value, (you would nned a conversion table somewhere.)

Many values can be automatically entered by the form based on other values, for example, when a User selected an acct name from the drop down list, the acct number would be auto-entered. Today's date is another example.

You can determine whether or not to let users modify any existing record or only enter new records.

[SamT]

I think (I'm using Excel 2002,) that IsNumber should be IsNumeric and to insure the wrong row wasn't entered, you could use
If Not(Cells(Response, 5) = "") Then
'Oopsies! :)

[SamT]

BTW, you cannot use that simple Data Entry Form unless you move the totals Rows to the Top of the sheet.

It might not be GAAP to have the totals on top, but it is good Excel Practice. With a custom Form, It doesn't matter.

[jaydee]

Thank you, Sam!
I'm going to try this later today when I have time. I've been doing the month end close for the financial statements and haven't had time.

I realized if I use end(xlup) etc, the column I use has to have data from the previous day, but it depends if there were transactions in that category. Maybe I can use the dates to help find the row.

I'm going to get this working, thanks again for your help, I really appreciate your input.

[SamT]

I would assume the Date column has dates for every transaction Row.

I finally took a good look at you upload. It sure looks like a Single-Entry bookkeeping system, which is why you are having so much trouble maintaining it.

Even as a SE system, it can be streamlined to make it easier to use and maintain, and at the same time more idiot proof.

If you're interested I can whip up something to give you a taste of a better designed system.