PDA

View Full Version : Problem with trying to create a data entry screen template



nano999
11-19-2012, 02:42 PM
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 :

StartDate RETURN# RETURNStatus WarrantyStatus
Client ClientEquipment ReportedProblem S/N DateSentforRepair DateSenttoCustomer AdditionalComments

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

Kenneth Hobs
11-19-2012, 03:17 PM
Welcome to the forum!

Data Form may meet your needs.

What you ask is not trivial if you don't use Data Form.

For more advanced methods see: http://www.vbaexpress.com/forum/showthread.php?t=36057