Search Bars and Data lookup issues in Movie Rental

[Dinger045]

So attached I have a preliminary thought process of what I am looking for. I want it to be easy to use and monitor and organized. So Sheet 1 you will be able to select a movie on the list in sheet 2 from the box on Sheet one.....kind of like a lookup tool. Then I want the rating to appear with that movie selection.....then I want the A/U to load to say whether the movie is available or unavailable......and then once we put a number into the apartment number...the date loads and it is labeled as rented.....

So heres the deal....I don't know how to do any of this....I don't know how to do the search box or the vlookup for the rating....Pivottables I am fine with....but I also want the menu on sheet one to update data in sheet two for columns C and D.....


Does this sound possible to anyone....Anyone at all....If you could please do some magic and see what updates that you can make it would be greatly appreciated.Movie Rental.xlsx

[FDibbins]

See if this is something you could build on? I added anothetr category - genre - with a drop-down to pick that. A list then gets populated based on which genre is selected.

If you are good with this, we can try and build other stuff in to for you?

[Dinger045]

Did u forget the attachment?

[FDibbins]

hmm I could have sworn I attached it

[Dinger045]

I like the Genre Category, will make it easier for residents to pick I suppose, just have to make sure I assign the right genres to each movie now but that neither here nor there.....Now a google style search bar that brings up whether or not the movie is available to rent......its a search box but would it be a vlookup script?? I cant do it....but I want to learn while I am receiving this gracious gift from you!

[FDibbins]

OK I took it a bit further for you. I added some names names and dates. Take a look and let me know

[Dinger045]

Is there a search bar...I mean sheet 2 makes sense.....but sheet one isn't doing anything....I cant search or do drop down menus or anything.....or is there nothing there???

[Dinger045]

Like I want the Blue Box to be the one all stop all for the leasers. they type in the title....It says Avalable unavailable......then if its available a drop down yes or no for rent this movie.... put in the apartment....then the date autoloads......and all this data transfers to sheet two.....
then I use sheet 2 to create a Pivot Table on sheet one...

I want to select the genre....and start typing a title into the box under title and it kinda auto fills in like google does.....and then the yes no portion under rent this movie will be a drop down menu that fills in the date automatically and we just enter the renters number

click type click click type Done
updates data and pivot table

[FDibbins]

they type in the title....It says Avalable unavailable......then if its available a drop down yes or no for rent this movie

already done, see notes in upload

put in the apartment....then the date autoloads......and all this data transfers to sheet two.....

will need VBA, hopefully another member can help with that

[Dinger045]

I don't get it....It seems to be working backwards....Like I feel we have to update sheet 2.....

[humdingaling]

is this more what you were after?
type in the textbox and it updates the listbox

click on the listbox and it updates the bit in purple
i got rid of genre because well the coding behind it would be more difficult and im not entirely sure it is needed

not sure on the updating thing....
you cant have one cell to both receive and update information unless there is so trigger
you could probably add in some command buttons to trigger it

ps...this does not take into account duplicates and assumes there is only one version of every movie
if you have duplicates you need to tell me how you want to handle them

[Dinger045]

So the search bars seem to be doing two things....one overlapping....and then another making the top part scroll down and disappear. The overlapping is displayed by the red arrow.

The red portions of the table I want to be like if it is available then all they have to do is like click a button to say its being rented....a Command if you will to update sheet two and then another to enter the apartment number on the green menu part which goes to sheet two and the date autopopulates when anything is entered into the date location.movie rental 2.1.xlsm

[humdingaling]

ok i think i fixed the auto resizing of the listbox but i dont know how to stop the screen moving once you click listbox
ill have a look later to see if i can correct it properly

the work around to that is having a smaller listbox that fits the screen in its entirety

not sure what you wanted with that green area
i added a button in there to click when you want to rent out
if no movie is selected msgbox pops up
if movie is already rented msgbox pops up

[Dinger045]

AWESOMESAUCE!!! One last request....Is there a way to make a button to check the movies back in. A button macro that will clear the data from Sheet 2, to make it available to rent out again.

Thank you all sooooo much for your time and dedication to this. You really don't know how much it means to me!!

[humdingaling]

how do you want to check back in
just clear the date and apartment?

[Dinger045]

Well we would like to keep record of the last person that had it.....so...take the current number and move it over one column to the right in sheet 2. That way if the next person who rents it realizes there is a problem.....we can charge who had it last. So yes but just have the cell info get transferred over......

If that is not possible....just clear out the apartment number so it can be rerented.

[humdingaling]

check out the latest version to see if it does what you want it to do

check back in button has been put in
copies whoever is renting it out now and puts into two column next to it on sheet 2
clears out the data on both sheet 2 and sheet 1
changes it to available incase you need to rent out straight away

[Dinger045]

Thanks so much.... this is perfect. I sincerely appreciate your help!!

[humdingaling]

glad you like it
good thing with things

cheers
Hum