Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBoxes and Data Filtering
I have a large worksheet which I would like to filter from within a UserForm
(that I have working), my problem is that I want to populate some TextBoxes based on that filtered data. To populate TextBox1 is easy since it is the active row but how do I populate TextBox2 in this example? The required data could be in the next row down or it might be 97 rows down. Below is just an example, the final product will have a number of other TextBoxes and OptionButtons based on the values contained in each row. Private Sub UserForm_Initialize() Dim rng Set rng = Cells(ActiveCell.Row, 1) TextBox1.Value = rng(1, 3).Value TextBox2.Value = rng(2, 3).Value End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBoxes and Data Filtering
It is so rare to get no response, I have to ask. Is what I am asking not
possible? "Patrick C. Simonds" wrote in message ... I have a large worksheet which I would like to filter from within a UserForm (that I have working), my problem is that I want to populate some TextBoxes based on that filtered data. To populate TextBox1 is easy since it is the active row but how do I populate TextBox2 in this example? The required data could be in the next row down or it might be 97 rows down. Below is just an example, the final product will have a number of other TextBoxes and OptionButtons based on the values contained in each row. Private Sub UserForm_Initialize() Dim rng Set rng = Cells(ActiveCell.Row, 1) TextBox1.Value = rng(1, 3).Value TextBox2.Value = rng(2, 3).Value End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBoxes and Data Filtering
Would this work for you ?
Dim rng Set rng = Cells(ActiveCell.Row, 1) TextBox1.Value = rng.offset(0,3).Value TextBox2.Value = NextVisbleCellDown(rng).offset(0,3).Value 'Get the next visible cell down Function NextVisbleCellDown(rng As Range) As Range Dim rv As Range Set rv = rng.Offset(1, 0) Do While rv.Height = 0 Set rv = rv.Offset(1, 0) Loop Set NextVisbleCellDown = rv End Function ....or you could maybe check out using SpecialCells(xlCellTypeVisible) but there's no direct way of indexing that range since it can be multi-area. Tim "Patrick C. Simonds" wrote in message ... It is so rare to get no response, I have to ask. Is what I am asking not possible? "Patrick C. Simonds" wrote in message ... I have a large worksheet which I would like to filter from within a UserForm (that I have working), my problem is that I want to populate some TextBoxes based on that filtered data. To populate TextBox1 is easy since it is the active row but how do I populate TextBox2 in this example? The required data could be in the next row down or it might be 97 rows down. Below is just an example, the final product will have a number of other TextBoxes and OptionButtons based on the values contained in each row. Private Sub UserForm_Initialize() Dim rng Set rng = Cells(ActiveCell.Row, 1) TextBox1.Value = rng(1, 3).Value TextBox2.Value = rng(2, 3).Value End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBoxes and Data Filtering
I get a "ByRef argument type mismatch error"
Was I to use the code as written or was I suppose to do something else with the function code below? 'Get the next visible cell down Function NextVisbleCellDown(rng As Range) As Range Dim rv As Range Set rv = rng.Offset(1, 0) Do While rv.Height = 0 Set rv = rv.Offset(1, 0) Loop Set NextVisbleCellDown = rv End Function "Tim Williams" wrote in message ... Would this work for you ? Dim rng Set rng = Cells(ActiveCell.Row, 1) TextBox1.Value = rng.offset(0,3).Value TextBox2.Value = NextVisbleCellDown(rng).offset(0,3).Value 'Get the next visible cell down Function NextVisbleCellDown(rng As Range) As Range Dim rv As Range Set rv = rng.Offset(1, 0) Do While rv.Height = 0 Set rv = rv.Offset(1, 0) Loop Set NextVisbleCellDown = rv End Function ...or you could maybe check out using SpecialCells(xlCellTypeVisible) but there's no direct way of indexing that range since it can be multi-area. Tim "Patrick C. Simonds" wrote in message ... It is so rare to get no response, I have to ask. Is what I am asking not possible? "Patrick C. Simonds" wrote in message ... I have a large worksheet which I would like to filter from within a UserForm (that I have working), my problem is that I want to populate some TextBoxes based on that filtered data. To populate TextBox1 is easy since it is the active row but how do I populate TextBox2 in this example? The required data could be in the next row down or it might be 97 rows down. Below is just an example, the final product will have a number of other TextBoxes and OptionButtons based on the values contained in each row. Private Sub UserForm_Initialize() Dim rng Set rng = Cells(ActiveCell.Row, 1) TextBox1.Value = rng(1, 3).Value TextBox2.Value = rng(2, 3).Value End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBoxes and Data Filtering
Dim rng
should have been Dim rng as Range Tim On Jul 15, 9:32*am, "Patrick C. Simonds" wrote: I get a "ByRef argument type mismatch error" Was I to use the code as written or was I suppose to do something else with the function code below? 'Get the next visible cell down Function NextVisbleCellDown(rng As Range) As Range Dim rv As Range * * Set rv = rng.Offset(1, 0) * * Do While rv.Height = 0 * * * * Set rv = rv.Offset(1, 0) * * Loop * * Set NextVisbleCellDown = rv End Function "Tim Williams" wrote in message ... Would this work for you ? Dim rng Set rng = Cells(ActiveCell.Row, 1) TextBox1.Value = rng.offset(0,3).Value TextBox2.Value = NextVisbleCellDown(rng).offset(0,3).Value 'Get the next visible cell down Function NextVisbleCellDown(rng As Range) As Range Dim rv As Range * *Set rv = rng.Offset(1, 0) * *Do While rv.Height = 0 * * * *Set rv = rv.Offset(1, 0) * *Loop * *Set NextVisbleCellDown = rv End Function ...or you could maybe check out using *SpecialCells(xlCellTypeVisible) but there's no direct way of indexing that range since it can be multi-area. Tim "Patrick C. Simonds" wrote in message ... It is so rare to get no response, I have to ask. Is what I am asking not possible? "Patrick C. Simonds" wrote in message ... I have a large worksheet which I would like to filter from within a UserForm (that I have working), my problem is that I want to populate some TextBoxes based on that filtered data. To populate TextBox1 is easy since it is the active row but how do I populate TextBox2 in this example? *The required data could be in the next row down or it might be 97 rows down. Below is just an example, the final product will have a number of other TextBoxes and OptionButtons based on the values contained in each row.. Private Sub UserForm_Initialize() Dim rng Set rng = Cells(ActiveCell.Row, 1) TextBox1.Value = rng(1, 3).Value TextBox2.Value = rng(2, 3).Value End Sub- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBoxes and Data Filtering
Here is the code as it appears on my UserForm. The code stops at the
comment line "get the next visible cell down" and I get an Expect End Sub error. I suspect that I should be doing something else with the code below your comment line but I do not know what. Private Sub UserForm_Initialize() Dim rng As Range Set rng = Cells(ActiveCell.Row, 1) TextBox1.Value = rng.Offset(0, 3).Value TextBox2.Value = NextVisbleCellDown(rng).Offset(0, 3).Value 'Get the next visible cell down Function NextVisbleCellDown(rng As Range) As Range Dim rv As Range Set rv = rng.Offset(1, 0) Do While rv.Height = 0 Set rv = rv.Offset(1, 0) Loop Set NextVisbleCellDown = rv End Function End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBoxes and Data Filtering
Upon further research I discovered that I needed to put the code below into
it's own module. Thanks for your help. 'Get the next visible cell down Function NextVisbleCellDown(rng As Range) As Range Dim rv As Range Set rv = rng.Offset(1, 0) Do While rv.Height = 0 Set rv = rv.Offset(1, 0) Loop Set NextVisbleCellDown = rv End Function |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBoxes and Data Filtering
Yes: It doesn't need to be in its own module, but it does need to be outside
of the other Sub. Tim "Patrick C. Simonds" wrote in message ... Upon further research I discovered that I needed to put the code below into it's own module. Thanks for your help. 'Get the next visible cell down Function NextVisbleCellDown(rng As Range) As Range Dim rv As Range Set rv = rng.Offset(1, 0) Do While rv.Height = 0 Set rv = rv.Offset(1, 0) Loop Set NextVisbleCellDown = rv End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation for TextBoxes | Excel Programming | |||
Delete Data from textboxes | Excel Programming | |||
Show Results in TextBoxes, ONLY if All TextBoxes have Data in them | Excel Programming | |||
Need help w/ userform with 12 textboxes with data going into colum | Excel Discussion (Misc queries) | |||
Combining data in multiple textboxes | Excel Programming |