User form command buttons

[ValJan]

Hi

I'm new to vb but have managed to create a user form to make adding information to a spreadsheet easier. I have a command button named 'Add' and when clicked this automatically updates the worksheet named 'Database'.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Database")

End Sub

However, sometimes all the information needed to complete an entry is not available and I would like to be able to save the information I have to a worksheet named 'Draft' so that I can retrieve it later, add the missing information and then save it to the 'Database' worksheet. This sounded simply when I thought about it, but I am now struggling to work out how to do it. I would really appreciate some help, or pointing in the right direction if this has already been dealt with on the forum.

Thanks in advance

 

[Artur1982]

Hi,

I'd have one command button to have the data stored to the data base and one to store to the drafts sheet when necessary- The code would be the same just replacing the name of the worksheet, infact you could have it on the same button but choose which one when you click the button:

ws = inputbox("Which sheet do you want to save this to?") - Although with this you would obviously have to type the name exactly right unless you design your own msgbox with a combo box on it.

Once it's saved to the drafts recalling it just involves reversing the code. So if you had:

Range("a1").value = txtCustomerName

then to recall it:

txtCustomerName = sheets("Draft").Range("a1").value

which will recall it - do this for all the fields, then the user can amend the data and press the command button you originally had to send it to the database.

I'd suggest assigning a reference to each line so that you can have more than one (use the macro recorder to get the code for 'cells.find... .select' so you can search for the reference and then use activecell.offset to get the rest of the data).

Hope that helps... =D

Arthur =D