| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter list and display data.
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" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter list and display data.
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" wrote: 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" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter list and display data.
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. law wrote: 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" wrote: 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" wrote: 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 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter list and display data.
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!) law wrote: 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" wrote: 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" wrote: 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 -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter list and display data.
pps.
Hit and hold the shift key while you click the "Find Next" button is a better way of writing it. law wrote: 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" wrote: 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" wrote: 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 -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter list and display data.
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" wrote: 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. law wrote: 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" wrote: 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" wrote: 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter list and display data.
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" wrote: pps. Hit and hold the shift key while you click the "Find Next" button is a better way of writing it. law wrote: 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" wrote: 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" wrote: 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do i filter data on one worksheet and display on another. | Excel Discussion (Misc queries) | |||
| filters how do i keep the display list empty until filter applied | Excel Discussion (Misc queries) | |||
| DATA FILTER ARROWS DISPLAY WHILE INACTIVE | Excel Worksheet Functions | |||
| filter data from 10+workbooks and display in separate workbook? | Excel Discussion (Misc queries) | |||
| Data Filter - Not all rows in spreadsheet will display in Autofilt | Excel Worksheet Functions | |||
Linear Mode
