PDA

View Full Version : Solved: Changing Values On UserForm ListBox



sooty8
12-28-2008, 04:57 AM
Hi

Hope you all had a nice holiday and all the best for 2009

Any help with the following much appreciated

Sooty 8


Private Sub Cb12_Click()
'This Sub along with finding the correct row in the List Box ("Lb1") it also finds the
'correct cell on "Sheet1" in column "A" - is it possible to alter the values in columns
'"I" - "J" & "K" on the selected row without closing the UserForm - changing the
'values on "Sheet1" then reopening the UserForm. I'm using "Row Source" to fill the List Box

Dim rngToSearch As Range
Dim rngFound As Range
Set rngToSearch = ActiveSheet.Columns("A")
Set rngFound = rngToSearch.Find(What:=Tb31L.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & Tb31L.Value & " was not found."
Else
With Lb1
For i = 0 To .ListCount - 1
If .List(i, 0) = Tb31L.Value Then
Lb1.ListIndex = i
For Each cell In Range(Cells(rngFound.Row, 3), _
Cells(rngFound.Row, 3))
s = cell.Value & ","
Next
Exit For
End If
Next
rngFound.Select
End With
End If

Tb31L.Value = ""

End Sub

sooty8
12-28-2008, 01:22 PM
Hi All
Solved the question myself after a few attempts with 3 Textboxes & a Commandbutton used ActiveCell and Offset (,8)-(,9)-(,10) works OK for me so marked it solved.

Cheers

Sooty8