populate a sheet through a userform

[GrabberHackman]

Hi I need a hand in finishing this userform, we accept suggestions on how to proceed, I need you to fill in the yellow cells, also if it is possible to do so, I would like to insert a condition that if you use the SELEZIONA ALIMENTO combox you will not can use the RICERCA ALIMENTO and vice versa.
Finally I would like to use the command botton to insert the foods in the respective cell both when using the search and when selecting the food through the comboboxes

To launch the userform just double click on a part of the sheet and it appears
the userform is composed of a tetxtbox with text search + list box connected to the food database sheet, where the list box is connected to cell f12, again in this userform there are two other comboxes which allow me to recall the categories and the subcategories and using the commandbutton values ​​are entered in cell f11



ps I think that to do this I use a ListFillRange in reverse or a for each loop

[Greg M]

Hi there,

I've just had a VERY quick look at your workbook, and have included Option Buttons for selecting either "Seleziona Alimento" or "Ricerca Alimento". Selecting one option automatically hides the ListBox/ComboBox for the other option.


Part of your code has been modified as follows:

Please Login or Register  to view this content.

The attached workbook shows how this is implemented.

Hope this helps.

Regards,

Greg M

[GrabberHackman]

thank you very much, I had not thought about the option bottons, which work as true and false, they are a beginner

[GrabberHackman]

I need you to fill in the yellow cells,Finally I would like to use the command botton to insert the foods in the respective cell both when using the search and when selecting the food through the comboboxes

Please Login or Register  to view this content.

[Greg M]

Hi again,

This was interesting, so I took some time to "play" with your workbook, and have rewritten some of your code to provide better encapsulation - i.e. the CodeModule associated with the UserForm does not "know" anything about worksheets, columns, ranges etc. Similarly, the CodeModule associated with displaying the UserForm and updating the worksheet does not "know" anything about ComboBoxes, ListBoxes, CommandButtons etc.

Double-click on any of the "Alimento o Ricetta" cells to display the UserForm.

Data are copied to the worksheet by clicking on the "Inserisci Alimento" button - this applies to selections made either from the "Seleziona" ComboBox or the "Ricerca" ListBox.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[GrabberHackman]

fantastic job I don't know how to thank you, I would offer you a beer, but simao a bit distant. Really amazed, I would like to understand a few things about it, the first, how did you set the opening on that particular line, I used "if not intersect" where I assigned the range of cells to write, so I just had to click in the row and assign the food. another question it is possible to add a button to delete the last food inserted, and another one with the name reset that deletes the entire list where the userform works.

I just uploaded it to my spreadsheet and I must say it is sensational, truly a masterpiece. I've been playing around with it for a while and ask if you can kindly make these changes

Another change, always if possible, is the one linked you have DEFINED NAME if I would like to display them without underscores I think it is not possible because they refer to the number given by the program?

moreover, if it is not too much trouble and it is possible to add a scroll bar that moves on the food plan sheet, in this way it is possible to change the previously inserted foods or delete a specific food

[Greg M]

Hi again,

Many thanks for your feedback - nice to see that we're moving in the right direction!

I've added the two buttons in the attached workbook - see what you think.

Sorry, I'm not really sure what you mean when you say:

I would like to understand a few things about it, the first, how did you set the opening on that particular line, I used "if not intersect" where I assigned the range of cells to write, so I just had to click in the row and assign the food.

Which particular line did you mean?

Hope this helps - as always, please let me know how you get on.

Regards,

Greg M

[Greg M]

Very quick partial response!

I saw that if you change the wording "Alimento o Ricetta" the userform does not start, if I would like to change the wording which string should I change?

Please Login or Register  to view this content.

More later - adesso sto per prepararmi la cena!


Regards,

Greg M


P. S.

Another change, always if possible, is the one linked you have DEFINED NAME if I would like to display them without underscores I think it is not possible because they refer to the number given by the program?

I think I'll be able to arrange this one also

[GrabberHackman]

you are a special person, have a nice dinner and take your time, I also found the really brilliant string

[GrabberHackman]

