D
Don
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
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