Point Me In The Right Direction

[josephezell]

I'm not looking for anyone to do the work for me; I want to be pointed in the right direction.

I want to create an Excel spread sheet for logging call information when our main tracking system is down. It doesn't need to be sophisticated.

I want to create a work sheet with input fields:

First Name
Last Name
ID Number
Address
Phone Number
Comments
etc
​

Once the information is entered, the user clicks a submit button and the raw data is dumped/stored on a second work sheet in the same workbook.

Outside of summing the total number of calls, there will be no calculating.

Can I do this with Excel (2007)? Do you advise using some of the Developer tools like text fields? What's the best way to get started?

Thanks for the advice.

Joe

 

[mattm1958]

Joe,

Now that I look at my code again, I see one thing that needs changed.

The "lastrow=" line near the top of the subroutine looks at column D, however the Data sheet is only growing in columns H, I and J, so on successive passes, the LastRow will not be calculated correctly. Sorry about the bug in there. Change the "D65365" in the "Lastrow=" line to "H65365" and it will work.

 

[josephezell]

Joe,

Now that I look at my code again, I see one thing that needs changed.

The "lastrow=" line near the top of the subroutine looks at column D, however the Data sheet is only growing in columns H, I and J, so on successive passes, the LastRow will not be calculated correctly. Sorry about the bug in there. Change the "D65365" in the "Lastrow=" line to "H65365" and it will work.

I've got lot on my plate right now. I'll make the needed changes and give this a test run a.s.a.p. Thanks for the help.

Joe

 

[josephezell]

I know you are thinking this guy doesn';t know what he is doing, well - you are right. I'm learning as I go.

Here is the code so far. It works as far as posting the data and clearing the fields but, it does not move to the next row. It posts to the first row every time.

Sub SaveMyData()
'
' SaveData Macro
' Saves information from Dashboard to Data
'
Dim lastrow As Long
lastrow = Worksheets("Data").Range("O65536").End(xlUp).Row
NextRow = lastrow + 1

Source_Date = "D6"
Source_State = "H6"
Source_Inquiry_Type = "D8"
Source_Member_ID = "H8"
Source_Inquirer_Last_Name = "D10"
Source_Inquirer_First_Name = "H10"
Source_Contact_Name = "L10"
Source_Reference_Type = "D14"
Source_Reference_ID = "H14"
Source_Reference_Last_Name = "D16"
Source_Reference_First_Name = "H16"
Source_Telephone = "D18"
Source_Reason = "H18"
Source_Comments = "D22:L23"
Source_Comments2 = "D25:L26"

Destination_Date = "A"
Destination_State = "B"
Destination_Inquiry_Type = "C"
Destination_Member_ID = "D"
Destination_Inquirer_Last_Name = "E"
Destination_Inquirer_First_Name = "F"
Destination_Contact_Name = "G"
Destination_Reference_Type = "H"
Destination_Reference_ID = "I"
Destination_Reference_Last_Name = "J"
Destination_Reference_First_Name = "K"
Destination_Telephone = "L"
Destination_Reason = "M"
Destination_Comments = "N"
Destination_Comments2 = "O"

InputRange = Source_Date
NextCol = Destination_Date
Worksheets("Dashboard").Range(Source_Date).Copy Destination:=Worksheets("Data").Range(NextCol & NextRow)
Worksheets("Dashboard").Range(Source_Date).ClearContents

InputRange = Source_State
NextCol = Destination_State
Worksheets("Dashboard").Range(Source_State).Copy Destination:=Worksheets("Data").Range(NextCol & NextRow)
Worksheets("Dashboard").Range(Source_State).ClearContents

InputRange = Source_Inquiry_Type
NextCol = Destination_Inquiry_Type
Worksheets("Dashboard").Range(Source_Inquiry_Type).Copy Destination:=Worksheets("Data").Range(NextCol & NextRow)
Worksheets("Dashboard").Range(Source_Inquiry_Type).ClearContents

InputRange = Source_Member_ID
NextCol = Destination_Member_ID
Worksheets("Dashboard").Range(Source_Member_ID).Copy Destination:=Worksheets("Data").Range(NextCol & NextRow)
Worksheets("Dashboard").Range(Source_Member_ID).ClearContents

InputRange = Source_Inquirer_Last_Name
NextCol = Destination_Inquirer_Last_Name
Worksheets("Dashboard").Range(Source_Inquirer_Last_Name).Copy Destination:=Worksheets("Data").Range(NextCol & NextRow)
Worksheets("Dashboard").Range(Source_Inquirer_Last_Name).ClearContents

InputRange = Source_Inquirer_First_Name
NextCol = Destination_Inquirer_First_Name
Worksheets("Dashboard").Range(Source_Inquirer_First_Name).Copy Destination:=Worksheets("Data").Range(NextCol & NextRow)
Worksheets("Dashboard").Range(Source_Inquirer_First_Name).ClearContents

End Sub

Of course, there will be more lines. This is all I am testing right now.

How do I get the code to jump to the next available row?

Joe

 

[mattm1958]

Joe,

Take a look at the lines at the top that read.....

lastrow = Worksheets("Data").Range("O65536").End(xlUp).Row
NextRow = lastrow + 1

The "O" in "O65536" makes that statement search from the bottom up ("End(xlUp).") to find the last cell in column O that has data in it. By the looks of the code it appears that column O is a comment field that may only occasionally be being filled out. Change this letter to a field that is always filled out. That way, when the statement searches that column, there will always be an entry in that column in the last row.

If there isn't a column that will always be filled out, make one by always writing "*" or "X" to some column when ANY entry is made. Then you can search that column and be sure of finding the last used row because every entry has something in that row.

Also, just a helpful comment on the layout of your entry fields; the default mode of Excel when you press enter, is to move one row down in the same column. If you use, D6, H6, D8, H8, etc as entry fields, you will use 4+6=10 keystrokes to enter every two fields. That can be a lot more keystrokes per day if a lot of entry is going on. Whereas if you stack the entry fields in column E and put titles beside them in column D, one tap of the enter key will take you to the next field.

Please give me a post back and let me know how you make out.