PDA

View Full Version : Userform Spinbutton to advance row and refresh



xabbu
08-19-2008, 09:25 PM
(To be upfront I have posted this question in another forum but have'nt gotten what I need. I used to use excelforum.com but it seems to have been shut down or they have technical issues so I have been trying to find an equally helpful and knowledgeable forum)

I have a current Userform for data review and entry.
The userform should progress by row and auto fill the data into the form from that row and leaves spots for data entry which is put back into the worksheet.

The A: colum lists the Case or Client "Number"(that is its name in the form and wks) and has a combobox that will allow you to chose any client "Number" via scroll. (this part works fine)
There is a spinbutton that advances the rows of a worksheet by the "Number " ComboBox1

Unfortunately if there are duplicate alphanumerics in th "Number" column (which is common for same client number different instance or visit etc.) the spinbutton does not refresh the form with each row with the duplicate "Number". instead it progresses without refreshing until it gets a new alphanumeric in the column. (so if a there are say 3 instances of "number" ACDB115 it will show the data from the first, ... click.. no change to data... click.. no change to data,.... click.. next row ACDB300 with its proper data.)


I need the Spinbutton to simply Advance to the Next Row, and refresh all the data in the form with what is on that row even if what is in cell A: is not different.

I know this should be the most rudimentary code but I've been looking everywhere and just cant get it.

Attached is my working sample which works for all BUT the fact that the last 3 rows have duplicate "Numbers" in column A: though their data differs. the spin button will only show the data from the first row of duplicates.

ideally, I would ask someone pull up my example and the vba code attatched to it, and make it work, and then explain where my mental fubar was, and how they corrected it, (and if it isnt asking way to much, explaining why it works and why mine didnt,.. though I know that might be really stretching it.)

I know it is hard dealing with people who have more questions then answeres. I also know it is often a hassle to hold their hand or walk them through. Unfortunately, that's where I am at the moment.

Thanks in advance for what I know I'm going to be embarrassed by having overlooked or not found.

Bob Phillips
08-20-2008, 01:13 AM
Sub LoadComboBox1()

Dim LastRow As Long
Dim Rng As Range

LastRow = Wks.Cells(Rows.Count, "A").End(xlUp).Row
LastRow = IIf(LastRow < StartRow, StartRow, LastRow)

With Wks
Set Rng = .Range(.Cells(StartRow, "A"), .Cells(LastRow, "A"))
End With

With UserForm1.ComboBox1
.Clear
.List = Application.Transpose(Rng)
End With

End Sub