Results 1 to 14 of 14
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: scroll records with distinct value in column (200

    I don't understand what you are after. Could you elaborate?

    What should be in the textbox and what should the scrollbar do?

    Steve

  2. #2
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: scroll records with distinct value in column

    If you see attached sheet is present a userform, contain combobox textbox and scrollbar.
    for test select PUGLIA from combobox.
    Now i want fill the textbox, during the scroll, only with the value in column H where in column Y is present the value PUGLIA.
    In this case the rexord to scroll inntextbox during the scroll are: 41

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: scroll records with distinct value in column

    Is this what you are after?

    Steve

    <pre>Option Explicit
    Dim vArray() As Variant
    Private Sub ComboBox4_Change()
    Dim x As Long
    Dim i As Long
    Dim rCell As Range
    Dim rng As Range
    With Worksheets("Luststp")
    Set rng = .Range(.Range("Y3"), _
    .Cells(.Cells.Rows.Count, "Y").End(xlUp))
    End With
    x = Application.WorksheetFunction _
    .CountIf(rng, Me.ComboBox4.Value)
    ReDim vArray(1 To x)
    i = 0
    For Each rCell In rng
    If rCell = Me.ComboBox4.Value Then
    i = i + 1
    vArray(i) = rCell.Offset(0, -17)
    End If
    Next
    Me.ScrollBar1.Min = 1
    Me.ScrollBar1.Max = x
    Me.ScrollBar1.Value = 1
    End Sub

    Private Sub ScrollBar1_Change()
    Me.TextBox1.Value = vArray(Me.ScrollBar1.Value)
    End Sub</pre>


  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: scroll records with distinct value in column

    How about this?

    Change the variable ITBCount in the intitialize to reflect the number of textboxes you have (currently it is 2). TextBox1 will have column A, TextBox2 is Col B, etc

    Steve

    <pre>Option Explicit
    Dim vArray() As Variant
    Dim iTBCount As Integer
    Private Sub UserForm_Initialize()
    iTBCount = 2 'Number of Textboxes on Form
    Call FILL_ComboBox4
    End Sub
    Private Sub ComboBox4_Change()
    Dim x As Long
    Dim i As Long
    Dim j As Integer
    Dim rCell As Range
    Dim rng As Range
    Dim iCol As Integer

    iCol = 25 'Column Y
    With Worksheets("Luststp")
    Set rng = .Range(.Cells(3, iCol), .Cells(.Cells.Rows.Count, iCol).End(xlUp))
    End With
    x = Application.WorksheetFunction.CountIf(rng, Me.ComboBox4.Value)
    ReDim vArray(1 To x, 1 To iTBCount)
    i = 0
    For Each rCell In rng
    If rCell = Me.ComboBox4.Value Then
    i = i + 1
    For j = 1 To iTBCount
    vArray(i, j) = Trim(rCell.Offset(0, -iCol + j))
    Next
    End If
    Next

    Me.ScrollBar1.Min = 1
    Me.ScrollBar1.Max = x
    Me.ScrollBar1.Value = 1
    End Sub
    Private Sub ScrollBar1_Change()
    Dim j As Integer
    For j = 1 To iTBCount
    Me.Controls("Textbox" & j) = vArray(Me.ScrollBar1.Value, j)
    Next
    End Sub</pre>


  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: scroll records with distinct value in column

    Excellent work!
    But to understand and if i am not right...
    You code work:
    column A for tbox1, column B for tbox2, column C for tbox3... ecc.
    The code work with a fixed sequence column 1 with tbox1 column 2 with tbox2....
    If yes how to fill tbox4 with vlue in column L?
    hope understand me.:-)

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: scroll records with distinct value in column

    Why put it in textbox4 and not in Textbox12?

    If you are not going to want to use all the columns from 1 to whatever in each their own textbox (as you indicated earlier) you will have to be specific on your wants.

    How many textboxes will be on the form? What are there names? Which column of data do you want in each text box?

    Steve

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: scroll records with distinct value in column

    Hi Steve peraphs solved with your suggestion tell me if i am wrong...
    In other case a BIG tks for patience.
    see here wath you think?:

    Option Explicit
    Dim vArray() As Variant
    Private Sub UserForm_Initialize()
    Call FILL_ComboBox4
    End Sub
    Sub FILL_ComboBox4()

    Dim AllCells As Range, Cell As Range
    Dim NoDupes As New Collection
    Dim i As Integer, j As Integer
    Dim Swap1, Swap2, Item
    Dim ULTIMA_D As Long
    Dim WS1 As Worksheet

    Set WS1 = Sheets("FOGLIO1")
    ULTIMA_D = WS1.Cells(Rows.Count, "A").End(xlUp).Row
    Set AllCells = WS1.Range("A2:A" & ULTIMA_D)

    On Error Resume Next
    For Each Cell In AllCells
    NoDupes.Add Cell.Value, CStr(Cell.Value)
    Next Cell
    On Error GoTo 0
    For i = 1 To NoDupes.Count - 1
    For j = i + 1 To NoDupes.Count
    If NoDupes(i) > NoDupes(j) Then
    Swap1 = NoDupes(i)
    Swap2 = NoDupes(j)
    NoDupes.Add Swap1, before:=j
    NoDupes.Add Swap2, before:=i
    NoDupes.Remove i + 1
    NoDupes.Remove j + 1
    End If
    Next j
    Next i

    Me.ComboBox4.Clear

    For Each Item In NoDupes
    Me.ComboBox4.AddItem Item
    Next Item

    End Sub
    Private Sub ComboBox4_Change()

    Dim x As Long
    Dim i As Long
    Dim j As Integer
    Dim rCell As Range
    Dim rng As Range
    Dim iCol As Integer

    iCol = 25 'Column Y
    With Worksheets("Luststp")
    Set rng = .Range(.Cells(3, iCol), .Cells(.Cells.Rows.Count, iCol).End(xlUp))
    End With
    x = Application.WorksheetFunction.CountIf(rng, Me.ComboBox4.Value)
    ReDim vArray(1 To x, 1 To iCol)
    i = 0
    For Each rCell In rng
    If rCell = Me.ComboBox4.Value Then
    i = i + 1
    For j = 1 To iCol
    vArray(i, j) = Trim(rCell.Offset(0, -iCol + j))
    Next
    End If
    Next

    Me.ScrollBar1.Min = 1
    Me.ScrollBar1.Max = x
    Me.ScrollBar1.Value = 1

    End Sub

    Private Sub ScrollBar1_Change()

    Me.TextBox1 = vArray(Me.ScrollBar1.Value, 1)
    Me.TextBox3 = vArray(Me.ScrollBar1.Value, 2)
    End Sub

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: scroll records with distinct value in column

    It appears to scroll through the items in Col A (TxtBox1) and Col B (TxtBox3) based on the selection in the combobox. [I don't see a Txtbox4 or Col L] which is what you last asked about)

    I am not sure what you are trying to do, so i don't know if it works correctly.

    Does it do what you want it to do? If not could you tell us what you want it to do?

    Steve

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: scroll records with distinct value in column

    Hi Steve no worry, resolved all with a little modified in your code...
    Tks for all and patience.
    Wath you like Pizza or Expresso from Napoli?
    Sal.

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    scroll records with distinct value in column (200

    Have a sheet filled with variuos rekords...(started from line 2 range A2:Y & last filled cell)
    Have a userform with variuos tetx box fille from value in column of range.
    Have a combobox filled with "AAAA"
    Now in column Y of sheet are present value similar:

    J2 AAAA
    J3 AAAA
    J4 AAAA
    ....

    J2 BBBBB
    J3 CCCC
    J4 CCCC

    Now using a scrollbarchange1 event how to scroll only the line of sheet based value in comboxbox and column Y?
    Naturally use scrollbarchange in up and down...

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: scroll records with distinct value in column

    Hi Steve the code work fine!
    Tks a lot...

    But if i want to fill other textbox during the scrollbar in userform...
    Attached real version of my sheet...
    In textbox1 ai want to fill the value in column A, textbox2 fill with value in column B... ecc..
    make for me an example for this 2 textbox and i adjust for other ...

  12. #12
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: scroll records with distinct value in column

    Dubt....
    But, about the code, during the scrollbar up and down i have difficult to intercept the really line of sheet of the actually record in textbox...
    In effect i have see, the position of scrollbar.value (in event ScrollBar1_Change) not is the same of line in sheet...
    How to return the really position in sheet similar pos=????.row

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: scroll records with distinct value in column

    You never asked to keep track of the row (we can only provide answers to the questions you ask... This is why it is important to explain what you need in some detail...)

    You could add into the Array variable another "column" to include the row.

    <pre>ReDim vArray(1 To x, <font color=red>0</font color=red> To iCol)
    i = 0
    For Each rCell In rng
    If rCell = Me.ComboBox4.Value Then
    i = i + 1
    <font color=red>vArray(i, 0) = rCell.row</font color=red>
    For j = 1 To iCol
    vArray(i, j) = Trim(rCell.Offset(0, -iCol + j))
    Next</pre>


    Then you can add it to A textbox (eg named "TextboxRow") if you want in the "ScrollBar1_Change" to display whenever the scroll bar changes:

    <pre>Private Sub ScrollBar1_Change()
    <font color=red>Me.TextBoxRow = vArray(Me.ScrollBar1.Value, 0)</font color=red>
    Me.TextBox1 = vArray(Me.ScrollBar1.Value, 1)
    Me.TextBox3 = vArray(Me.ScrollBar1.Value, 2)
    End Sub</pre>


    Steve

  14. #14
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: scroll records with distinct value in column

    Sorry me but during to make the project is required to know the line of the sheet... <img src=/S/angel.gif border=0 alt=angel width=15 height=21>
    In other case the code to suggest me with the last your modified work PERFECT!...
    Thanks as usual
    Sal.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •