PDA

View Full Version : Solved: Scroll Bar in Userform Wipes Data in Cells



sal21
09-14-2004, 04:23 AM
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 (http://www.gssitaly.com/l0785_def.zip)

sebastienm
09-14-2004, 10:22 AM
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

sal21
09-15-2004, 12:01 AM
... Many tks,
if you have a time for me re-attache (or re-post the modify) my project with your modify.
Tks in advance.

sebastienm
09-15-2004, 12:51 PM
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

Anne Troy
09-16-2004, 08:09 AM
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...?

sebastienm
09-16-2004, 10:32 AM
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

Anne Troy
09-16-2004, 10:35 AM
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
09-28-2004, 10:53 PM
Hey, Sal. Did you ever get this solved?
I ended up in the hospital that weekend. :(

sal21
09-29-2004, 12:20 AM
Tks, to interess my prob!
The suggestion sebastienm (http://www.vbaexpress.com/forum/member.php?u=140) 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...

Anne Troy
09-29-2004, 12:23 AM
GREAT!
:)

I am "marking solved".

sal21
09-29-2004, 12:27 AM
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!

Anne Troy
09-29-2004, 12:33 AM
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!!)

sal21
09-29-2004, 12:36 AM
OH! OH! Attention for the next...
Very happy for good healt...