"Scrolling with Scrollbar or SpinButton on UserForm"

[Don]

Hi there,

I've searched for this answer quite extensively and either I don't
understand what I'm reading (which is very possible) or I haven't hit the
right site yet.

I have a UserForm that has several TextBoxes on it and have successfully
coded that form such that data can be entered in each of the TextBox's and
the data will be entered in the next available Row in the several Col's
involved. This part works great.

Using this UserForm (I assume with a ScrollBar) I'd like to be able to
scroll back up through the Row's and edit any or all of the TextBox entries
associated with any particular Row.

I hope that makes sense.....TIA,

Don

 

[Incidental]

Hi Don

It sounds like you could just use the built in Form found under the
Data menu in the main excel app. I have included some code below that
should do what you are after. To test put four textboxes and a
scrollbar on a userform and then paste the following code into the
userform code module and of course add some data to the columns A - D.

Option Explicit
Dim LstCell As Integer
Dim SetRow As Integer
Dim i As Integer
Dim Ctrl As MSForms.Control 'Alway declare your variables

Private Sub ScrollBar1_Change()

SetRow = ScrollBar1.Value 'Pass scrollbar value to a variable

Range("A" & SetRow).Activate 'Select a row using that variable

For i = 1 To 4 'Second number is the total No of textboxes

Set Ctrl = UserForm1.Controls("TextBox" & i) 'Select a textbox

Ctrl.Value = ActiveCell.Value 'Show cell value in textbox

ActiveCell.Offset(0, 1).Activate 'Offset cell for the next pass

Next 'Iterate textbox

Range("A" & SetRow).Activate 'Reselect the first column for when you
save

End Sub

Private Sub UserForm_Initialize()

LstCell = [A65535].End(xlUp).Row 'Find last used cell in column A

ScrollBar1.Min = 1 'Set Min and Max value to scroll bar

ScrollBar1.Max = LstCell

End Sub

Hope this Helps

S

 

[Don]

Thanks for the quick reply and it works great....does exactly what I was
after, after coding in a command button. Thank you for your time and
knowledge, appreciate it very much.

Don

 

[Don]

It's me again...everything is working fine so far...but I have two
questions... How do I code this scrollbar so that it opens on the last value?
Here's what I've done so that the scrollbar has an open blank cell at the
bottom, I'd like the TextBoxes to open in this blank Row:


Private Sub UserForm_Initialize()
LstCell = [A65535].End(xlUp).Row 'Find last used cell in column A
ScrollBar1.Min = 4 'Set Min and Max value to scroll bar
ScrollBar1.Max = LstCell + 1 '<<<increased by one to the first empty cell
End Sub

Second Question:

Here is my code for entering the data onto the WS:

Private Sub CmdBtn1_Click()
Range("A" & SetRow).Value = TextBox1.Value
Range("B" & SetRow).Value = TextBox2.Value
Range("C" & SetRow).Value = TextBox3.Value
Range("D" & SetRow).Value = TextBox4.Value
Range("E" & SetRow).Value = TextBox5.Value
Range("F" & SetRow).Value = TextBox6.Value
Range("G" & SetRow).Value = TextBox7.Value
Range("H" & SetRow).Value = TextBox8.Value
Range("I" & SetRow).Value = TextBox9.Value
Range("J" & SetRow).Value = TextBox10.Value
Unload UserForm1
UserForm1.Show
End Sub

Is adding the Unload and Show command the best way to get to the next empty
line after entering data?

Thanks again for all your help... Don