Excel VBA Userform Date issue

Lastly can I create a list on a user form which adds items each a new item is added from a combobox list without creating a complete new record every time a new item is added- thinking of a way to explain

• it would be like shopping online where your online shopping basket acts like a list box and then copies all those items when your finished onto the worksheet when you press SAVE

That is a complex problem, but solvable.

You need to create a database in memory, I suggest to use a Scripting.Dictionary as container. (You can also use an array or collection or temporary sheet, but IMHO that is much more complicated).

Inside that container, each record has to be an array, for your case you can use a simple one dimensional array, because that can be written very easy into a row in a sheet.

All your routines inside the form must use this array (record) instead of cells to store the values.

If you're done with the input in the form, simply add the record to the container. Update the Listbox with all keys that are currently inside the container.

Finally if you want to save the data to the sheet, you have to get all data from the container and write each array into a row.

I am not sure if this guide will help you, in any case it means that you have to discard your previous code.

Andreas.

Adrian,

I don't know how I can help you there.

I thought I made it clear and showed you the errors that are already in your code and in your database. I showed you how you can use and evaluate a real date instead of a text.

You have declined this and want to use text and the solution that Ossie suggested. That's ok for me.

But, IMHO, this solution will never work for this purposes. So I can't give you any suggestions, I'm sorry.

Andreas.

Hi Andreas

I think im well aware of how to use a change event thank you 

My question is regarding the code i need on the user form - also the other point regarding the list box please

is there away to carry out that calculation while the user is inputting the data for each each a new incident record is generated?

You can use the Change event of a Textbox to execute a code while the user is typing.

Choose the related Textbox on the left and the Change event on the right drop down.

Andreas.

Hi Andreas and Ossie

Thank you both for assistance

Andreas - yes I know text was incorrect but that was the point of the original help request because text actully does work up to an extend until you have a date leading with 0 - I posted to the request to seek assistance with that issue which Ossie resolved and correctly in respect of the UK format

So stepping back - regarding the the post in respect of using passive vba (rather than a command button on the user form) or a formula in the workbook - is there away to carry out that calculation while the user is inputting the data for each each a new incident record is generated?

Lastly can I create a list on a user form which adds items each a new item is added from a combobox list without creating a complete new record every time a new item is added- thinking of a way to explain - it would be like shopping online where your online shopping basket acts like a list box and then copies all those items when your finished onto the worksheet when you press SAVE