Consulting

Results 1 to 13 of 13

Thread: Scroll Bar in Userform Wipes Data in Cells

  1. #1

    Scroll Bar in Userform Wipes Data in Cells

    I have inserted a controll on a scroll bar:

    If Cells(ScrollBar1.Value + 6, 4) = "C/C" Then 
    'init list
    MsgBox "INIZIO ELENCO, IMPOSSIBILE PROSEGUIRE!", , "ATTENZIONE..."
    Exit Sub
    End If
    If Cells(ScrollBar1.Value + 6, 6) = "" Then
    'end list
    MsgBox "FINE ELENCO, IMPOSSIBILE PROSEGUIRE!", , "ATTENZIONE..."
    Exit Sub
    End If
    but it not work, WHY...

    if you have another way is welcone

    www.gssitaly.com/l0785_def.zip

  2. #2
    VBAX Regular
    Joined
    Jun 2004
    Location
    USA
    Posts
    10
    Hi,
    In the designer, you have set the Min property of the scroll bar to 1 and at the _Initialize of the form, you've set the Max to: Range("Q65536").End(xlUp).Row - 6.
    Therefore, the Min and Max will be 1 and the number of rows of data (without headers, so here, 8).

    This means, that the
    Cells(ScrollBar1.Value + 6, 4)
    line will always be between Cells(7, 4) and cell Cells(14,4), which is data.

    To have Cells(ScrollBar1.Value + 6, 4) refer to cells(6,4) ie "C/C" , you would need scrollbar1.value to be 0 , but it can't 'cause its min property is 1.

    Regards,
    Sebastien
    Seb

  3. #3
    ... Many tks,
    if you have a time for me re-attache (or re-post the modify) my project with your modify.
    Tks in advance.

  4. #4
    VBAX Regular
    Joined
    Jun 2004
    Location
    USA
    Posts
    10
    There is nothing to modify. As it is (and was) now, it works perfectly since you can only scroll within the rows of data. Your current code automatically prevents the user to scroll outside the data.
    Do you mean you want to display the message anyway? That can be done, but it would be a a pain to the user to click the 'ok' button each time it reaches the begining/end of data!

    Sebastien
    Seb

  5. #5
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Okay, guys. I'm helping Sal to figure this one out. Here's what happens, tho it wasn't real easy to get it to do this until we went back and forth a time or two.

    Open the file, and the userform pops up.
    Hit the scrollbar on the userform to go to the 2nd record.
    Put a 5 into the Esito Note BOU box, which takes your cursor to another textbox. Type something into that textbox and it populates column X for that record.
    Now, scroll UP with the scrollbar to the previous record, then scroll back down, and column X is wiped out, tho we did not touch the textbox.

    Like, it's updating that value EVERY time...?
    ~Anne Troy

  6. #6
    VBAX Regular
    Joined
    Jun 2004
    Location
    USA
    Posts
    10
    Problem:
    When moving from one record to another one, the ScrollBar1_Change is executed (where all textboxes are populated).
    The line TextBox38 = Cells(ScrollBar1.Value + 6, 23) is executed
    -->it triggers the TextBox38_Change() sub where the last line is
    Cells(ScrollBar1.Value + 6, 24) = Indirizzario.TextBox39.Value
    Since Textbox39 is still empty, the cell becomes blank.
    Then, the line TextBox39 = UCase(Cells(ScrollBar1.Value + 6, 24))
    but the cell has been blanked out from line above, so the textbox becomes blank.

    Fix:
    In ScrollBar1_Change(), instead of
    TextBox38 = Cells(ScrollBar1.Value + 6, 23)
    TextBox39 = UCase(Cells(ScrollBar1.Value + 6, 24))
    do
    TextBox39 = UCase(Cells(ScrollBar1.Value + 6, 24))
    TextBox38 = Cells(ScrollBar1.Value + 6, 23)
    (just switch the 2 lines)

    Now check the consequences of that changes to make sure it doesn't break something else somewhere.

    Regards,
    Sebastien
    Seb

  7. #7
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Thanks a bunch, Sebastien! I'm not familiar enough with code to make heads or tails out of it. I'm sure Sal will check out what you said and get back to us.
    ~Anne Troy

  8. #8
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Hey, Sal. Did you ever get this solved?
    I ended up in the hospital that weekend.
    ~Anne Troy

  9. #9
    Tks, to interess my prob!
    The suggestion sebastienm vbmenu_register("postmenu_8151", true); and your are very simple and with this i have solved my problem in btillinat mode.!:-)
    Tks for subseguent prob...
    Sal
    1 help 1 pizza
    2 help 1 pizza 1 caff?
    3 help 1 pizza 1 caff? 1 mozzarella
    ...
    Spaghetti, Lasagne and Tortellini for the "Lady" ;-)

  10. #10
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    GREAT!


    I am "marking solved".
    ~Anne Troy

  11. #11
    I ended up in the hospital that weekend.

    In the hospital? I hope, taht your problem for healt is simple and solved?
    The forum is the second... the healt and family the first!
    At in this time is all ok!
    Sal
    1 help 1 pizza
    2 help 1 pizza 1 caff?
    3 help 1 pizza 1 caff? 1 mozzarella
    ...
    Spaghetti, Lasagne and Tortellini for the "Lady" ;-)

  12. #12
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    All is GOOD!

    (Stupid gall bladder. And you know why? I hate a WHOLE pizza by myself!! And I ended up in the hospital!! LOL!!)
    ~Anne Troy

  13. #13
    OH! OH! Attention for the next...
    Very happy for good healt...
    Sal
    1 help 1 pizza
    2 help 1 pizza 1 caff?
    3 help 1 pizza 1 caff? 1 mozzarella
    ...
    Spaghetti, Lasagne and Tortellini for the "Lady" ;-)

Posting Permissions

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