I had not seen this message, spectacular the two buttons that also make the summary of what you want to delete

my old userform I started the user form like this, in this way I didn't have to create other code to insert the selected data since with the double click I had already selected where to write, but yours is much more professional

Please Login or Register  to view this content.

Another change, always if possible, is the one linked you have DEFINED NAME if I would like to display them without underscores I think it is not possible because they refer to the number given by the program?

moreover, if it is not too much trouble and it is possible to add a scroll bar that moves on the food plan sheet, in this way it is possible to change the previously inserted foods or delete a specific food

I am attaching these two videos to show you these things that written they would not be able to explain to you, if you can manage them as you see fit, thank you very much for what you did
youtu.be/FEuPc2UNuPk
youtu.be/q88IjGXIuJ4

[Greg M]

Hi again,

Many thanks for your feedback. Now I understand what "particular line" you were referring to!

The attached version of the workbook has removed the underscore characters from the "Categoria" ComboBox, and enables/disables the "Inserici" and Cancellare" CommandButtons as appropriate.

When you say:

moreover, if it is not too much trouble and it is possible to add a scroll bar that moves on the food plan sheet, in this way it is possible to change the previously inserted foods or delete a specific food

do you mean to place a scrollbar on the UserForm which navigates through the ten worksheet rows associated with the current selection?

Hope this helps - as always, please let me know how you get on.

Regards,

Greg M

[GrabberHackman]

good morning my friend, I was amazed again, great job, I have attached two videos that show you some improvements if it is possible to make, as far as the scroll bar is concerned I wanted to insert it in the userform so that it moves up and down in the sheet. I am attaching another video that also shows this detail to be improved


------https://youtu.be/ZhQozwRKHbg----------

[GrabberHackman]

do you mean to place a scrollbar on the UserForm which navigates through the ten worksheet rows associated with the current selection?

yes, just that

better to insert gods "Previous" and "Next" Command Buttons, no scroll bar

[Greg M]

Ok, I'll see what I can do tomorrow or Monday.

Greg M

[GrabberHackman]

thank you so much take it easy

[Greg M]

Hi again,

Take a look at the attached workbook and see what you think.

It uses a slightly different approach. The UserForm now contains a ListBox with ten rows - the contents of the ListBox can be added to or deleted, and when the contents of the ListBox are as required the "Inserici Alimenti" button is used to copy the contents of the ListBox to the appropriate rows on the worksheet.

Displaying the UserForm while selecting an "Alimento o Ricetta" range which already contains some values will cause those values to be displayed immediately in the ListBox.


Hope this helps - as always, please let me know how you get on.

Regards,

Greg M

[GrabberHackman]

First of all, good evening Greg, just tested, and you did an AMAZING job, my reaction was this you left me "OPEN MOUTH". I play around for a while to see if there are any bugs, but with this method you have solved everything. thank you again for what you did.

[GrabberHackman]

hello greg you can make the search no case sentitive, because I explain to you, if I insert the foods with an initial capital letter, it gives me more records while if I search for foods without the initial capital letter fewer records come out

Then I wanted to do the comments for the Italian, with which you wrote the vb code. By chance if he was born in Italy?

[Greg M]

Hi again,

Many thanks for all of your feedback and your compliments

Sorry about providing a case-sensitive search facility - I should have thought of that

Just make the highlighted changes shown, and the search will be case-insensitive:

Please Login or Register  to view this content.

As far as being born in Italy is concerned, I was born in Ireland to Irish non-Italian speaking parents, but when I retired I decided to try to learn to speak Italian - che lingua meravigliosa!


Best regards,

Greg M


P. S.

The following alternative method is probably better than the one I suggested above:

Please Login or Register  to view this content.

[GrabberHackman]

In time I renew my congratulations, every time I use the userform, I understand that behind there is a work of a super professional, as for not having thought about the search method used, I don't think it's a problem, given the result obtained with that little string of commando.Ti renew my congratulations and if you decide to come to Italy, I invite you to be my guest, so we can enjoy a little bit of the wonders of Italy.