Solved: ScrollBar to show Userform Form field values

[simora]

I have a Userform and I added a ScrollBar from the Toolbox:
What code do I need to add to it so that I can scroll through the form and see the values populated in the various textboxes using the ScrollBar.
I'm guessing that the code goes here,
Private Sub ScrollBar1_Change(); but exactly what values do I enter?
Sample code and suggestions welcome.

Thanks

[bonyclyd]

How about using ScroBars,ScrollHeight,ScrollWidth properties of the Userform?

[simora]

bonyclyd:
That scrolls the whole Userform. I need to scroll the form and see the textbox values change with the position of the ScrollBar

[bonyclyd]

Hi simora

you want to input value into the textbox with scrollbar.

If you do so, you can write the code as following:

Private Sub ScrollBar1_Change()
Me.TextBox1.Text = Me.ScrollBar1.Value
End Sub

Private Sub UserForm_Initialize()
With Me.ScrollBar1
.Value = 0
.Min = 0
.Max = 100
.LargeChange = 10
.SmallChange = 5
End With
End Sub


HTH

SJOO

[simora]

bonyclyd:
I actually have say 5 TextBoxes 1- 5 that post their data to Columns A-E on Worksheet 1
Your code is showing me Values 1- 100 in the TextBox but I need to see the data from the workbook in Columns A-E presented in the UserForm TextBoxes 1- 5 as I scroll the ScrollBar. Hope this is clear. Thanks

[bonyclyd]

Well... You want to navigate the worksheet with the userform.
I attached the sample file for your reference.
I wrote a rudimentary code considering you might be a novice to VBA.

Option Explicit

Dim wks As Worksheet
Dim row_beginning As Long
Dim row_ending As Long

Private Sub ScrollBar1_Change()
Dim row As Long

row = CLng(Me.ScrollBar1.Value)
With wks
Me.TextBox1.Text = .Cells(row, 1)
Me.TextBox2.Text = .Cells(row, 2)
Me.TextBox3.Text = .Cells(row, 3)
Me.TextBox4.Text = .Cells(row, 4)
Me.TextBox5.Text = .Cells(row, 5)
End With
End Sub

Private Sub UserForm_Initialize()
Set wks = Worksheets("Sheet1")

row_beginning = 2
row_ending = wks.Cells(row_beginning, 1).End(xlDown).row

With Me.ScrollBar1
.Value = row_beginning
.Min = row_beginning
.Max = row_ending
.LargeChange = 1
.SmallChange = 1
End With
Call ScrollBar1_Change
End Sub

[simora]

bonyclyd:

THANKS !
I can follow your code. Just one line needs explaining if you can.
row = CLng(Me.ScrollBar1.Value)
Again. Thanks a million.

[bonyclyd]

Nice

CLng is a type-casting function that changes into long integer value.
It is not necessary because ScrollBar1.Value already returns integer value.
It's a kind of my mistake. but It doesn't disturb working.

Bye

[simora]

bonyclyd:

Thanks for clearing that up

Again. Thanks a million.

[finaljustice]

Well... You want to navigate the worksheet with the userform.
I attached the sample file for your reference.
I wrote a rudimentary code considering you might be a novice to VBA.

Option Explicit



Dim wks As Worksheet
Dim row_beginning As Long
Dim row_ending As Long

Private Sub ScrollBar1_Change()
Dim row As Long

row = CLng(Me.ScrollBar1.Value)
With wks
Me.TextBox1.Text = .Cells(row, 1)
Me.TextBox2.Text = .Cells(row, 2)
Me.TextBox3.Text = .Cells(row, 3)
Me.TextBox4.Text = .Cells(row, 4)
Me.TextBox5.Text = .Cells(row, 5)
End With
End Sub

Private Sub UserForm_Initialize()
Set wks = Worksheets("Sheet1")

row_beginning = 2
row_ending = wks.Cells(row_beginning, 1).End(xlDown).row

With Me.ScrollBar1
.Value = row_beginning
.Min = row_beginning
.Max = row_ending
.LargeChange = 1
.SmallChange = 1
End With
Call ScrollBar1_Change
End Sub


Hi I've never used scrollbars and I would like to do exactly what was proposed on this thread, I would like to navigate the worksheet with a scrollbar in the userform making the values be altered back at the worksheet. I haven't found the example you have said to attached, by any chance is there a way for you to attach it again so I can study how you have done this?

Thanks for your attention and time.
Yours sincerely
Luis.

[simora]

Unfortunately, Ill be traveling for another 3 -4 weeks and don't have that old information at hand. Have you taken a look at this ? http://www.vbforums.com/showthread.php?511916-VBA-Working-with-Scroll-Bars-The-most-simplest-way

OR http://www.excelforum.com/excel-programming-vba-macros/706941-scroll-bars-in-an-excel-userform.html