Filter list and display data.

L

law

I have the following code that will display data from a list index.
How can I amend or add to the data and save it in the same row as the
existing data.

Private Sub CommandButton2_Click()

Dim FoundCell As Range

If Me.ComboBox1.ListIndex = -1 Then
'nothing filled in
Beep
Exit Sub
End If

With Worksheets("Customers").Range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With


If FoundCell Is Nothing Then
'this shouldn't happen!
Beep
Else
Me.TextBox1.Value = FoundCell.Offset(0, 0).Value
Me.TextBox2.Value = FoundCell.Offset(0, 1).Value
Me.TextBox3.Value = FoundCell.Offset(0, 2).Value
Me.TextBox4.Value = FoundCell.Offset(0, 3).Value
Me.TextBox5.Value = FoundCell.Offset(0, 4).Value
Me.TextBox6.Value = FoundCell.Offset(0, 5).Value
Me.TextBox7.Value = FoundCell.Offset(0, 6).Value
Me.TextBox8.Value = FoundCell.Offset(0, 7).Value
Me.TextBox9.Value = FoundCell.Offset(0, 8).Value
Me.TextBox10.Value = FoundCell.Offset(0, 9).Value
Me.TextBox11.Value = FoundCell.Offset(0, 10).Value
Me.TextBox12.Value = FoundCell.Offset(0, 11).Value
Me.TextBox13.Value = FoundCell.Offset(0, 12).Value
Me.TextBox14.Value = FoundCell.Offset(0, 13).Value
Me.TextBox15.Value = FoundCell.Offset(0, 14).Value
Me.TextBox16.Value = FoundCell.Offset(0, 16).Value
Me.TextBox17.Value = FoundCell.Offset(0, 18).Value
Me.TextBox18.Value = FoundCell.Offset(0, 15).Value
Me.TextBox19.Value = FoundCell.Offset(0, 19).Value
Me.TextBox20.Value = FoundCell.Offset(0, 17).Value
Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(0, 20).Value) = "yes")
Me.CheckBox2.Value = CBool(LCase(FoundCell.Offset(0, 21).Value) = "yes")
Me.CheckBox3.Value = CBool(LCase(FoundCell.Offset(0, 22).Value) = "yes")
Me.CheckBox4.Value = CBool(LCase(FoundCell.Offset(0, 23).Value) = "yes")
Me.CheckBox5.Value = CBool(LCase(FoundCell.Offset(0, 24).Value) = "yes")
Me.CheckBox6.Value = CBool(LCase(FoundCell.Offset(0, 25).Value) = "yes")
Me.CheckBox7.Value = CBool(LCase(FoundCell.Offset(0, 26).Value) = "yes")
Me.CheckBox8.Value = CBool(LCase(FoundCell.Offset(0, 27).Value) = "yes")
Me.CheckBox9.Value = CBool(LCase(FoundCell.Offset(0, 28).Value) = "yes")
Me.CheckBox10.Value = CBool(LCase(FoundCell.Offset(0, 29).Value) = "yes")
Me.CheckBox11.Value = CBool(LCase(FoundCell.Offset(0, 30).Value) = "yes")
Me.CheckBox12.Value = CBool(LCase(FoundCell.Offset(0, 31).Value) = "yes")
Me.CheckBox13.Value = CBool(LCase(FoundCell.Offset(0, 33).Value) = "i
agree")
Me.CheckBox14.Value = CBool(LCase(FoundCell.Offset(0, 34).Value) = "i
disagree")
Me.ComboBox2.Value = FoundCell.Offset(0, 32).Value
Me.TextBox23.Value = FoundCell.Offset(0, 35).Value

'and on and on for 35 textboxes
'or that looping code from before if your textboxes are nicely named
End If

End Sub
 
D

Dave Peterson

Your commandbutton2 button is a Retrieve from the worksheet.

You could add a commandbutton3 button that says Save:

Untested, uncompiled:

Private Sub CommandButton3_Click()

Dim FoundCell As Range

If Me.ComboBox1.ListIndex = -1 Then
'nothing filled in
Beep
Exit Sub
End If

With Worksheets("Customers").Range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With


If FoundCell Is Nothing Then
'this shouldn't happen!
Beep
Else
foundcell.offset(0,0).value = me.textbox1.value
'lots more like this one!

if me.checkbox1.value = true then
foundcell.offset(0,20).value = "yes"
else
foundcell.offset(0,20).value = "no" 'or what you want
end if
'and lots more like this one
End If

End Sub

Essentially, you're just changing the direction of the assignments.
 
L

law

Hi Dave,
I have a button named enter details with the following code that saves the
data.
It`s just when I call up the data from the list index it doesnt save any
changes or additions but also it creates a new entry. For instance if I have
entered details for "Jones" before, I end up having two entries.
I need it to save any changes and save to the original row. Hope that makes
sense!

Private Sub CommandButton1_Click()
Dim LastRow As Object

Application.EnableEvents = False
Set LastRow = Sheet6.Range("a5000").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
LastRow.Offset(1, 3).Value = TextBox4.Text
LastRow.Offset(1, 4).Value = TextBox5.Text
LastRow.Offset(1, 5).Value = TextBox6.Text
LastRow.Offset(1, 6).Value = TextBox7.Text
LastRow.Offset(1, 7).Value = TextBox8.Text
LastRow.Offset(1, 8).Value = TextBox9.Text
LastRow.Offset(1, 9).Value = TextBox10.Text
LastRow.Offset(1, 10).Value = TextBox11.Text
LastRow.Offset(1, 11).Value = TextBox12.Text
LastRow.Offset(1, 12).Value = TextBox13.Text
LastRow.Offset(1, 13).Value = TextBox14.Text
LastRow.Offset(1, 14).Value = TextBox15.Text
LastRow.Offset(1, 15).Value = TextBox16.Text
LastRow.Offset(1, 16).Value = TextBox17.Text
LastRow.Offset(1, 17).Value = TextBox18.Text
LastRow.Offset(1, 18).Value = TextBox19.Text
LastRow.Offset(1, 19).Value = TextBox20.Text
LastRow.Offset(1, 20).Value = CheckBox1.Caption
LastRow.Offset(1, 21).Value = CheckBox2.Caption
LastRow.Offset(1, 22).Value = CheckBox3.Caption
LastRow.Offset(1, 23).Value = CheckBox4.Caption
LastRow.Offset(1, 24).Value = CheckBox5.Caption
LastRow.Offset(1, 25).Value = CheckBox6.Caption
LastRow.Offset(1, 26).Value = CheckBox7.Caption
LastRow.Offset(1, 27).Value = CheckBox8.Caption
LastRow.Offset(1, 28).Value = CheckBox9.Caption
LastRow.Offset(1, 29).Value = CheckBox10.Caption
LastRow.Offset(1, 30).Value = CheckBox11.Caption
LastRow.Offset(1, 31).Value = CheckBox12.Caption
LastRow.Offset(1, 32).Value = ComboBox2.Text
LastRow.Offset(1, 33).Value = CheckBox13.Caption
LastRow.Offset(1, 34).Value = CheckBox14.Caption
LastRow.Offset(1, 35).Value = TextBox23.Text

MsgBox "Do you want to enter another record?", vbYesNo

If vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
TextBox12.Text = ""
TextBox13.Text = ""
TextBox14.Text = ""
TextBox15.Text = ""
TextBox16.Text = ""
TextBox17.Text = ""
TextBox18.Text = ""
TextBox19.Text = ""
TextBox20.Text = ""
ComboBox2.Text = ""
TextBox23.Text = ""

TextBox1.SetFocus
Application.EnableEvents = True

Else
UserForm2.Hide
End If

End Sub
 
D

Dave Peterson

You could look for a match in the key field (combobox1???).

With Worksheets("Customers").Range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If foundcell is nothing then
'do the lastrow stuff
else
'do the foundcell.offset() stuff.
end if


Hi Dave,
I have a button named enter details with the following code that saves the
data.
It`s just when I call up the data from the list index it doesnt save any
changes or additions but also it creates a new entry. For instance if I have
entered details for "Jones" before, I end up having two entries.
I need it to save any changes and save to the original row. Hope that makes
sense!

Private Sub CommandButton1_Click()
Dim LastRow As Object

Application.EnableEvents = False
Set LastRow = Sheet6.Range("a5000").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
LastRow.Offset(1, 3).Value = TextBox4.Text
LastRow.Offset(1, 4).Value = TextBox5.Text
LastRow.Offset(1, 5).Value = TextBox6.Text
LastRow.Offset(1, 6).Value = TextBox7.Text
LastRow.Offset(1, 7).Value = TextBox8.Text
LastRow.Offset(1, 8).Value = TextBox9.Text
LastRow.Offset(1, 9).Value = TextBox10.Text
LastRow.Offset(1, 10).Value = TextBox11.Text
LastRow.Offset(1, 11).Value = TextBox12.Text
LastRow.Offset(1, 12).Value = TextBox13.Text
LastRow.Offset(1, 13).Value = TextBox14.Text
LastRow.Offset(1, 14).Value = TextBox15.Text
LastRow.Offset(1, 15).Value = TextBox16.Text
LastRow.Offset(1, 16).Value = TextBox17.Text
LastRow.Offset(1, 17).Value = TextBox18.Text
LastRow.Offset(1, 18).Value = TextBox19.Text
LastRow.Offset(1, 19).Value = TextBox20.Text
LastRow.Offset(1, 20).Value = CheckBox1.Caption
LastRow.Offset(1, 21).Value = CheckBox2.Caption
LastRow.Offset(1, 22).Value = CheckBox3.Caption
LastRow.Offset(1, 23).Value = CheckBox4.Caption
LastRow.Offset(1, 24).Value = CheckBox5.Caption
LastRow.Offset(1, 25).Value = CheckBox6.Caption
LastRow.Offset(1, 26).Value = CheckBox7.Caption
LastRow.Offset(1, 27).Value = CheckBox8.Caption
LastRow.Offset(1, 28).Value = CheckBox9.Caption
LastRow.Offset(1, 29).Value = CheckBox10.Caption
LastRow.Offset(1, 30).Value = CheckBox11.Caption
LastRow.Offset(1, 31).Value = CheckBox12.Caption
LastRow.Offset(1, 32).Value = ComboBox2.Text
LastRow.Offset(1, 33).Value = CheckBox13.Caption
LastRow.Offset(1, 34).Value = CheckBox14.Caption
LastRow.Offset(1, 35).Value = TextBox23.Text

MsgBox "Do you want to enter another record?", vbYesNo

If vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
TextBox12.Text = ""
TextBox13.Text = ""
TextBox14.Text = ""
TextBox15.Text = ""
TextBox16.Text = ""
TextBox17.Text = ""
TextBox18.Text = ""
TextBox19.Text = ""
TextBox20.Text = ""
ComboBox2.Text = ""
TextBox23.Text = ""

TextBox1.SetFocus
Application.EnableEvents = True

Else
UserForm2.Hide
End If

End Sub
Click to expand...
 
L

law

Sorry Dave, I still have the same problem.
It wont save any changes made to the data and it creates a duplicate data of
the clients details.
 
D

Dave Peterson

How did you determine if the record should be added or if an existing record
should be edited?
Sorry Dave, I still have the same problem.
It wont save any changes made to the data and it creates a duplicate data of
the clients details.
Click to expand...
 
L

law

Basicaly, I keep details of people whome visit this unit. Details such as
address, next of kin etc. One of the entries is the date of visit and whether
it was completed or cancelled. They are allowed only three visits in total.
So when a request comes in for someone to visit I first check, by use of
dropdown list (combobox1), if they are on the list then I click on the name
and his/her details come up. I then add the date of the next visit and save.
This is where I am having problems, it does not save the new details. Also
it adds a duplicate details so I end up with two entries of the same person.
Hopefully this gives you a better picture as to what I am trying to achieve.
 
L

law

Dave,
The code you sent me, I presume it searches downwards (ie row1 through to
row 65,000).

Can it search upwards. something like this:
Set LastRow = Sheet6.Range("a5000").End(xlUp)
Only a thought!

--
law


law said:
Basicaly, I keep details of people whome visit this unit. Details such as
address, next of kin etc. One of the entries is the date of visit and whether
it was completed or cancelled. They are allowed only three visits in total.
So when a request comes in for someone to visit I first check, by use of
dropdown list (combobox1), if they are on the list then I click on the name
and his/her details come up. I then add the date of the next visit and save.
This is where I am having problems, it does not save the new details. Also
it adds a duplicate details so I end up with two entries of the same person.
Hopefully this gives you a better picture as to what I am trying to achieve.
--
law


Dave Peterson said:
How did you determine if the record should be added or if an existing record
should be edited?
Click to expand...
Click to expand...
 
D

Dave Peterson

This portion:

With Worksheets("Customers").Range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

looks at column A (A:A) and searchs for the next match (xlnext) after the last
cell in that range (.cells(.cells.count) does that). You can reverse the search
by changing a couple of parms:

With Worksheets("Customers").Range("A:A")
Set FoundCell = .Cells.Find(what:=Me.ComboBox1.Value, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False)
End With

It starts in the first cell (.cells(1) and looks for the previous (xlprevious)
match--that's the backwards part.
 
D

Dave Peterson

Ps.

If you wanted to search manually (via edit|Find), you could hit the Shift-key on
that dialog and you'll be searching for the previous match. (You didn't ask,
but I thought it was interesting!)
 
D

Dave Peterson

pps.

Hit and hold the shift key while you click the "Find Next" button is a better
way of writing it.
 
L

law

Dave,
That did the trick. It works just great now.
There is another question of deleting duplicate names but I should enter a
new post for that.
Many thanks for your help.
 
L

law

Dave,
That did the trick. It works just great now.
There is another question of deleting duplicate names but I should enter a
new post for that.
Many thanks for your help.

--
law


Dave Peterson said:
pps.

Hit and hold the shift key while you click the "Find Next" button is a better
way of writing it.
Click to expand...
 
Top