Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Filter list and display data.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
law law is offline
external usenet poster
  
Posts: 20
Default Filter list and display data.
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
--
law
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default Filter list and display data.
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.

law wrote:

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
--
law

--

Dave Peterson
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
law law is offline
external usenet poster
  
Posts: 20
Default Filter list and display data.
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
--
law


"Dave Peterson" wrote:

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.

law wrote:

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
--
law

--

Dave Peterson

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default Filter list and display data.
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



law wrote:

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
--
law

"Dave Peterson" wrote:

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.

law wrote:

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
--
law

--

Dave Peterson


--

Dave Peterson
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
law law is offline
external usenet poster
  
Posts: 20
Default Filter list and display data.
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.
--
law


"Dave Peterson" wrote:

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



law wrote:

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
--
law

"Dave Peterson" wrote:

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.

law wrote:

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
--
law

--

Dave Peterson


--

Dave Peterson

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default Filter list and display data.
How did you determine if the record should be added or if an existing record
should be edited?

law wrote:

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.
--
law

"Dave Peterson" wrote:

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



law wrote:

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
--
law

"Dave Peterson" wrote:

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.

law wrote:

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
--
law

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i filter data on one worksheet and display on another. excellent Excel Discussion (Misc queries) 1 June 22nd 06 12:15 PM
filters how do i keep the display list empty until filter applied Beewfc Excel Discussion (Misc queries) 1 April 10th 06 01:08 PM
DATA FILTER ARROWS DISPLAY WHILE INACTIVE lassie Excel Worksheet Functions 0 August 3rd 05 02:41 PM
filter data from 10+workbooks and display in separate workbook? crush Excel Discussion (Misc queries) 1 July 8th 05 08:46 PM
Data Filter - Not all rows in spreadsheet will display in Autofilt Excel Help Excel Worksheet Functions 1 November 17th 04 05:40 PM


All times are GMT +1. The time now is 07:26 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement