Problem with trying to create a data entry screen template

[nano999]

Hi all,
I am trying to create a data entry screen which will allow users to
update any cell in a worksheet I have already made and also search by any
column name in that worksheet. The 11 columns I have for the worksheet
from left to right are :
Start Date RETURN # RETURN Status Warranty Status
Client Client Equipment
Reported Problem S/N Date Sent for Repair Date Sent
to Customer Additional Comments

What I want to do is to have this spreadsheet hidden on my pc and only
available to me so I can prevent other people in the office from going
into it and accidentally deleting a record. I think there is a way in
excel 2007 to create a publicly viewable data entry screen box for the
other people in the office and to only have that available to them and to
have it linked to the spreadsheet so all they see is the entry screen box
and not the actual spreadsheet.

I want to place a shortcut on everyone's pc desktop called Return Log and
when they launch the Return Log shortcut it will only pull up the data
entry screen template which is linked to the actual hidden spreadsheet.
The data entry screen template should allow the user to:

-search by any of the column headers above (for example Start Date,
RETURN #, etc...)
-update an existing record's cell
-automatically assign a new RETURN # everytime they click on the "NEW"
button on the data entry box. The first one could be R0001 which
corresponds to the first record entered, and then the next one could be
R0002, R0003, etc. It would add a new record from the top of the
spreadsheet at A1 and then when you click the "NEW" button it will add
the new record at A2, and then A3 for the next new record, etc

Basically I'm trying to create a simple data entry screen which allows
users to search by any of the columns listed above or update a record

I played around with an add-in called "J-Walk Enhanced Data Form" but the
problem with that add-in is when you click on "New" it adds a new record
to the very top of the spreadsheet at A1 and pushes the record you
already have below it to A2 so the older records keep being pushed to the
bottom of your spreadsheet and the newest record is always at the very
top A1 cell. It also has no search feature built into it which would
allow a user to search by column name (for example RETURN #)
I'm not proficient enough in VBA to write this although I suspect that I
would need to use VBA to create some sort of a userform to pull this off.
Does anyone know how to do this or can anyone lead me in the right path?
Thank you
J

[Ken Puls]

First thing I see here is that it sounds like you want to essentially create a database of records. If that's the case, Excel is not what you want for this, you really want a database.

I've built a lot of databases that use Excel as the front end, but I'd never try and link everyone in the office into a workbook on my PC. It's just too rife with perils for my taste.

If you're not afraid to learn, and you have the time to do so, I can certainly lead you along. It sounds like you've get a fairly clear idea of what you want, which is good, but you'd probably still need to flesh it out a bit more, and probably provide us some samples to get it working.

Basically what you'd need in order to do what I'm thinking is:
-A copy of Microsoft Access (or someone who can build the basic database -- providing it's not too complicated, I could help with that)
-An understanding of exactly what data needs to be captured
-An Excel template to capture your data
-A routine to push that data into the database (like this one here)
-A routine to pull the data from the database back to Excel (like this one)

With that, you can create a template that you can give to users. They can fill out the key pieces and upload new data into the database. You can then sort it however you like with a simple SQL query. In addition, you can also extract data, allow them to modify in Excel and re-upload. And you could also add the ability to remove an entry, but require a password to do so.

The benefits of the database are:
-That is what they are for
-You can then pull your data directly into PivotTables to report on it
-It's far less likely that someone will get into it and muck with it than a spreadsheet

Optionally, you could even role up your templates into a package, modify the ribbon and deploy it as an application quite easily.

I woudl certainly help you with this, but I'll warn you first that I'm busy, I'm not on everyday, and youd' be stuck with waiting for me if no one else came along to chip in.

[nano999]

Hi Ken! You are absolutely correct in the way you perceived this and I am currently using Access and Excel 2007.

What would be ideal is to create an actual Access table and then import the existing Return Log spreadsheet (not workbook as it's only 1 worksheet I'm using) into that access table. Is that what you mean by an access database? forgive my ignorance in this...

I like the idea of having the access database be the back-end and then to have an excel template (like a userform for example) be the front end of that access 2007 database rather than creating an excel spreadsheet and placing it on my pc and having everyone have the ability to get to that excel spreadsheet.. that way, as you correctly pointed out, it prevents multiple users from tampering with the database fields and creating a situation rife with perils.


Im not proficient enough in SQL (yet) to be able to write queries but I would like to learn this if I would need to. I like the idea of creating a pivottable to create a report based off the records I pull from the access database. is there a way to automatically create a report button on the userform so that when it is pressed a report of any recored can be created?

also, as for the requirements:

-A copy of Microsoft Access (or someone who can build the basic database -- providing it's not too complicated, I could help with that) <--I MAY TAKE YOU UP ON THIS. HOW MUCH WOULD YOU CHARGE FOR THIS?
-An understanding of exactly what data needs to be captured<---THIS I KNOW. THE DATA THAT WOULD NEED TO BE CAPTURED (INPUTTED) INTO THE USERFORM IS IDENTICAL TO THE FIELDS I MENTIONED ABOVE (for ex. Client, Reported Issue, etc)
-An Excel template to capture your data<--THIS I DONT KNOW HOW TO MAKE. IM A NEWBIE TO VBA. I THINK A TEMPLATE WOULD BE GREAT AND EASY TO USE FOR ANY USER IN THE OFFICE
-A routine to push that data into the database (like that one)<--I THINK I UNDERSTAND WHAT YOU MEAN.THIS LOOKS LIKE A VBA ROUTINE USED TO PUSH DATA WHICH IS INPUTTED INTO THE EXCEL TEMPLATE WHICH WOULD THEN GO INTO THE ACCESS DATABASE
-A routine to pull the data from the database back to Excel (like that one)<---THIS LOOKS LIKE A VBA ROUTINE TO PULL DATA INTO THE EXCEL TEMPLATE FROM THE ACCESS DATABASE WHICH WOULD THEN APPEAR IN THE TEMPLATE. FOR EXAMPLE IF A USER WANTS TO PULL UP THE CLIENT'S INFORMATION IT WOULD EXTRACT THAT DATA FROM THE ACCESS DATABASE AND DISPLAY IT ON THE SCREEN. THE WHOLE RECORD COULD BE DISPLAYED.

[Ken Puls]

Hi there,

Yep, you're following me, only one thing to clarify...

And Excel template is nothing more than an Excel workbook that is saved as a template. (xltx or xltm format.) The purpose of a template is to ensure that when a user opens it up, they can't save over the source file. (It will automatically open a SaveAs prompt.) By setting up in this way, we make sure that the Excel front end is always as it should be. (The secret is that a File->Open will open the file in Edit mode, where creating a New file off the template will not.)

Personally I find that it's often easier to just create an Excel workbook as my user interface than a userform. I get all of the goodness of the whole Excel toolset to work with, and can even validate my data before it's pushed to the database.

What I'd suggest at this point is that you do two things:

1) Using some dummy data, build a worksheet that you would use to capture the data. Once you're done, upload that here and we'll take a look at it. Make sure that the field names are consistent with what you need though, as I'll help you build your access database off that.

2) On other worksheets, build the reports that you'd like to see come back. (If you'd like PivotTables, give us a few lines of data that you then turn into the PivotTable.)

With those, we should be able to create your templates and a database structure.