Starter User Form

[chef]

Hi there
I'm trying to get my head around making a user form and getting confused how it all glues together from google and utube various code.

I have made a form called UserForm1 and I'm just starting with 6 entries into a sheet called data.
On my UserForm1 I have the following

cmb1 - I need the choice to be entered into sheet called Data in cell C5
tb1 - this is date from label and I need format to be "dd/mmm/yy" and to go to cell E5
tb2 - this is date to label and I need format to be "dd/mmm/yy" and to go to cell F5
Lb1 - this is a list box i made and I need the choice made to go to cell H5
tb3 - this hours claimed and should be a value and entry should go to cell I5
tb4 - this is hours claimed and should be value of either 15,30 or 45 and then go to cell J5

I then need the form to clear once an entry submitted so another can then be made and then find the next blank cell down within sheet Data for next entry

I would need a submit button
One to open form and one to close

I have the form set up but I cannot seem to get the code to make this work and hoping someone would be kind enough to help.
I'm trying to understand better and think just need lick start and then I will expand and learn more.

I have spent many hours trying all sorts and am now just confused as I'm using bits of code from various sources and none gels.

I'm hoping this would be easy enough for someone for these 6 entries and help me on the rung of being able to utilise user forms.

Any help at all would be greatly appreciated
regards
ian

 

[mikerickson]

Since you are starting. Get rid of the idea that a textboxs are "formatted". They only hold strings. If you keep that in mind, that "29/May/20" is NOT A DATE when its in a text box and that you have to convert it to a date for other use. Keeping that in mind will make things much easier down the road.

I would suggest that you start by coding the Submit button to put whatever is in those controls into those cells, without attaching any meaning, just getting the strings where you want them to go.

You will also notice that coding a combobox is almost identical to coding a listbox.

What code are you using to fill the comboboxes and listboxes. Also what code does your Submit button trigger.

 

[chef]

Thank you for quick reply.
I have not coded combo/list box but put named range in source row on properties box?

to fill the combo box I have row source as nameb as I used a named range from another sheet
same with list box and that row source is called categories

I have no code at all now on submit as Im not sure where any of it went on the boxes?
So would you be kind enough to help start me off with what code I should put on the submit button to get the 6 entries into proper cells

 

[mikerickson]

To put the selected item from a combobox into a cell, you would use code like. Similarly for other controls.

ThisWorkbook.Sheets("Sheet1").Range("A1").Value = ComboBox1.Value

 

[chef]

ok thanks for advice re text box and strings and no worries.
I'm still no further forward as not fully understanding how to get my 6 entries into cells I want on sheet called data and entries thereafter.
Not sure what I would do with that one line or where code goes on buttons etc..

I'll go back to utube and try examples again and see if I can get a workaround that helps my particular dataset.

 

[chef]

Is anyone else able to help me at all with actual code to get my user form working.
I'm starting with 6 entries and just needing a helping start to I can learn from one that works and I can understand better.
Thank you.

 

[LenaH]

I have created a workbook for you, since I know exactly, where you are coming from.

Lena

 

[chef]

Thank you so much. any help at all is greatly appreciated as I;m just looking for a start so I can then learn from.
I'm a little confused how it's all glued together re code so a workbook that has code will let me see what I need to start to do for other forms I want to use and develop.
Are you able to cut and paste and share the code with me please?

Thank you.

 

[chef]

Many many thanks Lena and I can now understand the rationale about what goes where and this will help me to start and develop.
Was looking for exactly that and just a gentle nudge and help with something I was unsure of, or would not have asked for help.

If there is anything I can do to help people, I try my best to do so within the parameters of my knowledge and I find my learning method is to work with an example so I can visualise and understand better.
You have saved me soooooo much and I will now expand on this and just grateful I have a working model to work from and I can now see how some of this all links together.
Extremely grateful for your time and effort to willingly help someone.

Kindest regards
ian