Userform signing out equipment - Table searching help

[Battletoads]

I have a userform that allows users to sign out items and return them whenever; all data is put onto a table

When they return it, they just put their name in, the item the want to return, and the code searches the table for their name and the item and inputs the date they returned it a few columns over.

The problem is, I can't get it to work. Like I said, i'm using a search function to count through the rows and match up the inputted name and item.

The most frustrating thing is everything worked a while ago but I recently added more columns to the table and I thought I changed everything correctly, but it seems I haven't, and I can't seem to find where I messed up.

Example: NameA takes out itemA and then returns it. A messagebox should appear saying the item was returned, but it doesn't appear. It also doesn't sppear to be returned on the table itself.

That's the main problem. I'm also having a problem with the error messagebox. If NameA tries to return ItemB without ever signing it out, a message box should appear, but it doesn't. Not too concerned because it shouldn't be a problem as long as staff don't purposely do this.

Lastly, how can I make sure the "date to be returned" is always consistent in it's format? I have the cells formatted as Long Date but inputting different date formats seem to make it stay as is.

Thanks, any help is appreciated.

[patel45]

you assigned Label "Equipment" to Textbox "txtPrice"

[Battletoads]

you assigned Label "Equipment" to Textbox "txtPrice"

On the userform you mean? That's just to put a rough value to the item being lent out

[HaHoBe]

Hi, Battletoad,

do you still need help on the issue as this thread is marked Solved?

I have a couple of additional questions/remarks prior to starting to work on the code (a couple of these might call for a re-design of the UFs) and would do so only if you come back and there still is some need for help.


You have given names to the ranges but you should exchange Names with Employees as Names is a VBA Keyword. And you have used fixed ranges so any change in Names, Items, Programs will cause you to change these ranges whereas adding a dynmaic range like http://spreadsheetpage.com/index.php...nter_new_data/ might help.

You want to enter the information about the loaner into the UserForm while I would prefer to create a list of addresses and choose from there (holding a unique number for each client which may be referred to). You might use a List- or ComboBox to choose from with a hidden first column holding the ID and the second showing the full name and getting the information for the other fields via VLookUp). You may even add an entry into the database via another UF.

The items to loan are plainly listed in your approach but I would go with the same concept here as for the clients using maybe the serial number as an ID together with the full name as you may have a couple fo items from the same brand.

You may even create an additional list of prices for the items based on duration of loan and maybe a category for each individual loaner.

For returning date I would prefer to use some kind of DatePicker (there are samples on the net for creating a UF to fulfill this task). You should maybe have a combobox instead of a fixed date to be entered giving you a bit more flexibility on the returning date and leaving it up to the code to format the date.


Since you want to enter the data into a Table you canīt use the commands like for entering data into a nonformatted list. Using the End(xlUp) command will bring you to the last cell in a given column in the Table but it will also do this if the last rows in the Table are not filled with data. To get the last filled row of data inside a Table you would need to start at the header of that column and use End(xlDown) and work from there. Same goes for entering new data. You would need to add a new line to the Table if needed and not write the data to the first line after the Table.

Please Login or Register  to view this content.

might help to add a new row to the Table if needed.


Regarding the return of items I would imagine a different approach. You would need the name of the employee as well as of the client there and a list of items the client has loaned within a List- or ComboBox to choose from (make multiple selections available as well as it would be the same date of return for the items. When filling the box with data I would use another hidden column for entering the row number of the item in order to write data to if returned. Maybe I got the concept wrong but I would make it loaner based and not employee based as I imagine every employee to enter returned items no matter if she or he was the person to hand it out. Please correct me if Iīm wrong on this.


A lot of text (and hopefully not too many typos in here - you might keep them if you find them, Iīve got more than enough still to spend ).

Ciao,
Holger

[Battletoads]

Thank you for the suggestions Holger/

I'm making this for someone so it would depend on what they want as well as my own abilities as a coder but I will definitely try some of your suggestions out.

[HaHoBe]

Hi, Battletoads,

feel free to come back and ask if you would need help on an idea or with a code. And you should sit down by yourself and make a plan on how you want the application to look like and talk with the people about it as well: if they donīt feel comfortable with the look or the working flow thereīs hardly a chance they will enjoy using it (which means a lot of error handling needs to be included).

Ciao,
Holger