TextBoxes and Data Filtering

P

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
 
P

Patrick C. Simonds

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

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
 
P

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
 
P

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
 
P

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
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top