how to keep data already inserted in a sheet when using an userform

[s7yzrs]

Hello everybody,

is it possible to use an userform multiple times but to keep the data that has already been inserted in the sheet?

[cytop]

A general question like that can only be answered in general - so, yes.

You need to explain in more detail to get a more detailed answer

[s7yzrs]

Hi Cytop, thank you for your reply.

well... I am about to start a small program where people will be allowed to insert data in a sheet via an userform. As not all the info will be know at the same time, I would like for them to be able to quit the program but, when they open it again, I need the already filled cells to keep what has already been inserted even if the userform cells from where they get the data is empty.

[cytop]

Unfortunately that can be read 2 ways...

1. The userform is used to add data but allows partial data only to be saved to the worksheet. The user then returns to userform, next day perhaps, and re-loads the partial data to continue input.

2. While inputting data, the user can 'save' partial data without updating the workbook, and reload it again later.

Either is possible.

[s7yzrs]

Hi cytops,

in my case either way is good. But the first method is preferred

[cytop]

Very rough and ready but shows one way how to ...

Bear in mind there are 99 ways to do anything in Excel and there are definitely better ways to do this but for 10 minutes work it's not going to be a polished, tested, fully featured little app.

Happy to explain anything you may not understand, but that'll be tomorrow

[s7yzrs]

Good mornin Cytop, thank you again for your reply

I quickly saw the example you sent me. I am going to implement it and get back to you shorty.

Meanwhile I can say that, for as far as I could see, it is exactly what I was looking for.

[s7yzrs]

Hello Cytop, I have checked the example you posted. It does look ok but I have a question: the userform functions on the active sheet, but what if I want to store (and retrieve) the data to/from a hidden sheet (this way my colleague cannot "accidentally" screw up the data....

[cytop]

Try this then.

A new Sheet2 has been added to hold the buttons. There are 2 buttons but not 'Add' & 'Edit'. The first is 'Do work' which loads the userform. This now has a combobox that displays 'New record' as the first item and then all items in Column B of sheet1 as a picklist to choose what to do, Add or Edit.

the Save button will only be enabled after a change is made in any textbox, just to prevent the user trying to save a blank record.

Sheet1 is 'Very hidden' so a user will not see it in Excels' Unhide options but there is also a button to hide/unhide it.

[s7yzrs]

Hi cytop,

thank you again for your reply. I have checked it and it does what need but... I do not save the data once done with the job.

Attached please find an example of what I have.

So, to be more specific: I open the worksheet and the userform shows (not implemented in the example but I know how to do it). The userform should show the data that has already been inserted in the sheet. Now I have 2 options. Either I clear all the text boxes (and the sheet where all the data is stored) else I continue working from where I left.

[cytop]

Going back to my first post, I can only give an example based on what I know, and I have done that but then more 'requirements' pop up. That's not a problem in itself but knowing what the end result should be helps to avoid wasting time. For example, in the first example, I assumed you were building a list on a worksheet but now it seems you're inputting data which is probably used as the basis for some calculations and you now want a method to clear what has been entered - or maybe even that assumption is wrong.

This is not a complaint, just mentioning, but it's time you explained the whole process rather than bits at a time.

[s7yzrs]

Hi Cytop, sorry for not being clear.

My last post (the one with the attachment) is the whole procedure.

We are assigned a flight, we insert some data in the sheet (at the end these datas will generate a report that will be printed, reason why there is no need for saving in the pc). We don't always have all the data we need so, we might need to close the program, go out and handle the flight and come back, after the aircraft left, and insert the missing data and, where necessary, change some of it (maybe the weights have changed, etc etc)