How To Amend Data Via Userform (and get spinbox to work correctly)

[ScotyB]

In the attached workbook, data is written to a hidden sheet (Data) via the use of a userform (the ADD NEW DATA button activates the AddNewData userform) - that part works great. Sometimes however, a user may need to amend an entry and initially, the OOPS button would simply open the hidden sheet to allow them to make changes - this was not the way I would like it to work as it introduces all kinds of inconsistencies with formatting where users are inputting data that's different to the combo or list box that adds data in the first place.

My thought then was to use another userform (identical to the AddNewData form) to make the necessary changes. The AmendData userform is activated by the OOps button. So, I have three main areas of difficulty:

1. I'd like the userform to open with populated data from the Data sheet, starting from the last record in the sheet (which would USUALLY be the record that needs amending);
2. I can't get the spinbox to show any more than 10 items of data even though there are some 70+ rows in the Data worksheet (user may need to scroll up or down occasionally to find a record to amend), and finally,
3. how do I save the amended data back to the Data worksheet? The user might change one or more fields and my hope is that the SAVE button on the form will simply (over)write the new data to the DATA sheet.

Any help would be appreciated so many thanks in advance to those willing to take a look for me.

Cheers,

AJ

[rylo]

Hi

1) Create some initialize code for the amend data form.

Please Login or Register  to view this content.

2) This happens due to the way that you are establishing the data to fill
Currently you have
Me.ComboBox1.Value = Worksheets("Data").Range("A2" & Me.SpinButton1.Value).Value
If your spin button gets to 10, then you are effectively trying to fill from A210 (it concatenates the value from the spin button onto A2 giving A210) on the data sheet. Change to to be

Please Login or Register  to view this content.

Now this will give you some trouble if you let it get to 1, or 0, but worry about that later.

3) The spinbutton will house the row of the last data input to the form. So you can put that value to a variable, and use it to paste back the data.

HTH

rylo

[protonLeah]

For the Amend form:


Change #1:

Please Login or Register  to view this content.

Change #2:

Please Login or Register  to view this content.

[rylo]

Proton

Nice. Hadn't thought of setting the max and min for the spinbutton like that. As row 1 has headers, maybe make the spinbutton to be 2 not 1.

rylo

[ScotyB]

Rylo, ProtonLeah,

thank you both for your replies - the code worked a treat and since you were both pretty close in methodology, I'll give you both a gold star, lol.

As for this...

3) The spinbutton will house the row of the last data input to the form. So you can put that value to a variable, and use it to paste back the data

... I'm afraid that Vba scares the bejeebers outta me, I'm on a very steep learning curve, and I have no idea how to do that, so a hint on that would be greatly appreciated also, then I can mark this one as solved.

Many thanks again to you both.

Cheers,

AJ

[rylo]

AJ

In post #2 I've got the code

Please Login or Register  to view this content.

In this code, the variable lastrow is filled with the last used row, and then the spinbutton is filled from the variable lastrow.

So just reverse things. Create a variable that makes sense to you (say something like CurrentItemRow) and assign it the value from the spinbutton

Please Login or Register  to view this content.

Then you have a variable that has the row of the item being actioned. You can refer to it either by the range method range("A" & currentitemrow)] or the cells method [cells(currentitemrow,1)]. So if currentitemrow is 10, the both these constructs refer to A10.

rylo

[protonLeah]

How about:

Please Login or Register  to view this content.

Suggestion: Add a label under the spin button then add:

Please Login or Register  to view this content.

to the spinbutton_change code.

[ScotyB]

Bingo!

Thanks again, Rylo and ProtonLeah - that worked perfectly (and I even understood some of it, lol) - I'll now mark this thread as solved.

Cheers,

AJ