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

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

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

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

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 -

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

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

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