Populating userform textbox with filtered results

[cardinalphin]

Good evening and Merry Christmas!

I've got a very simple spreadsheet that I'm trying to control via a userform. I have several comboboxes that apply filters to the information on a sheet that contains all of the data for the spreadsheet.

I've got a lot of it working, in large part thanks to help from this site. I'm having issues with getting the filtered data to populate the text boxes in my user form.

Here's how I want the form to work. I want to populate the top result in the text box (there are actually 2 text boxes - but they are to be populated from the same row of data). I'll eventually be tying in buttons to allow users to select the next & previous row as well.

I've tried a few different ways to get the basic implementation working, but I'm not really having any luck.

This is the most recent attempt that I've made to accomplish what I'm looking for... With this code I'm getting a run time 424 error.

faultBox = Data.Range.SpecialCells(xlCellTypeVisible).Cells(22, 1).Value

I've also tried it this way...

faultBox = Worksheets("Data").Range.SpecialCells(xlCellTypeVisible).Cells(1, 1).Text

Using that code I'm getting a run-time 450 error.

I appreciate any help that anyone can offer.

Thanks for looking and have a merry Christmas!!!

 

[CalcSux78]

When you're using a non-contiguous range for a Combo Box RowSource, you'll need to create a loop to add each value, item by item.

Dim rng As Range
Dim cel As Range


faultBox.Clear
Set rng = Intersect(Columns(1).SpecialCells(xlCellTypeVisible), ActiveSheet.UsedRange)
For Each cel In rng.Cells
    faultBox.AddItem cel
Next cel

For more info, read here: Globaliconnect.com

 

[cardinalphin]

I'm not quite sure if this is quite what I'm trying to do... Essentially, I want to fill the textbox with the first filtered result from column V. I want to put in a button so that I can grab the next line at a later time, but I only want to populate the information from one individual cell at a time. I'm not sure if I'm misinterpreting what your code is doing or if I might not have explained that well - so just trying to make sure we're on the same wavelength.

Having said that, I do appreciate your help so I did try your code. However, when I try to execute it now I get the following error:

Compile Error:
Method or data member not found.

It faults here:

faultBox.Clear

Since I don't generally have any data in this combobox at this point in time, I assumed that's why I was getting the fault at first, so I commented that out. At that point it just starts faulting out here:

faultBox.AddItem cel

I thought maybe the issue was that I didn't have the correct name for my textbox, but I double checked that and it's correct.

I've done a little preliminary searching; but I've got to get to bed so not a lot. It seems like maybe the textbox might not be an ActiveX textbox perhaps? If that's the case though, I seem to be missing something because I really only see the one option in regards to a textbox.

Thanks for your help!

 

[mikerickson]

You mention "from column V"

To get the first filtered result in that column, try

faultBox.Text = Worksheets("Data").Range("V:V").SpecialCells(xlCellTypeVisible).Cells(1, 1).Text

If you have a header, you'll need to use

With Worksheets("Data").Range("V:V")
    With Range(.Cells(1,1), .Cells(.Rows.Count,1).Offset(0,1).SpecialCells(xlCellTypeVisible)
        falutBox.Areas(1).Cells(1,1)
    End With
End With

To get the N'th result (assuming a header) you could use code like

Dim oneCell as Range
Dim i as long

i = N
With Worksheets("Data").Range("V:V")
    With Range(.Cells(1,1), .Cells(.Rows.Count,1).Offset(0,1)
        For each oneCell in .SpecialCells(xlCellTypeVisible)
            i = i - 1
            if i = 0 Then Exit For  
        Next oneCell
    End With
End With

If i = 0 then
    faultBox.Text = oneCell.Text
Else
    faultBox.Text = "less than N results"
End If

 

[cardinalphin]

Thanks for your help! I am using a header, and because I want the capability to grab the N'th result, I went with the 3rd block of code. I did have to do some minor modifications to get it to work, but I wouldn't have got there without everyone's help.

Here's my working code:

Sub populateTextBoxes(filterLevel)

    Dim oneCell As Range
    Dim i As Long
    
    
    i = 2
    Range("iValue").Value = i
    
    'Populate the fault description box
    With Worksheets("Data").Range("V:V")
        With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).Offset(filterLevel * -1, 0))
            For Each oneCell In .SpecialCells(xlCellTypeVisible)
                i = i - 1
                If i = 0 Then Exit For
            Next oneCell
        End With
    End With
    
    If i = 0 Then
        faultBox.Text = oneCell.Text
    Else
        faultBox.Text = "less than N results"
    End If
    
    'Populate the resolution box
    Dim oneCellSol As Range
    i = 2
    
    With Worksheets("Data").Range("W:W")
        With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).Offset(filterLevel * -1, 0))
            For Each oneCellSol In .SpecialCells(xlCellTypeVisible)
                i = i - 1
                If i = 0 Then Exit For
            Next oneCellSol
        End With
    End With
    
    If i = 0 Then
        resolutionBox.Text = oneCellSol.Text
    Else
        resolutionBox.Text = "less than N results"
    End If


End Sub

I forgot to mention before that I actually need to populate a couple of text boxes using two different cells from the same line. I suspect there is probably a cleaner way to code this than what I did, but regardless, this solution does seem to work perfectly as far as I can tell (with the minor quirk that in my previous/next buttons I don't currently have anything to prevent users from exceeding the upper and lower limits. I think I can clean that up though.

The one notable thing I had to change in the code was just the offset from 0,1 to -1, 0 - and as you'll see I just multiply it by my filter level. The multiplication by the filterLevel variable is only here because I used this same code for a previous button and a next button, which allows me to call out the N'th line.

Thanks again for everyone's help! I never could have gotten this far without it!