"Another ScrollBar Question"
 

Am very appreciative to Incidental for responding to my first post on this
and I've got his code working very well in my UserForm. However the UF opens
with the scroll bar set at the min value of the range established when the
Form is opened. Is there a way to have the scrollbar focus on the next empty
cell in Col A? Here is the code being used to initiate the Form:

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 10 'Second number is the total No of textboxes
Set Ctrl = UF3.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 = 4 'Set Min and Max value to scroll bar
ScrollBar1.Max = LstCell + 1
End Sub


One more question, the last I hope....I've used to code below to
re-establish the max value for the scroll bar....is this the best way of
doing this? I'm pretty sure it's not but it does work.

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 UF3
UF3.Show
End Sub

Sorry for reposting this but I think it got burried because I thanked
Incidental and clicked on the question was answered.

TIA for all the help this forum has been,

Don

"Another ScrollBar Question"
 

Hi Don

Glad the scroll bar code is working out for you. I have added the
requested line of code to set the activecell to the next blank cell in
column A, to do this all you need to do is set the value of the
scrollbar to whatever value you require, also I have looked at the
second question and have added some code to cover that which is the
same idea as putting the values in the textboxes just amended a little
to put the values in the cells, then I recall the userform initialise
event which will recalculate the range and select the next blank cell.

Option Explicit
Dim i As Integer
Dim LstCell As Integer
Dim SetRow As Integer
Dim Ctrl As MSForms.Control

Private Sub CmdBtn1_Click()

If TextBox1.Value = "" Then 'Check the user entered something

MsgBox "Enter A Name to Proceed" 'If not prompt them to do so

Exit Sub 'Stop the sub

End If

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

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

ActiveCell.Value = Ctrl.Value 'Pass textbox value to
activecell

ActiveCell.Offset(0, 1).Select 'Iterate cell 1 to the right

Next 'Iterate textbox

UserForm_Initialize

End Sub

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 10 'Second number is the total No of textboxes

Set Ctrl = UF3.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).Offset(1, 0).Row 'Find last used cell in
column A + 1

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

ScrollBar1.Max = LstCell

ScrollBar1.Value = LstCell 'Set scrollbar to the next empty cell

TextBox1.SetFocus 'Set the focus back to the first textbox

End Sub


I hope this clears things up for you

Steve

"Another ScrollBar Question"
 

Thank you very much Steve.....just got off work so haven't had a chance to
try it, but based on what you provided me before, I'm sure it'll do the
trick. I'll be playing with it tonight. Thanks again,

Don

"Incidental" wrote:

Hi Don

Glad the scroll bar code is working out for you. I have added the
requested line of code to set the activecell to the next blank cell in
column A, to do this all you need to do is set the value of the
scrollbar to whatever value you require, also I have looked at the
second question and have added some code to cover that which is the
same idea as putting the values in the textboxes just amended a little
to put the values in the cells, then I recall the userform initialise
event which will recalculate the range and select the next blank cell.

Option Explicit
Dim i As Integer
Dim LstCell As Integer
Dim SetRow As Integer
Dim Ctrl As MSForms.Control

Private Sub CmdBtn1_Click()

If TextBox1.Value = "" Then 'Check the user entered something

MsgBox "Enter A Name to Proceed" 'If not prompt them to do so

Exit Sub 'Stop the sub

End If

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

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

ActiveCell.Value = Ctrl.Value 'Pass textbox value to
activecell

ActiveCell.Offset(0, 1).Select 'Iterate cell 1 to the right

Next 'Iterate textbox

UserForm_Initialize

End Sub

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 10 'Second number is the total No of textboxes

Set Ctrl = UF3.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).Offset(1, 0).Row 'Find last used cell in
column A + 1

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

ScrollBar1.Max = LstCell

ScrollBar1.Value = LstCell 'Set scrollbar to the next empty cell

TextBox1.SetFocus 'Set the focus back to the first textbox

End Sub


I hope this clears things up for you

Steve