Results 1 to 4 of 4

Thread: Problem with trying to create a data entry screen template

  1. #1

    Problem with trying to create a data entry screen template



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    that makes sense...

    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.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •