TextBoxes and Data Filtering

[Patrick C. Simonds]

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

 

[Patrick C. Simonds]

It is so rare to get no response, I have to ask. Is what I am asking not
possible?

 

[Tim Williams]

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]

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]

Dim rng

should have been

Dim rng as Range


Tim

 

[Patrick C. Simonds]

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

 

[Patrick C. Simonds]

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

 

[Tim Williams]

Yes: It doesn't need to be in its own module, but it does need to be outside
of the other Sub.

Tim