PDA

View Full Version : Solved: UserForm List Box Problem



sooty8
05-03-2010, 05:24 AM
Hi All

I have a UserForm List Box problem - when selecting from the drop down Select Site - I need it to renew the data in the selected columns on the Data Sheet at the moment it is not changing - I'm missing something and need your expert guidance.

Have attached a shortened altered version of the file.

Many Thanks

Sooty8

mdmackillop
05-03-2010, 07:19 AM
Get rid of merged cells; use Centre Across Selection instead.

One example, but your listbox is not showing the correct site data.

Private Sub Tb4_Change()
Dim Rw%, Col%
Rw = Columns(1).Find(Lb1).Row
Col = Rows(1).Find(Cb2).Column
Cells(Rw, Col + 1) = Tb4
End Sub

sooty8
05-03-2010, 08:42 AM
Hi MD

I'm a tad lost here - I have just opened the file I attached and on selecting Site1 the list box filled - on selecting a row in the list box the text boxes above filled ok - my problem is on say selecting Site 2 only the Miles & Yards should change and they are not changing to the relevant columns. I have also changed to Centre Across Selection however I cannot understand your code for Tb4 when Tb4 is already in use. Any further help would be much appreciated. Perhaps there is another and better way of filling the list box?

Regards

Sooty8

mdmackillop
05-03-2010, 09:03 AM
I though you were looking to change data on the sheet.

You forgot to use your ColNumDist variable in your code

For rownumdist = startrowdist To totalrowdist
Lb1.AddItem Sheets(Trim(Me.Tb1A.Value)).Cells(rownumdist, 1)
Lb1.List(rownumdist - startrowdist, 1) = Sheets(Trim(Me.Tb1A.Value)).Cells(rownumdist, 2)
Lb1.List(rownumdist - startrowdist, 2) = Sheets(Trim(Me.Tb1A.Value)).Cells(rownumdist, 3)
Lb1.List(rownumdist - startrowdist, 3) = Sheets(Trim(Me.Tb1A.Value)).Cells(rownumdist, colnumdist)
Lb1.List(rownumdist - startrowdist, 4) = Sheets(Trim(Me.Tb1A.Value)).Cells(rownumdist, colnumdist + 1)
Next rownumdist

sooty8
05-03-2010, 09:12 AM
Hi MD

Brilliant - I'm not very good at explaining myself - I know what I want to happen but somehow it never transfers properly into my text.

Many Thanks for your Help

Regards

Sooty8