| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter question?
At the moment I have a data sheet that details are entered using a userform.
It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
How are you displaying those 32 columns?
If it's part of a giant listbox or combobox, then if you were only using up to 10 columns, you could loop through the important column and add each item that matches your requirement -- and include the cells to the right. But since you're using columns A:AF (32 columns), you can filter your range to show what you want. Then copy the visible rows to a new location (on a new worksheet--maybe hidden???). Then use the range on that worksheet as the rowsource. If you're displaying each of those fields in a separate textbox (or label or ....), you could loop through the visible cells in column A and do something like: Dim HowManyVisRows as long dim VisRng as range dim myCell as range dim iCtr as long 'apply the filter someway With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1 if howmanyvisrows 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1, 1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else for each mycell in visrng.cells me.textbox1.value = mycell.value me.textbox2.value = mycell.offset(0,1).value 'etc 'or if the textboxes are named nicely. for ictr = 1 to 32 me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value next ictr end if Untested, uncompiled--watch for typos. AOP wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
Thanks Dave,
Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: How are you displaying those 32 columns? If it's part of a giant listbox or combobox, then if you were only using up to 10 columns, you could loop through the important column and add each item that matches your requirement -- and include the cells to the right. But since you're using columns A:AF (32 columns), you can filter your range to show what you want. Then copy the visible rows to a new location (on a new worksheet--maybe hidden???). Then use the range on that worksheet as the rowsource. If you're displaying each of those fields in a separate textbox (or label or ....), you could loop through the visible cells in column A and do something like: Dim HowManyVisRows as long dim VisRng as range dim myCell as range dim iCtr as long 'apply the filter someway With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1 if howmanyvisrows 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1, 1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else for each mycell in visrng.cells me.textbox1.value = mycell.value me.textbox2.value = mycell.offset(0,1).value 'etc 'or if the textboxes are named nicely. for ictr = 1 to 32 me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value next ictr end if Untested, uncompiled--watch for typos. AOP wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
If it's a followup to this question, just post in the same thread.
If it's a different question, start a new thread. You'll find that there are lots of people who read the newsgroups who can help. AOP wrote: Thanks Dave, Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: How are you displaying those 32 columns? If it's part of a giant listbox or combobox, then if you were only using up to 10 columns, you could loop through the important column and add each item that matches your requirement -- and include the cells to the right. But since you're using columns A:AF (32 columns), you can filter your range to show what you want. Then copy the visible rows to a new location (on a new worksheet--maybe hidden???). Then use the range on that worksheet as the rowsource. If you're displaying each of those fields in a separate textbox (or label or ....), you could loop through the visible cells in column A and do something like: Dim HowManyVisRows as long dim VisRng as range dim myCell as range dim iCtr as long 'apply the filter someway With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1 if howmanyvisrows 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1, 1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else for each mycell in visrng.cells me.textbox1.value = mycell.value me.textbox2.value = mycell.offset(0,1).value 'etc 'or if the textboxes are named nicely. for ictr = 1 to 32 me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value next ictr end if Untested, uncompiled--watch for typos. AOP wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
Sorry Dave,
I cant get my head round this. could you walk me through this.......The sheet that has the data is sheet(6) "Customers" columns A to AJ. Sheet(2) is the menu sheet with one button, when pressed userform(2) is shown and the data entered in that gets stored in sheet 6. That works well. I'm assuming that I need to put an additional button on sheet (2) to run the code? or I'm well off the mark! -- AOP "Dave Peterson" wrote: If it's a followup to this question, just post in the same thread. If it's a different question, start a new thread. You'll find that there are lots of people who read the newsgroups who can help. AOP wrote: Thanks Dave, Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: How are you displaying those 32 columns? If it's part of a giant listbox or combobox, then if you were only using up to 10 columns, you could loop through the important column and add each item that matches your requirement -- and include the cells to the right. But since you're using columns A:AF (32 columns), you can filter your range to show what you want. Then copy the visible rows to a new location (on a new worksheet--maybe hidden???). Then use the range on that worksheet as the rowsource. If you're displaying each of those fields in a separate textbox (or label or ....), you could loop through the visible cells in column A and do something like: Dim HowManyVisRows as long dim VisRng as range dim myCell as range dim iCtr as long 'apply the filter someway With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1 if howmanyvisrows 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1, 1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else for each mycell in visrng.cells me.textbox1.value = mycell.value me.textbox2.value = mycell.offset(0,1).value 'etc 'or if the textboxes are named nicely. for ictr = 1 to 32 me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value next ictr end if Untested, uncompiled--watch for typos. AOP wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
I don't understand what you're doing.
Either you'll have to provide more information or maybe someone else will chime in. AOP wrote: Sorry Dave, I cant get my head round this. could you walk me through this.......The sheet that has the data is sheet(6) "Customers" columns A to AJ. Sheet(2) is the menu sheet with one button, when pressed userform(2) is shown and the data entered in that gets stored in sheet 6. That works well. I'm assuming that I need to put an additional button on sheet (2) to run the code? or I'm well off the mark! -- AOP "Dave Peterson" wrote: If it's a followup to this question, just post in the same thread. If it's a different question, start a new thread. You'll find that there are lots of people who read the newsgroups who can help. AOP wrote: Thanks Dave, Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: How are you displaying those 32 columns? If it's part of a giant listbox or combobox, then if you were only using up to 10 columns, you could loop through the important column and add each item that matches your requirement -- and include the cells to the right. But since you're using columns A:AF (32 columns), you can filter your range to show what you want. Then copy the visible rows to a new location (on a new worksheet--maybe hidden???). Then use the range on that worksheet as the rowsource. If you're displaying each of those fields in a separate textbox (or label or ....), you could loop through the visible cells in column A and do something like: Dim HowManyVisRows as long dim VisRng as range dim myCell as range dim iCtr as long 'apply the filter someway With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1 if howmanyvisrows 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1, 1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else for each mycell in visrng.cells me.textbox1.value = mycell.value me.textbox2.value = mycell.offset(0,1).value 'etc 'or if the textboxes are named nicely. for ictr = 1 to 32 me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value next ictr end if Untested, uncompiled--watch for typos. AOP wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
Ok, I'll try and explain.
The workbook has been altered and now has two sheets ("Customer" and "Menu"). I also have a userform (2) with 35 textboxes. The Menu sheet has one button that opens the userform. I enter the details in the userform (2) and the data gets stored in sheet ("Customer") columns A to AJ rows 1 to 5000. What I'm trying to do is to be able search by name (column A) and call up individual customer details and have it displayed on the userform, if that is possible. -- AOP "Dave Peterson" wrote: I don't understand what you're doing. Either you'll have to provide more information or maybe someone else will chime in. AOP wrote: Sorry Dave, I cant get my head round this. could you walk me through this.......The sheet that has the data is sheet(6) "Customers" columns A to AJ. Sheet(2) is the menu sheet with one button, when pressed userform(2) is shown and the data entered in that gets stored in sheet 6. That works well. I'm assuming that I need to put an additional button on sheet (2) to run the code? or I'm well off the mark! -- AOP "Dave Peterson" wrote: If it's a followup to this question, just post in the same thread. If it's a different question, start a new thread. You'll find that there are lots of people who read the newsgroups who can help. AOP wrote: Thanks Dave, Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: How are you displaying those 32 columns? If it's part of a giant listbox or combobox, then if you were only using up to 10 columns, you could loop through the important column and add each item that matches your requirement -- and include the cells to the right. But since you're using columns A:AF (32 columns), you can filter your range to show what you want. Then copy the visible rows to a new location (on a new worksheet--maybe hidden???). Then use the range on that worksheet as the rowsource. If you're displaying each of those fields in a separate textbox (or label or ....), you could loop through the visible cells in column A and do something like: Dim HowManyVisRows as long dim VisRng as range dim myCell as range dim iCtr as long 'apply the filter someway With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1 if howmanyvisrows 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1, 1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else for each mycell in visrng.cells me.textbox1.value = mycell.value me.textbox2.value = mycell.offset(0,1).value 'etc 'or if the textboxes are named nicely. for ictr = 1 to 32 me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value next ictr end if Untested, uncompiled--watch for typos. AOP wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
All the names are unique in column A?
I'd put a combobox on the userform that displays those unique values from Column A. Then you could search for that username and when you find it, you could fill out the 35 textboxes. I'd put a button that retrieved the values after the user chose the customer name from the combobox. Option Explicit Private Sub CommandButton1_Click() Dim FoundCell as Range if me.combobox1.listindex = -1 then 'nothing filled in beep exit sub end if With worksheets("Customer") set Foundcell = .range("a:a").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,1).value 'and on and on for 35 textboxes 'or that looping code from before if your textboxes are nicely named end if End sub Uncompiled, untested. Watch for typos! AOP wrote: Ok, I'll try and explain. The workbook has been altered and now has two sheets ("Customer" and "Menu"). I also have a userform (2) with 35 textboxes. The Menu sheet has one button that opens the userform. I enter the details in the userform (2) and the data gets stored in sheet ("Customer") columns A to AJ rows 1 to 5000. What I'm trying to do is to be able search by name (column A) and call up individual customer details and have it displayed on the userform, if that is possible. -- AOP "Dave Peterson" wrote: I don't understand what you're doing. Either you'll have to provide more information or maybe someone else will chime in. AOP wrote: Sorry Dave, I cant get my head round this. could you walk me through this.......The sheet that has the data is sheet(6) "Customers" columns A to AJ. Sheet(2) is the menu sheet with one button, when pressed userform(2) is shown and the data entered in that gets stored in sheet 6. That works well. I'm assuming that I need to put an additional button on sheet (2) to run the code? or I'm well off the mark! -- AOP "Dave Peterson" wrote: If it's a followup to this question, just post in the same thread. If it's a different question, start a new thread. You'll find that there are lots of people who read the newsgroups who can help. AOP wrote: Thanks Dave, Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: How are you displaying those 32 columns? If it's part of a giant listbox or combobox, then if you were only using up to 10 columns, you could loop through the important column and add each item that matches your requirement -- and include the cells to the right. But since you're using columns A:AF (32 columns), you can filter your range to show what you want. Then copy the visible rows to a new location (on a new worksheet--maybe hidden???). Then use the range on that worksheet as the rowsource. If you're displaying each of those fields in a separate textbox (or label or ....), you could loop through the visible cells in column A and do something like: Dim HowManyVisRows as long dim VisRng as range dim myCell as range dim iCtr as long 'apply the filter someway With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1 if howmanyvisrows 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1, 1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else for each mycell in visrng.cells me.textbox1.value = mycell.value me.textbox2.value = mycell.offset(0,1).value 'etc 'or if the textboxes are named nicely. for ictr = 1 to 32 me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value next ictr end if Untested, uncompiled--watch for typos. AOP wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
Dave,
I followed you instructions, all fine until it runs the following happens: I get run 13 error. with the following lines highlighted. With Worksheets("Customers") Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) any ideas -- AOP "Dave Peterson" wrote: All the names are unique in column A? I'd put a combobox on the userform that displays those unique values from Column A. Then you could search for that username and when you find it, you could fill out the 35 textboxes. I'd put a button that retrieved the values after the user chose the customer name from the combobox. Option Explicit Private Sub CommandButton1_Click() Dim FoundCell as Range if me.combobox1.listindex = -1 then 'nothing filled in beep exit sub end if With worksheets("Customer") set Foundcell = .range("a:a").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,1).value 'and on and on for 35 textboxes 'or that looping code from before if your textboxes are nicely named end if End sub Uncompiled, untested. Watch for typos! AOP wrote: Ok, I'll try and explain. The workbook has been altered and now has two sheets ("Customer" and "Menu"). I also have a userform (2) with 35 textboxes. The Menu sheet has one button that opens the userform. I enter the details in the userform (2) and the data gets stored in sheet ("Customer") columns A to AJ rows 1 to 5000. What I'm trying to do is to be able search by name (column A) and call up individual customer details and have it displayed on the userform, if that is possible. -- AOP "Dave Peterson" wrote: I don't understand what you're doing. Either you'll have to provide more information or maybe someone else will chime in. AOP wrote: Sorry Dave, I cant get my head round this. could you walk me through this.......The sheet that has the data is sheet(6) "Customers" columns A to AJ. Sheet(2) is the menu sheet with one button, when pressed userform(2) is shown and the data entered in that gets stored in sheet 6. That works well. I'm assuming that I need to put an additional button on sheet (2) to run the code? or I'm well off the mark! -- AOP "Dave Peterson" wrote: If it's a followup to this question, just post in the same thread. If it's a different question, start a new thread. You'll find that there are lots of people who read the newsgroups who can help. AOP wrote: Thanks Dave, Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: How are you displaying those 32 columns? If it's part of a giant listbox or combobox, then if you were only using up to 10 columns, you could loop through the important column and add each item that matches your requirement -- and include the cells to the right. But since you're using columns A:AF (32 columns), you can filter your range to show what you want. Then copy the visible rows to a new location (on a new worksheet--maybe hidden???). Then use the range on that worksheet as the rowsource. If you're displaying each of those fields in a separate textbox (or label or ....), you could loop through the visible cells in column A and do something like: Dim HowManyVisRows as long dim VisRng as range dim myCell as range dim iCtr as long 'apply the filter someway With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1 if howmanyvisrows 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1, 1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else for each mycell in visrng.cells me.textbox1.value = mycell.value me.textbox2.value = mycell.offset(0,1).value 'etc 'or if the textboxes are named nicely. for ictr = 1 to 32 me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value next ictr end if Untested, uncompiled--watch for typos. AOP wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
Typo on my part:
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 AOP wrote: Dave, I followed you instructions, all fine until it runs the following happens: I get run 13 error. with the following lines highlighted. With Worksheets("Customers") Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) any ideas -- AOP "Dave Peterson" wrote: All the names are unique in column A? I'd put a combobox on the userform that displays those unique values from Column A. Then you could search for that username and when you find it, you could fill out the 35 textboxes. I'd put a button that retrieved the values after the user chose the customer name from the combobox. Option Explicit Private Sub CommandButton1_Click() Dim FoundCell as Range if me.combobox1.listindex = -1 then 'nothing filled in beep exit sub end if With worksheets("Customer") set Foundcell = .range("a:a").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,1).value 'and on and on for 35 textboxes 'or that looping code from before if your textboxes are nicely named end if End sub Uncompiled, untested. Watch for typos! AOP wrote: Ok, I'll try and explain. The workbook has been altered and now has two sheets ("Customer" and "Menu"). I also have a userform (2) with 35 textboxes. The Menu sheet has one button that opens the userform. I enter the details in the userform (2) and the data gets stored in sheet ("Customer") columns A to AJ rows 1 to 5000. What I'm trying to do is to be able search by name (column A) and call up individual customer details and have it displayed on the userform, if that is possible. -- AOP "Dave Peterson" wrote: I don't understand what you're doing. Either you'll have to provide more information or maybe someone else will chime in. AOP wrote: Sorry Dave, I cant get my head round this. could you walk me through this.......The sheet that has the data is sheet(6) "Customers" columns A to AJ. Sheet(2) is the menu sheet with one button, when pressed userform(2) is shown and the data entered in that gets stored in sheet 6. That works well. I'm assuming that I need to put an additional button on sheet (2) to run the code? or I'm well off the mark! -- AOP "Dave Peterson" wrote: If it's a followup to this question, just post in the same thread. If it's a different question, start a new thread. You'll find that there are lots of people who read the newsgroups who can help. AOP wrote: Thanks Dave, Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: How are you displaying those 32 columns? If it's part of a giant listbox or combobox, then if you were only using up to 10 columns, you could loop through the important column and add each item that matches your requirement -- and include the cells to the right. But since you're using columns A:AF (32 columns), you can filter your range to show what you want. Then copy the visible rows to a new location (on a new worksheet--maybe hidden???). Then use the range on that worksheet as the rowsource. If you're displaying each of those fields in a separate textbox (or label or ....), you could loop through the visible cells in column A and do something like: Dim HowManyVisRows as long dim VisRng as range dim myCell as range dim iCtr as long 'apply the filter someway With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1 if howmanyvisrows 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1, 1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else for each mycell in visrng.cells me.textbox1.value = mycell.value me.textbox2.value = mycell.offset(0,1).value 'etc 'or if the textboxes are named nicely. for ictr = 1 to 32 me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value next ictr end if Untested, uncompiled--watch for typos. AOP wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
Thanks very much Dave,
Its working fine now...geat stuff!. But, only one more thing. On my userform I have a checkbox, when selected it saves it on the data sheet as text "Yes" with the following code: LastRow.Offset(1, 20).Value = CheckBox1.Caption In your code how do I recall the checkbox as a tick? I have written it as: Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value -- AOP "Dave Peterson" wrote: Typo on my part: 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 AOP wrote: Dave, I followed you instructions, all fine until it runs the following happens: I get run 13 error. with the following lines highlighted. With Worksheets("Customers") Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) any ideas -- AOP "Dave Peterson" wrote: All the names are unique in column A? I'd put a combobox on the userform that displays those unique values from Column A. Then you could search for that username and when you find it, you could fill out the 35 textboxes. I'd put a button that retrieved the values after the user chose the customer name from the combobox. Option Explicit Private Sub CommandButton1_Click() Dim FoundCell as Range if me.combobox1.listindex = -1 then 'nothing filled in beep exit sub end if With worksheets("Customer") set Foundcell = .range("a:a").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,1).value 'and on and on for 35 textboxes 'or that looping code from before if your textboxes are nicely named end if End sub Uncompiled, untested. Watch for typos! AOP wrote: Ok, I'll try and explain. The workbook has been altered and now has two sheets ("Customer" and "Menu"). I also have a userform (2) with 35 textboxes. The Menu sheet has one button that opens the userform. I enter the details in the userform (2) and the data gets stored in sheet ("Customer") columns A to AJ rows 1 to 5000. What I'm trying to do is to be able search by name (column A) and call up individual customer details and have it displayed on the userform, if that is possible. -- AOP "Dave Peterson" wrote: I don't understand what you're doing. Either you'll have to provide more information or maybe someone else will chime in. AOP wrote: Sorry Dave, I cant get my head round this. could you walk me through this.......The sheet that has the data is sheet(6) "Customers" columns A to AJ. Sheet(2) is the menu sheet with one button, when pressed userform(2) is shown and the data entered in that gets stored in sheet 6. That works well. I'm assuming that I need to put an additional button on sheet (2) to run the code? or I'm well off the mark! -- AOP "Dave Peterson" wrote: If it's a followup to this question, just post in the same thread. If it's a different question, start a new thread. You'll find that there are lots of people who read the newsgroups who can help. AOP wrote: Thanks Dave, Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: How are you displaying those 32 columns? If it's part of a giant listbox or combobox, then if you were only using up to 10 columns, you could loop through the important column and add each item that matches your requirement -- and include the cells to the right. But since you're using columns A:AF (32 columns), you can filter your range to show what you want. Then copy the visible rows to a new location (on a new worksheet--maybe hidden???). Then use the range on that worksheet as the rowsource. If you're displaying each of those fields in a separate textbox (or label or ....), you could loop through the visible cells in column A and do something like: Dim HowManyVisRows as long dim VisRng as range dim myCell as range dim iCtr as long 'apply the filter someway With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1 if howmanyvisrows 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1, 1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else for each mycell in visrng.cells me.textbox1.value = mycell.value me.textbox2.value = mycell.offset(0,1).value 'etc 'or if the textboxes are named nicely. for ictr = 1 to 32 me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value next ictr end if Untested, uncompiled--watch for typos. AOP wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
The checkbox value will be true/false.
So you could use: if lcase(foundcell.offset(0,20).value) = "yes" then me.checkbox1.value = true else me.checkbox1.value = false end if or me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes") AOP wrote: Thanks very much Dave, Its working fine now...geat stuff!. But, only one more thing. On my userform I have a checkbox, when selected it saves it on the data sheet as text "Yes" with the following code: LastRow.Offset(1, 20).Value = CheckBox1.Caption In your code how do I recall the checkbox as a tick? I have written it as: Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value -- AOP "Dave Peterson" wrote: Typo on my part: 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 AOP wrote: Dave, I followed you instructions, all fine until it runs the following happens: I get run 13 error. with the following lines highlighted. With Worksheets("Customers") Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) any ideas -- AOP "Dave Peterson" wrote: All the names are unique in column A? I'd put a combobox on the userform that displays those unique values from Column A. Then you could search for that username and when you find it, you could fill out the 35 textboxes. I'd put a button that retrieved the values after the user chose the customer name from the combobox. Option Explicit Private Sub CommandButton1_Click() Dim FoundCell as Range if me.combobox1.listindex = -1 then 'nothing filled in beep exit sub end if With worksheets("Customer") set Foundcell = .range("a:a").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,1).value 'and on and on for 35 textboxes 'or that looping code from before if your textboxes are nicely named end if End sub Uncompiled, untested. Watch for typos! AOP wrote: Ok, I'll try and explain. The workbook has been altered and now has two sheets ("Customer" and "Menu"). I also have a userform (2) with 35 textboxes. The Menu sheet has one button that opens the userform. I enter the details in the userform (2) and the data gets stored in sheet ("Customer") columns A to AJ rows 1 to 5000. What I'm trying to do is to be able search by name (column A) and call up individual customer details and have it displayed on the userform, if that is possible. -- AOP "Dave Peterson" wrote: I don't understand what you're doing. Either you'll have to provide more information or maybe someone else will chime in. AOP wrote: Sorry Dave, I cant get my head round this. could you walk me through this.......The sheet that has the data is sheet(6) "Customers" columns A to AJ. Sheet(2) is the menu sheet with one button, when pressed userform(2) is shown and the data entered in that gets stored in sheet 6. That works well. I'm assuming that I need to put an additional button on sheet (2) to run the code? or I'm well off the mark! -- AOP "Dave Peterson" wrote: If it's a followup to this question, just post in the same thread. If it's a different question, start a new thread. You'll find that there are lots of people who read the newsgroups who can help. AOP wrote: Thanks Dave, Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: How are you displaying those 32 columns? If it's part of a giant listbox or combobox, then if you were only using up to 10 columns, you could loop through the important column and add each item that matches your requirement -- and include the cells to the right. But since you're using columns A:AF (32 columns), you can filter your range to show what you want. Then copy the visible rows to a new location (on a new worksheet--maybe hidden???). Then use the range on that worksheet as the rowsource. If you're displaying each of those fields in a separate textbox (or label or ....), you could loop through the visible cells in column A and do something like: Dim HowManyVisRows as long dim VisRng as range dim myCell as range dim iCtr as long 'apply the filter someway With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1 if howmanyvisrows 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1, 1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else for each mycell in visrng.cells me.textbox1.value = mycell.value me.textbox2.value = mycell.offset(0,1).value 'etc 'or if the textboxes are named nicely. for ictr = 1 to 32 me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value next ictr end if Untested, uncompiled--watch for typos. AOP wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
Dave,
I used the following code Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(1, 20).Value) = "yes") and nothing happens. All I get is the ticks on all the checkboxes in a light grey. Is there something I need to do in the properties maybe? -- AOP "Dave Peterson" wrote: The checkbox value will be true/false. So you could use: if lcase(foundcell.offset(0,20).value) = "yes" then me.checkbox1.value = true else me.checkbox1.value = false end if or me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes") AOP wrote: Thanks very much Dave, Its working fine now...geat stuff!. But, only one more thing. On my userform I have a checkbox, when selected it saves it on the data sheet as text "Yes" with the following code: LastRow.Offset(1, 20).Value = CheckBox1.Caption In your code how do I recall the checkbox as a tick? I have written it as: Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value -- AOP "Dave Peterson" wrote: Typo on my part: 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 AOP wrote: Dave, I followed you instructions, all fine until it runs the following happens: I get run 13 error. with the following lines highlighted. With Worksheets("Customers") Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) any ideas -- AOP "Dave Peterson" wrote: All the names are unique in column A? I'd put a combobox on the userform that displays those unique values from Column A. Then you could search for that username and when you find it, you could fill out the 35 textboxes. I'd put a button that retrieved the values after the user chose the customer name from the combobox. Option Explicit Private Sub CommandButton1_Click() Dim FoundCell as Range if me.combobox1.listindex = -1 then 'nothing filled in beep exit sub end if With worksheets("Customer") set Foundcell = .range("a:a").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,1).value 'and on and on for 35 textboxes 'or that looping code from before if your textboxes are nicely named end if End sub Uncompiled, untested. Watch for typos! AOP wrote: Ok, I'll try and explain. The workbook has been altered and now has two sheets ("Customer" and "Menu"). I also have a userform (2) with 35 textboxes. The Menu sheet has one button that opens the userform. I enter the details in the userform (2) and the data gets stored in sheet ("Customer") columns A to AJ rows 1 to 5000. What I'm trying to do is to be able search by name (column A) and call up individual customer details and have it displayed on the userform, if that is possible. -- AOP "Dave Peterson" wrote: I don't understand what you're doing. Either you'll have to provide more information or maybe someone else will chime in. AOP wrote: Sorry Dave, I cant get my head round this. could you walk me through this.......The sheet that has the data is sheet(6) "Customers" columns A to AJ. Sheet(2) is the menu sheet with one button, when pressed userform(2) is shown and the data entered in that gets stored in sheet 6. That works well. I'm assuming that I need to put an additional button on sheet (2) to run the code? or I'm well off the mark! -- AOP "Dave Peterson" wrote: If it's a followup to this question, just post in the same thread. If it's a different question, start a new thread. You'll find that there are lots of people who read the newsgroups who can help. AOP wrote: Thanks Dave, Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: How are you displaying those 32 columns? If it's part of a giant listbox or combobox, then if you were only using up to 10 columns, you could loop through the important column and add each item that matches your requirement -- and include the cells to the right. But since you're using columns A:AF (32 columns), you can filter your range to show what you want. Then copy the visible rows to a new location (on a new worksheet--maybe hidden???). Then use the range on that worksheet as the rowsource. If you're displaying each of those fields in a separate textbox (or label or ....), you could loop through the visible cells in column A and do something like: Dim HowManyVisRows as long dim VisRng as range dim myCell as range dim iCtr as long 'apply the filter someway With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1 if howmanyvisrows 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1, 1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else for each mycell in visrng.cells me.textbox1.value = mycell.value me.textbox2.value = mycell.offset(0,1).value 'etc 'or if the textboxes are named nicely. for ictr = 1 to 32 me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value next ictr end if Untested, uncompiled--watch for typos. AOP wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
Is there a reason you changed .offset(0,20) to .offset(1,20)?
Did you really mean to go to the next row down? And did you change the .triplestate property from False to True? If you want checked and unchecked, that property should be False. And that code only looks at one checkbox and one cell. Did you assign a common linked cell to that checkbox? AOP wrote: Dave, I used the following code Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(1, 20).Value) = "yes") and nothing happens. All I get is the ticks on all the checkboxes in a light grey. Is there something I need to do in the properties maybe? -- AOP "Dave Peterson" wrote: The checkbox value will be true/false. So you could use: if lcase(foundcell.offset(0,20).value) = "yes" then me.checkbox1.value = true else me.checkbox1.value = false end if or me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes") AOP wrote: Thanks very much Dave, Its working fine now...geat stuff!. But, only one more thing. On my userform I have a checkbox, when selected it saves it on the data sheet as text "Yes" with the following code: LastRow.Offset(1, 20).Value = CheckBox1.Caption In your code how do I recall the checkbox as a tick? I have written it as: Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value -- AOP "Dave Peterson" wrote: Typo on my part: 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 AOP wrote: Dave, I followed you instructions, all fine until it runs the following happens: I get run 13 error. with the following lines highlighted. With Worksheets("Customers") Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) any ideas -- AOP "Dave Peterson" wrote: All the names are unique in column A? I'd put a combobox on the userform that displays those unique values from Column A. Then you could search for that username and when you find it, you could fill out the 35 textboxes. I'd put a button that retrieved the values after the user chose the customer name from the combobox. Option Explicit Private Sub CommandButton1_Click() Dim FoundCell as Range if me.combobox1.listindex = -1 then 'nothing filled in beep exit sub end if With worksheets("Customer") set Foundcell = .range("a:a").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,1).value 'and on and on for 35 textboxes 'or that looping code from before if your textboxes are nicely named end if End sub Uncompiled, untested. Watch for typos! AOP wrote: Ok, I'll try and explain. The workbook has been altered and now has two sheets ("Customer" and "Menu"). I also have a userform (2) with 35 textboxes. The Menu sheet has one button that opens the userform. I enter the details in the userform (2) and the data gets stored in sheet ("Customer") columns A to AJ rows 1 to 5000. What I'm trying to do is to be able search by name (column A) and call up individual customer details and have it displayed on the userform, if that is possible. -- AOP "Dave Peterson" wrote: I don't understand what you're doing. Either you'll have to provide more information or maybe someone else will chime in. AOP wrote: Sorry Dave, I cant get my head round this. could you walk me through this.......The sheet that has the data is sheet(6) "Customers" columns A to AJ. Sheet(2) is the menu sheet with one button, when pressed userform(2) is shown and the data entered in that gets stored in sheet 6. That works well. I'm assuming that I need to put an additional button on sheet (2) to run the code? or I'm well off the mark! -- AOP "Dave Peterson" wrote: If it's a followup to this question, just post in the same thread. If it's a different question, start a new thread. You'll find that there are lots of people who read the newsgroups who can help. AOP wrote: Thanks Dave, Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: How are you displaying those 32 columns? If it's part of a giant listbox or combobox, then if you were only using up to 10 columns, you could loop through the important column and add each item that matches your requirement -- and include the cells to the right. But since you're using columns A:AF (32 columns), you can filter your range to show what you want. Then copy the visible rows to a new location (on a new worksheet--maybe hidden???). Then use the range on that worksheet as the rowsource. If you're displaying each of those fields in a separate textbox (or label or ....), you could loop through the visible cells in column A and do something like: Dim HowManyVisRows as long dim VisRng as range dim myCell as range dim iCtr as long 'apply the filter someway With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1 if howmanyvisrows 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1, 1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else for each mycell in visrng.cells me.textbox1.value = mycell.value me.textbox2.value = mycell.offset(0,1).value 'etc 'or if the textboxes are named nicely. for ictr = 1 to 32 me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value next ictr end if Untested, uncompiled--watch for typos. AOP wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
Hi Dave,
Yes I changed the offset to 1, because there is a heading row and after several different ways that was the only code that would work. The triplestate was not changed, I checked and is at "False". And there is no link set up from the checkbox to a cell. -- AOP "Dave Peterson" wrote: Is there a reason you changed .offset(0,20) to .offset(1,20)? Did you really mean to go to the next row down? And did you change the .triplestate property from False to True? If you want checked and unchecked, that property should be False. And that code only looks at one checkbox and one cell. Did you assign a common linked cell to that checkbox? AOP wrote: Dave, I used the following code Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(1, 20).Value) = "yes") and nothing happens. All I get is the ticks on all the checkboxes in a light grey. Is there something I need to do in the properties maybe? -- AOP "Dave Peterson" wrote: The checkbox value will be true/false. So you could use: if lcase(foundcell.offset(0,20).value) = "yes" then me.checkbox1.value = true else me.checkbox1.value = false end if or me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes") AOP wrote: Thanks very much Dave, Its working fine now...geat stuff!. But, only one more thing. On my userform I have a checkbox, when selected it saves it on the data sheet as text "Yes" with the following code: LastRow.Offset(1, 20).Value = CheckBox1.Caption In your code how do I recall the checkbox as a tick? I have written it as: Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value -- AOP "Dave Peterson" wrote: Typo on my part: 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 AOP wrote: Dave, I followed you instructions, all fine until it runs the following happens: I get run 13 error. with the following lines highlighted. With Worksheets("Customers") Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) any ideas -- AOP "Dave Peterson" wrote: All the names are unique in column A? I'd put a combobox on the userform that displays those unique values from Column A. Then you could search for that username and when you find it, you could fill out the 35 textboxes. I'd put a button that retrieved the values after the user chose the customer name from the combobox. Option Explicit Private Sub CommandButton1_Click() Dim FoundCell as Range if me.combobox1.listindex = -1 then 'nothing filled in beep exit sub end if With worksheets("Customer") set Foundcell = .range("a:a").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,1).value 'and on and on for 35 textboxes 'or that looping code from before if your textboxes are nicely named end if End sub Uncompiled, untested. Watch for typos! AOP wrote: Ok, I'll try and explain. The workbook has been altered and now has two sheets ("Customer" and "Menu"). I also have a userform (2) with 35 textboxes. The Menu sheet has one button that opens the userform. I enter the details in the userform (2) and the data gets stored in sheet ("Customer") columns A to AJ rows 1 to 5000. What I'm trying to do is to be able search by name (column A) and call up individual customer details and have it displayed on the userform, if that is possible. -- AOP "Dave Peterson" wrote: I don't understand what you're doing. Either you'll have to provide more information or maybe someone else will chime in. AOP wrote: Sorry Dave, I cant get my head round this. could you walk me through this.......The sheet that has the data is sheet(6) "Customers" columns A to AJ. Sheet(2) is the menu sheet with one button, when pressed userform(2) is shown and the data entered in that gets stored in sheet 6. That works well. I'm assuming that I need to put an additional button on sheet (2) to run the code? or I'm well off the mark! -- AOP "Dave Peterson" wrote: If it's a followup to this question, just post in the same thread. If it's a different question, start a new thread. You'll find that there are lots of people who read the newsgroups who can help. AOP wrote: Thanks Dave, Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: How are you displaying those 32 columns? If it's part of a giant listbox or combobox, then if you were only using up to 10 columns, you could loop through the important column and add each item that matches your requirement -- and include the cells to the right. But since you're using columns A:AF (32 columns), you can filter your range to show what you want. Then copy the visible rows to a new location (on a new worksheet--maybe hidden???). Then use the range on that worksheet as the rowsource. If you're displaying each of those fields in a separate textbox (or label or ....), you could loop through the visible cells in column A and do something like: Dim HowManyVisRows as long dim VisRng as range dim myCell as range dim iCtr as long 'apply the filter someway With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1 if howmanyvisrows 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1, 1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else for each mycell in visrng.cells me.textbox1.value = mycell.value me.textbox2.value = mycell.offset(0,1).value 'etc 'or if the textboxes are named nicely. for ictr = 1 to 32 me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value next ictr end if Untested, uncompiled--watch for typos. AOP wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
Sorry Dave,
I was looking at a different code. The code you posted me remains the same ie Me.TextBox1.Value = FoundCell.Offset(0, 0).Value Me.TextBox2.Value = FoundCell.Offset(0, 1).Value and so on for all the textboxes. The following you posted me for the checkboxes ie: me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes") when I run it I get an error message with "lcase" high lighted. -- AOP "AOP" wrote: Hi Dave, Yes I changed the offset to 1, because there is a heading row and after several different ways that was the only code that would work. The triplestate was not changed, I checked and is at "False". And there is no link set up from the checkbox to a cell. -- AOP "Dave Peterson" wrote: Is there a reason you changed .offset(0,20) to .offset(1,20)? Did you really mean to go to the next row down? And did you change the .triplestate property from False to True? If you want checked and unchecked, that property should be False. And that code only looks at one checkbox and one cell. Did you assign a common linked cell to that checkbox? AOP wrote: Dave, I used the following code Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(1, 20).Value) = "yes") and nothing happens. All I get is the ticks on all the checkboxes in a light grey. Is there something I need to do in the properties maybe? -- AOP "Dave Peterson" wrote: The checkbox value will be true/false. So you could use: if lcase(foundcell.offset(0,20).value) = "yes" then me.checkbox1.value = true else me.checkbox1.value = false end if or me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes") AOP wrote: Thanks very much Dave, Its working fine now...geat stuff!. But, only one more thing. On my userform I have a checkbox, when selected it saves it on the data sheet as text "Yes" with the following code: LastRow.Offset(1, 20).Value = CheckBox1.Caption In your code how do I recall the checkbox as a tick? I have written it as: Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value -- AOP "Dave Peterson" wrote: Typo on my part: 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 AOP wrote: Dave, I followed you instructions, all fine until it runs the following happens: I get run 13 error. with the following lines highlighted. With Worksheets("Customers") Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) any ideas -- AOP "Dave Peterson" wrote: All the names are unique in column A? I'd put a combobox on the userform that displays those unique values from Column A. Then you could search for that username and when you find it, you could fill out the 35 textboxes. I'd put a button that retrieved the values after the user chose the customer name from the combobox. Option Explicit Private Sub CommandButton1_Click() Dim FoundCell as Range if me.combobox1.listindex = -1 then 'nothing filled in beep exit sub end if With worksheets("Customer") set Foundcell = .range("a:a").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,1).value 'and on and on for 35 textboxes 'or that looping code from before if your textboxes are nicely named end if End sub Uncompiled, untested. Watch for typos! AOP wrote: Ok, I'll try and explain. The workbook has been altered and now has two sheets ("Customer" and "Menu"). I also have a userform (2) with 35 textboxes. The Menu sheet has one button that opens the userform. I enter the details in the userform (2) and the data gets stored in sheet ("Customer") columns A to AJ rows 1 to 5000. What I'm trying to do is to be able search by name (column A) and call up individual customer details and have it displayed on the userform, if that is possible. -- AOP "Dave Peterson" wrote: I don't understand what you're doing. Either you'll have to provide more information or maybe someone else will chime in. AOP wrote: Sorry Dave, I cant get my head round this. could you walk me through this.......The sheet that has the data is sheet(6) "Customers" columns A to AJ. Sheet(2) is the menu sheet with one button, when pressed userform(2) is shown and the data entered in that gets stored in sheet 6. That works well. I'm assuming that I need to put an additional button on sheet (2) to run the code? or I'm well off the mark! -- AOP "Dave Peterson" wrote: If it's a followup to this question, just post in the same thread. If it's a different question, start a new thread. You'll find that there are lots of people who read the newsgroups who can help. AOP wrote: Thanks Dave, Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: How are you displaying those 32 columns? If it's part of a giant listbox or combobox, then if you were only using up to 10 columns, you could loop through the important column and add each item that matches your requirement -- and include the cells to the right. But since you're using columns A:AF (32 columns), you can filter your range to show what you want. Then copy the visible rows to a new location (on a new worksheet--maybe hidden???). Then use the range on that worksheet as the rowsource. If you're displaying each of those fields in a separate textbox (or label or ....), you could loop through the visible cells in column A and do something like: Dim HowManyVisRows as long dim VisRng as range dim myCell as range dim iCtr as long 'apply the filter someway With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1 if howmanyvisrows 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1, 1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else for each mycell in visrng.cells me.textbox1.value = mycell.value me.textbox2.value = mycell.offset(0,1).value 'etc 'or if the textboxes are named nicely. for ictr = 1 to 32 me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value next ictr end if Untested, uncompiled--watch for typos. AOP wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
This is the code as it is:
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, 15).Value Me.TextBox17.Value = FoundCell.Offset(0, 16).Value Me.TextBox18.Value = FoundCell.Offset(0, 17).Value Me.TextBox19.Value = FoundCell.Offset(0, 18).Value Me.TextBox20.Value = FoundCell.Offset(0, 19).Value Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value Me.CheckBox2.Value = FoundCell.Offset(0, 21).Value Me.CheckBox3.Value = FoundCell.Offset(0, 22).Value Me.CheckBox4.Value = FoundCell.Offset(0, 23).Value Me.CheckBox5.Value = FoundCell.Offset(0, 24).Value Me.CheckBox6.Value = FoundCell.Offset(0, 25).Value Me.CheckBox7.Value = FoundCell.Offset(0, 26).Value Me.CheckBox8.Value = FoundCell.Offset(0, 27).Value Me.CheckBox9.Value = FoundCell.Offset(0, 28).Value Me.CheckBox10.Value = FoundCell.Offset(0, 29).Value Me.CheckBox11.Value = FoundCell.Offset(0, 30).Value Me.CheckBox12.Value = FoundCell.Offset(0, 31).Value Me.TextBox21.Value = FoundCell.Offset(0, 32).Value Me.CheckBox13.Value = FoundCell.Offset(0, 33).Value Me.CheckBox14.Value = FoundCell.Offset(0, 34).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 -- AOP "AOP" wrote: Sorry Dave, I was looking at a different code. The code you posted me remains the same ie Me.TextBox1.Value = FoundCell.Offset(0, 0).Value Me.TextBox2.Value = FoundCell.Offset(0, 1).Value and so on for all the textboxes. The following you posted me for the checkboxes ie: me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes") when I run it I get an error message with "lcase" high lighted. -- AOP "AOP" wrote: Hi Dave, Yes I changed the offset to 1, because there is a heading row and after several different ways that was the only code that would work. The triplestate was not changed, I checked and is at "False". And there is no link set up from the checkbox to a cell. -- AOP "Dave Peterson" wrote: Is there a reason you changed .offset(0,20) to .offset(1,20)? Did you really mean to go to the next row down? And did you change the .triplestate property from False to True? If you want checked and unchecked, that property should be False. And that code only looks at one checkbox and one cell. Did you assign a common linked cell to that checkbox? AOP wrote: Dave, I used the following code Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(1, 20).Value) = "yes") and nothing happens. All I get is the ticks on all the checkboxes in a light grey. Is there something I need to do in the properties maybe? -- AOP "Dave Peterson" wrote: The checkbox value will be true/false. So you could use: if lcase(foundcell.offset(0,20).value) = "yes" then me.checkbox1.value = true else me.checkbox1.value = false end if or me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes") AOP wrote: Thanks very much Dave, Its working fine now...geat stuff!. But, only one more thing. On my userform I have a checkbox, when selected it saves it on the data sheet as text "Yes" with the following code: LastRow.Offset(1, 20).Value = CheckBox1.Caption In your code how do I recall the checkbox as a tick? I have written it as: Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value -- AOP "Dave Peterson" wrote: Typo on my part: 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 AOP wrote: Dave, I followed you instructions, all fine until it runs the following happens: I get run 13 error. with the following lines highlighted. With Worksheets("Customers") Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) any ideas -- AOP "Dave Peterson" wrote: All the names are unique in column A? I'd put a combobox on the userform that displays those unique values from Column A. Then you could search for that username and when you find it, you could fill out the 35 textboxes. I'd put a button that retrieved the values after the user chose the customer name from the combobox. Option Explicit Private Sub CommandButton1_Click() Dim FoundCell as Range if me.combobox1.listindex = -1 then 'nothing filled in beep exit sub end if With worksheets("Customer") set Foundcell = .range("a:a").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,1).value 'and on and on for 35 textboxes 'or that looping code from before if your textboxes are nicely named end if End sub Uncompiled, untested. Watch for typos! AOP wrote: Ok, I'll try and explain. The workbook has been altered and now has two sheets ("Customer" and "Menu"). I also have a userform (2) with 35 textboxes. The Menu sheet has one button that opens the userform. I enter the details in the userform (2) and the data gets stored in sheet ("Customer") columns A to AJ rows 1 to 5000. What I'm trying to do is to be able search by name (column A) and call up individual customer details and have it displayed on the userform, if that is possible. -- AOP "Dave Peterson" wrote: I don't understand what you're doing. Either you'll have to provide more information or maybe someone else will chime in. AOP wrote: Sorry Dave, I cant get my head round this. could you walk me through this.......The sheet that has the data is sheet(6) "Customers" columns A to AJ. Sheet(2) is the menu sheet with one button, when pressed userform(2) is shown and the data entered in that gets stored in sheet 6. That works well. I'm assuming that I need to put an additional button on sheet (2) to run the code? or I'm well off the mark! -- AOP "Dave Peterson" wrote: If it's a followup to this question, just post in the same thread. If it's a different question, start a new thread. You'll find that there are lots of people who read the newsgroups who can help. AOP wrote: Thanks Dave, Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: How are you displaying those 32 columns? If it's part of a giant listbox or combobox, then if you were only using up to 10 columns, you could loop through the important column and add each item that matches your requirement -- and include the cells to the right. But since you're using columns A:AF (32 columns), you can filter your range to show what you want. Then copy the visible rows to a new location (on a new worksheet--maybe hidden???). Then use the range on that worksheet as the rowsource. If you're displaying each of those fields in a separate textbox (or label or ....), you could loop through the visible cells in column A and do something like: Dim HowManyVisRows as long dim VisRng as range dim myCell as range dim iCtr as long 'apply the filter someway With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1 if howmanyvisrows 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1, 1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else for each mycell in visrng.cells me.textbox1.value = mycell.value me.textbox2.value = mycell.offset(0,1).value 'etc 'or if the textboxes are named nicely. for ictr = 1 to 32 me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value next ictr end if Untested, uncompiled--watch for typos. AOP wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
Maybe these kinds of lines:
Me.TextBox13.Value = FoundCell.Offset(0, 12).Value have to be Me.TextBox13.Value = cbool(lcase(FoundCell.Offset(0, 12).Value) = "yes") If you're still getting an error with lCase, then look at: Tools|References Look for MISSING And uncheck it. A missing reference can cause errors like this. AOP wrote: This is the code as it is: 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, 15).Value Me.TextBox17.Value = FoundCell.Offset(0, 16).Value Me.TextBox18.Value = FoundCell.Offset(0, 17).Value Me.TextBox19.Value = FoundCell.Offset(0, 18).Value Me.TextBox20.Value = FoundCell.Offset(0, 19).Value Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value Me.CheckBox2.Value = FoundCell.Offset(0, 21).Value Me.CheckBox3.Value = FoundCell.Offset(0, 22).Value Me.CheckBox4.Value = FoundCell.Offset(0, 23).Value Me.CheckBox5.Value = FoundCell.Offset(0, 24).Value Me.CheckBox6.Value = FoundCell.Offset(0, 25).Value Me.CheckBox7.Value = FoundCell.Offset(0, 26).Value Me.CheckBox8.Value = FoundCell.Offset(0, 27).Value Me.CheckBox9.Value = FoundCell.Offset(0, 28).Value Me.CheckBox10.Value = FoundCell.Offset(0, 29).Value Me.CheckBox11.Value = FoundCell.Offset(0, 30).Value Me.CheckBox12.Value = FoundCell.Offset(0, 31).Value Me.TextBox21.Value = FoundCell.Offset(0, 32).Value Me.CheckBox13.Value = FoundCell.Offset(0, 33).Value Me.CheckBox14.Value = FoundCell.Offset(0, 34).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 -- AOP "AOP" wrote: Sorry Dave, I was looking at a different code. The code you posted me remains the same ie Me.TextBox1.Value = FoundCell.Offset(0, 0).Value Me.TextBox2.Value = FoundCell.Offset(0, 1).Value and so on for all the textboxes. The following you posted me for the checkboxes ie: me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes") when I run it I get an error message with "lcase" high lighted. -- AOP "AOP" wrote: Hi Dave, Yes I changed the offset to 1, because there is a heading row and after several different ways that was the only code that would work. The triplestate was not changed, I checked and is at "False". And there is no link set up from the checkbox to a cell. -- AOP "Dave Peterson" wrote: Is there a reason you changed .offset(0,20) to .offset(1,20)? Did you really mean to go to the next row down? And did you change the .triplestate property from False to True? If you want checked and unchecked, that property should be False. And that code only looks at one checkbox and one cell. Did you assign a common linked cell to that checkbox? AOP wrote: Dave, I used the following code Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(1, 20).Value) = "yes") and nothing happens. All I get is the ticks on all the checkboxes in a light grey. Is there something I need to do in the properties maybe? -- AOP "Dave Peterson" wrote: The checkbox value will be true/false. So you could use: if lcase(foundcell.offset(0,20).value) = "yes" then me.checkbox1.value = true else me.checkbox1.value = false end if or me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes") AOP wrote: Thanks very much Dave, Its working fine now...geat stuff!. But, only one more thing. On my userform I have a checkbox, when selected it saves it on the data sheet as text "Yes" with the following code: LastRow.Offset(1, 20).Value = CheckBox1.Caption In your code how do I recall the checkbox as a tick? I have written it as: Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value -- AOP "Dave Peterson" wrote: Typo on my part: 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 AOP wrote: Dave, I followed you instructions, all fine until it runs the following happens: I get run 13 error. with the following lines highlighted. With Worksheets("Customers") Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) any ideas -- AOP "Dave Peterson" wrote: All the names are unique in column A? I'd put a combobox on the userform that displays those unique values from Column A. Then you could search for that username and when you find it, you could fill out the 35 textboxes. I'd put a button that retrieved the values after the user chose the customer name from the combobox. Option Explicit Private Sub CommandButton1_Click() Dim FoundCell as Range if me.combobox1.listindex = -1 then 'nothing filled in beep exit sub end if With worksheets("Customer") set Foundcell = .range("a:a").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,1).value 'and on and on for 35 textboxes 'or that looping code from before if your textboxes are nicely named end if End sub Uncompiled, untested. Watch for typos! AOP wrote: Ok, I'll try and explain. The workbook has been altered and now has two sheets ("Customer" and "Menu"). I also have a userform (2) with 35 textboxes. The Menu sheet has one button that opens the userform. I enter the details in the userform (2) and the data gets stored in sheet ("Customer") columns A to AJ rows 1 to 5000. What I'm trying to do is to be able search by name (column A) and call up individual customer details and have it displayed on the userform, if that is possible. -- AOP "Dave Peterson" wrote: I don't understand what you're doing. Either you'll have to provide more information or maybe someone else will chime in. AOP wrote: Sorry Dave, I cant get my head round this. could you walk me through this.......The sheet that has the data is sheet(6) "Customers" columns A to AJ. Sheet(2) is the menu sheet with one button, when pressed userform(2) is shown and the data entered in that gets stored in sheet 6. That works well. I'm assuming that I need to put an additional button on sheet (2) to run the code? or I'm well off the mark! -- AOP "Dave Peterson" wrote: If it's a followup to this question, just post in the same thread. If it's a different question, start a new thread. You'll find that there are lots of people who read the newsgroups who can help. AOP wrote: Thanks Dave, Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: How are you displaying those 32 columns? If it's part of a giant listbox or combobox, then if you were only using up to 10 columns, you could loop through the important column and add each item that matches your requirement -- and include the cells to the right. But since you're using columns A:AF (32 columns), you can filter your range to show what you want. Then copy the visible rows to a new location (on a new worksheet--maybe hidden???). Then use the range on that worksheet as the rowsource. If you're displaying each of those fields in a separate textbox (or label or ....), you could loop through the visible cells in column A and do something like: Dim HowManyVisRows as long dim VisRng as range dim myCell as range dim iCtr as long 'apply the filter someway With worksheets("somesheetname").autofilter.range 'subtract one for the header. howmanyvisrows _ = .columns(1).cells.specialcells(xlcelltypevisible). cells.count - 1 if howmanyvisrows 0 then 'avoid the header and come down one row set visrng = .resize(.rows.count-1, 1).offset(1,0) _ .cells.specialcells(xlcelltypevisible) else set visrng = nothing end if end with if visrng is nothing then 'warning message??? else for each mycell in visrng.cells me.textbox1.value = mycell.value me.textbox2.value = mycell.offset(0,1).value 'etc 'or if the textboxes are named nicely. for ictr = 1 to 32 me.controls("Textbox" & ictr).value = mycell.offset(0,ictr-1).value next ictr end if Untested, uncompiled--watch for typos. AOP wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
Dave,
Thank you for your patience. It all works great. We finally got there thank you -- AOP "Dave Peterson" wrote: Maybe these kinds of lines: Me.TextBox13.Value = FoundCell.Offset(0, 12).Value have to be Me.TextBox13.Value = cbool(lcase(FoundCell.Offset(0, 12).Value) = "yes") If you're still getting an error with lCase, then look at: Tools|References Look for MISSING And uncheck it. A missing reference can cause errors like this. AOP wrote: This is the code as it is: 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, 15).Value Me.TextBox17.Value = FoundCell.Offset(0, 16).Value Me.TextBox18.Value = FoundCell.Offset(0, 17).Value Me.TextBox19.Value = FoundCell.Offset(0, 18).Value Me.TextBox20.Value = FoundCell.Offset(0, 19).Value Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value Me.CheckBox2.Value = FoundCell.Offset(0, 21).Value Me.CheckBox3.Value = FoundCell.Offset(0, 22).Value Me.CheckBox4.Value = FoundCell.Offset(0, 23).Value Me.CheckBox5.Value = FoundCell.Offset(0, 24).Value Me.CheckBox6.Value = FoundCell.Offset(0, 25).Value Me.CheckBox7.Value = FoundCell.Offset(0, 26).Value Me.CheckBox8.Value = FoundCell.Offset(0, 27).Value Me.CheckBox9.Value = FoundCell.Offset(0, 28).Value Me.CheckBox10.Value = FoundCell.Offset(0, 29).Value Me.CheckBox11.Value = FoundCell.Offset(0, 30).Value Me.CheckBox12.Value = FoundCell.Offset(0, 31).Value Me.TextBox21.Value = FoundCell.Offset(0, 32).Value Me.CheckBox13.Value = FoundCell.Offset(0, 33).Value Me.CheckBox14.Value = FoundCell.Offset(0, 34).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 -- AOP "AOP" wrote: Sorry Dave, I was looking at a different code. The code you posted me remains the same ie Me.TextBox1.Value = FoundCell.Offset(0, 0).Value Me.TextBox2.Value = FoundCell.Offset(0, 1).Value and so on for all the textboxes. The following you posted me for the checkboxes ie: me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes") when I run it I get an error message with "lcase" high lighted. -- AOP "AOP" wrote: Hi Dave, Yes I changed the offset to 1, because there is a heading row and after several different ways that was the only code that would work. The triplestate was not changed, I checked and is at "False". And there is no link set up from the checkbox to a cell. -- AOP "Dave Peterson" wrote: Is there a reason you changed .offset(0,20) to .offset(1,20)? Did you really mean to go to the next row down? And did you change the .triplestate property from False to True? If you want checked and unchecked, that property should be False. And that code only looks at one checkbox and one cell. Did you assign a common linked cell to that checkbox? AOP wrote: Dave, I used the following code Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(1, 20).Value) = "yes") and nothing happens. All I get is the ticks on all the checkboxes in a light grey. Is there something I need to do in the properties maybe? -- AOP "Dave Peterson" wrote: The checkbox value will be true/false. So you could use: if lcase(foundcell.offset(0,20).value) = "yes" then me.checkbox1.value = true else me.checkbox1.value = false end if or me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes") AOP wrote: Thanks very much Dave, Its working fine now...geat stuff!. But, only one more thing. On my userform I have a checkbox, when selected it saves it on the data sheet as text "Yes" with the following code: LastRow.Offset(1, 20).Value = CheckBox1.Caption In your code how do I recall the checkbox as a tick? I have written it as: Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value -- AOP "Dave Peterson" wrote: Typo on my part: 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 AOP wrote: Dave, I followed you instructions, all fine until it runs the following happens: I get run 13 error. with the following lines highlighted. With Worksheets("Customers") Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) any ideas -- AOP "Dave Peterson" wrote: All the names are unique in column A? I'd put a combobox on the userform that displays those unique values from Column A. Then you could search for that username and when you find it, you could fill out the 35 textboxes. I'd put a button that retrieved the values after the user chose the customer name from the combobox. Option Explicit Private Sub CommandButton1_Click() Dim FoundCell as Range if me.combobox1.listindex = -1 then 'nothing filled in beep exit sub end if With worksheets("Customer") set Foundcell = .range("a:a").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,1).value 'and on and on for 35 textboxes 'or that looping code from before if your textboxes are nicely named end if End sub Uncompiled, untested. Watch for typos! AOP wrote: Ok, I'll try and explain. The workbook has been altered and now has two sheets ("Customer" and "Menu"). I also have a userform (2) with 35 textboxes. The Menu sheet has one button that opens the userform. I enter the details in the userform (2) and the data gets stored in sheet ("Customer") columns A to AJ rows 1 to 5000. What I'm trying to do is to be able search by name (column A) and call up individual customer details and have it displayed on the userform, if that is possible. -- AOP "Dave Peterson" wrote: I don't understand what you're doing. Either you'll have to provide more information or maybe someone else will chime in. AOP wrote: Sorry Dave, I cant get my head round this. could you walk me through this.......The sheet that has the data is sheet(6) "Customers" columns A to AJ. Sheet(2) is the menu sheet with one button, when pressed userform(2) is shown and the data entered in that gets stored in sheet 6. That works well. I'm assuming that I need to put an additional button on sheet (2) to run the code? or I'm well off the mark! -- AOP "Dave Peterson" wrote: If it's a followup to this question, just post in the same thread. If it's a different question, start a new thread. You'll find that there are lots of people who read the newsgroups who can help. AOP wrote: Thanks Dave, Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: |
|
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?
That's good.
I was getting more and more confused <vbg. AOP wrote: Dave, Thank you for your patience. It all works great. We finally got there thank you -- AOP "Dave Peterson" wrote: Maybe these kinds of lines: Me.TextBox13.Value = FoundCell.Offset(0, 12).Value have to be Me.TextBox13.Value = cbool(lcase(FoundCell.Offset(0, 12).Value) = "yes") If you're still getting an error with lCase, then look at: Tools|References Look for MISSING And uncheck it. A missing reference can cause errors like this. AOP wrote: This is the code as it is: 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, 15).Value Me.TextBox17.Value = FoundCell.Offset(0, 16).Value Me.TextBox18.Value = FoundCell.Offset(0, 17).Value Me.TextBox19.Value = FoundCell.Offset(0, 18).Value Me.TextBox20.Value = FoundCell.Offset(0, 19).Value Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value Me.CheckBox2.Value = FoundCell.Offset(0, 21).Value Me.CheckBox3.Value = FoundCell.Offset(0, 22).Value Me.CheckBox4.Value = FoundCell.Offset(0, 23).Value Me.CheckBox5.Value = FoundCell.Offset(0, 24).Value Me.CheckBox6.Value = FoundCell.Offset(0, 25).Value Me.CheckBox7.Value = FoundCell.Offset(0, 26).Value Me.CheckBox8.Value = FoundCell.Offset(0, 27).Value Me.CheckBox9.Value = FoundCell.Offset(0, 28).Value Me.CheckBox10.Value = FoundCell.Offset(0, 29).Value Me.CheckBox11.Value = FoundCell.Offset(0, 30).Value Me.CheckBox12.Value = FoundCell.Offset(0, 31).Value Me.TextBox21.Value = FoundCell.Offset(0, 32).Value Me.CheckBox13.Value = FoundCell.Offset(0, 33).Value Me.CheckBox14.Value = FoundCell.Offset(0, 34).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 -- AOP "AOP" wrote: Sorry Dave, I was looking at a different code. The code you posted me remains the same ie Me.TextBox1.Value = FoundCell.Offset(0, 0).Value Me.TextBox2.Value = FoundCell.Offset(0, 1).Value and so on for all the textboxes. The following you posted me for the checkboxes ie: me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes") when I run it I get an error message with "lcase" high lighted. -- AOP "AOP" wrote: Hi Dave, Yes I changed the offset to 1, because there is a heading row and after several different ways that was the only code that would work. The triplestate was not changed, I checked and is at "False". And there is no link set up from the checkbox to a cell. -- AOP "Dave Peterson" wrote: Is there a reason you changed .offset(0,20) to .offset(1,20)? Did you really mean to go to the next row down? And did you change the .triplestate property from False to True? If you want checked and unchecked, that property should be False. And that code only looks at one checkbox and one cell. Did you assign a common linked cell to that checkbox? AOP wrote: Dave, I used the following code Me.CheckBox1.Value = CBool(LCase(FoundCell.Offset(1, 20).Value) = "yes") and nothing happens. All I get is the ticks on all the checkboxes in a light grey. Is there something I need to do in the properties maybe? -- AOP "Dave Peterson" wrote: The checkbox value will be true/false. So you could use: if lcase(foundcell.offset(0,20).value) = "yes" then me.checkbox1.value = true else me.checkbox1.value = false end if or me.checkbox1.value = cbool(lcase(foundcell.offset(0,2).value) = "yes") AOP wrote: Thanks very much Dave, Its working fine now...geat stuff!. But, only one more thing. On my userform I have a checkbox, when selected it saves it on the data sheet as text "Yes" with the following code: LastRow.Offset(1, 20).Value = CheckBox1.Caption In your code how do I recall the checkbox as a tick? I have written it as: Me.CheckBox1.Value = FoundCell.Offset(0, 20).Value -- AOP "Dave Peterson" wrote: Typo on my part: 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 AOP wrote: Dave, I followed you instructions, all fine until it runs the following happens: I get run 13 error. with the following lines highlighted. With Worksheets("Customers") Set FoundCell = .Range("a:a").Cells.Find(what:=Me.ComboBox1.Value, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) any ideas -- AOP "Dave Peterson" wrote: All the names are unique in column A? I'd put a combobox on the userform that displays those unique values from Column A. Then you could search for that username and when you find it, you could fill out the 35 textboxes. I'd put a button that retrieved the values after the user chose the customer name from the combobox. Option Explicit Private Sub CommandButton1_Click() Dim FoundCell as Range if me.combobox1.listindex = -1 then 'nothing filled in beep exit sub end if With worksheets("Customer") set Foundcell = .range("a:a").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,1).value 'and on and on for 35 textboxes 'or that looping code from before if your textboxes are nicely named end if End sub Uncompiled, untested. Watch for typos! AOP wrote: Ok, I'll try and explain. The workbook has been altered and now has two sheets ("Customer" and "Menu"). I also have a userform (2) with 35 textboxes. The Menu sheet has one button that opens the userform. I enter the details in the userform (2) and the data gets stored in sheet ("Customer") columns A to AJ rows 1 to 5000. What I'm trying to do is to be able search by name (column A) and call up individual customer details and have it displayed on the userform, if that is possible. -- AOP "Dave Peterson" wrote: I don't understand what you're doing. Either you'll have to provide more information or maybe someone else will chime in. AOP wrote: Sorry Dave, I cant get my head round this. could you walk me through this.......The sheet that has the data is sheet(6) "Customers" columns A to AJ. Sheet(2) is the menu sheet with one button, when pressed userform(2) is shown and the data entered in that gets stored in sheet 6. That works well. I'm assuming that I need to put an additional button on sheet (2) to run the code? or I'm well off the mark! -- AOP "Dave Peterson" wrote: If it's a followup to this question, just post in the same thread. If it's a different question, start a new thread. You'll find that there are lots of people who read the newsgroups who can help. AOP wrote: Thanks Dave, Yes I'm displaying each of the fields in a separate textbox and checkbox too. I'm trying to use the same form, which I use to enter data, to show the clients details. It is the mother of all forms but I need all the information. I shall try your code and see how I get on! If there is a problem can I get back to you? -- AOP "Dave Peterson" wrote: -- Dave Peterson |
|
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter question?is Cooper on players side in marine sharpshooters2
C .G. Messinger
"AOP" wrote: At the moment I have a data sheet that details are entered using a userform. It finds the last empty row and enters the data from columns "A" to "AF" Is there a way in which I can filter by name (column "A") and the result be shown on the same userform in which data was entered. Or do I have to use a different userform. -- AOP |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Filter Question | Excel Discussion (Misc queries) | |||
| Filter question | Excel Worksheet Functions | |||
| Filter Question | Excel Discussion (Misc queries) | |||
| a filter question | Excel Discussion (Misc queries) | |||
| filter question | Excel Discussion (Misc queries) | |||
Linear Mode
