+ Reply to Thread
Results 1 to 13 of 13

VBA Data entry userform.

  1. #1
    Registered User
    Join Date
    11-25-2016
    Location
    GZT
    MS-Off Ver
    2010
    Posts
    7

    Exclamation VBA Data entry userform.

    Hello Guys

    First of all thanks to everyone who is reading this and trying to help me out. I actually want to briefly explain what do I need so you can share your ideas and I hope we can come up with something since I need to finalize it until Monday. So well lets get it started i guess

    I have a database already and it is a leave tracking sheet basically and I am an HR Officer. So we are upgrading our database to a whole new form which I already created the database and the functions etc but the main thing I want to have is an user friendly interface which can make everything a lot easier. So what I want to make is a VBA interface which will allow me to enter dates and leave type automatically also the database that I have is a little bit advanced so I want VBA to navigate between dates by one click or one entry. I personally want to admit that I am pretty good at excel but about VBA I have so limited knowledge. So if necessary I will share some part of my database for you to check it out and give me some idea about what can i do.

    Any help highly appreciated.

    Kınd regards
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA Data entry userform.

    Hi,
    Your idea is understandable (in general). Sheet Database will be full of data and Sheet Interface you want to use for "smart navigation", right?
    BUT I can`t really see the question in your post
    How exactly you want to build this "navigation"? There are many ways to do this - UserForm, Formulas, ComboBox & ListBox...?
    Please specify what ecactly do you want from us, unless it look`s like "here is my file - please do some magic"

  3. #3
    Registered User
    Join Date
    11-25-2016
    Location
    GZT
    MS-Off Ver
    2010
    Posts
    7

    Re: VBA Data entry userform.

    Hello there

    For sure I am not seeking for magic I am just looking for some creative ideas. Let me explain that again so maybe it will be more clear

    So basically what i want to do instead of running around all of the dates a kind of userform that I can make search by name,id number or reference number which we use on the payroll so we can enter the leave by putting an "s" for sick leave or "l" for an annual leave. Which is basically like search and change function. But I want to do it in a kind of interface so assistants would be able to enter the leave dates and type with ease. I hope it is a bit more clear now It is not actually here is my file please do some magic but more like an idea or how to do it because i am not really familiar with VBA so much. I know what I want but I do not know how to actually achieve it.

    Thank you for your interest and i hope this makes sense now.

  4. #4
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA Data entry userform.

    Try this sample of search by No. Enter numbers from 1 to 3 (3 test customers are in Database) and press button.
    As I understood you need to make some updates from "Interface" - what really should be updated?
    Let`s say we found record number 3 and want to mark current person as Sick from 20/Nov/2016 till 25/Nov/2016.
    We choose Option = S, From = 20/Nov/2016, Till = 25/Nov/2016 - then press Update button (not working yet) - and all cells in current person row between mentioned dates should be filled with "S" (see Database sheet)?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-25-2016
    Location
    GZT
    MS-Off Ver
    2010
    Posts
    7

    Re: VBA Data entry userform.

    YES! You read my mind actually. But as you can see in the database it is a calendar version with a lot of functions in it. So when we want to make an update we need to navigate to all pages but instead in the interface tab I want to have something like that. That can just perform a search for me and make a match and we can type the data that we need to enter. BOOM Magic. It will be there. But what I want is not the start date and the end date. You see the calendar in the database? It actually means one day for one staff member. Imagine you have taken an annual leave on 11/25/16 so I need to go to spesific date and find your name and put "L" but if we can make an interface that can match the row that we have your name with the column with the date we specify and if one click we can change it then it will be awesome and for assistants it will be very clear and easy. Like this it takes tons of effort to update. What you do is really fine but as I told you we need to match with calendar. Their start dates and end dates is what we already have. We need to enter their annual leaves.

    I do not know if it is too much but i really appreciate your help very much.

    I guess it will be something real good

  6. #6
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA Data entry userform.

    Well, now you can seach by one of 3 criterias (No., Name, ID). After person if found you can choose one of "Options" and enter Date (note that Calendar is for 2017).
    Button Update will send Option letter to the persons row under Date. Please test it.
    This version definatelly is not an ideal (there are no any tests on wrong data), but I think that it contains main functinality that can be improved
    Let me know if it works and if we need to add some new "whistels" to this file.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-25-2016
    Location
    GZT
    MS-Off Ver
    2010
    Posts
    7

    Re: VBA Data entry userform.

    Hey buddy thank you for sharing with me the excel sheet Looks much better right now. Only there is one problem that I am getting a runtime error constantly and I am not really able to update anything. What is the cause of this runtime error?

  8. #8
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA Data entry userform.

    Are you gettin error only for update? Or search does not work either? I got errors trying to make update too, before I understood that I'm trying to make update for the date that can't be found in calendar. Calendar is for Y2017. Try to make update with some date from 2017

  9. #9
    Registered User
    Join Date
    11-25-2016
    Location
    GZT
    MS-Off Ver
    2010
    Posts
    7

    Re: VBA Data entry userform.

    Well actually I fixed the error that is fine now. But I would like to ask for some more things and if they are not a problem for you we can make those but if it is a problem we can let it go

    1- Is that possible to add a date range for example from 7th of October up until 10 th of October? Also I tried to add a pop up calendar but didnt work for me I guess I am so noob in this.
    2- Can we make the last rows only show up without updating them? Such as Leave Taken Number of Months Number of Days Earned Remaining Sick Leave Sick Leave Taken Sick Leave Balance?
    Only to see them not to make changes like when we make a search near the other things can that come too?
    3- I appreciate all of your efforts and just wanted to let you know what you are doing for an humanitarian organisation which makes it more precious I guess

    Thank you very very much and if we can do those also I will be sooooo grateful.

    Regards

  10. #10
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA Data entry userform.

    Hi,
    What was this error?
    1) Possible, I`ll implement update on a range base + will see that can we do about faster date pick up.
    2) I think it`s possible, but I didn`t fully get your idea, so please make sample excel sheet with all rows/columns/info/ ... you want to see + short comments (data source/should it be updated or not ...)
    3) Thanks for good words, I`m pleased to help (like everybody on this forum )

  11. #11
    Registered User
    Join Date
    11-25-2016
    Location
    GZT
    MS-Off Ver
    2010
    Posts
    7

    Re: VBA Data entry userform.

    So actually while we are entering the date instead of entering one by one I would love to have a date range option.

    And also what i mean is that you can see at the attachments. Just like while we are making the search we could have also the remaining balance of the same person. So we would not need to go and check on the database.

    If we could make those slight changes I think mostly it will be ready to work on it.

    Since they are auto formula they should not be updated but we just need to see the data there. Just like your search bar. But with more data

    Well thank you very much for offering your help to me it is highly appreciated from my side.

    P.S Search by ID Number and name is malfunctioning so hard. I Could not figure out what is the problem there too. Just to let you know
    Attached Files Attached Files
    Last edited by holdmyhand; 11-29-2016 at 04:39 AM.

  12. #12
    Valued Forum Contributor kasan's Avatar
    Join Date
    07-22-2009
    Location
    Riga, Latvia
    MS-Off Ver
    Excel 2010
    Posts
    680

    Re: VBA Data entry userform.

    Well, I tried to use this Search & Update on a sheet.. and I totally didn't like it, so user unfriendly and I got your idea about difficulties with search by name or ID.
    Thats why I created UserForm with textboxes and buttons.
    Now this tool can:
    1) Search by any criteria or even without criteria (you will get full list). You can make search by part of name (ex. joh) and get all results containing this text (like John Andersen and Andrew Johnson), search is NOT case sensitive, so you can write it as you wish joh, JOH or Joh - whatever.
    2) Result of search will be represented in ListBox below, this list box contain only main info because...
    3) ... when you click on one of results - you will get full info below (according your last attachment)
    4) When you are sure that picked person is the right one ...
    5) You can choose Option (I don`t know how to call it correct ) from Combobox (L, S, SH ...). In case option will not be picked - you will be noticed and update will not happen.
    6) Then you can easily choose Date From and Date Until (chosen Option will be filled for current person between those dates). When you choose Date From - value of Date Until will automatically changed to the same date (assuming that nobody can take day-off from 05.01.2017 to 01.01.2017 ). In case dates is picked wrong, let`s say you will pick some dates in Y2016 - nothing will happen, because those dates will not be found in Database. You will be noticed about this by MsgBox.


    I`ve tested this tool, but probably I missed something and.. I'm really bad designer so if you don't like visual of UserForm - you can move elements within Form as you like.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    11-25-2016
    Location
    GZT
    MS-Off Ver
    2010
    Posts
    7

    Re: VBA Data entry userform.

    Thank you very much!!!! It looks Great To be honest. I actually have one problem that I cant open anything on my computer it says that the object could not be loaded because it does not exist in this machine. Do you know how to solve that problem also? I am really sorry I am such a big pain in the butt but I really have no idea. The previous ones that you have sent was working very well. But here we have that problem. Could you please help me out with that one?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Userform for data entry
    By bqheng in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-05-2016, 09:35 PM
  2. Userform data entry
    By stevipop in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-27-2014, 04:12 PM
  3. Replies: 0
    Last Post: 02-04-2014, 12:36 AM
  4. Data entry using a userform
    By j_Southern in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-04-2010, 08:59 PM
  5. UserForm for data entry
    By jay11 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-15-2010, 03:30 PM
  6. userform data entry
    By john tempest in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-06-2006, 10:45 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1