Posts by Daph1990

Hi,

I'll start by thanking everyone in advance. So I have a matrix in Excel which essentially shows some category and sub-category areas. When the user clicks on the buttons/dropdowns I want the userform to show the text associated with their selection. i.e. if 'Strategy' is selected then 'Strategy Consultancy' then 'JLP Content' I then want all the different suppliers that fall under those categories to be listed - I've got that bit all working very well (thanks to some help I received on here last year - so thanks!).

Where I am struggling now is that behind every supplier name there would be a number of other bits of information that I would like to be displayed i.e. company address, telephone number etc so I created a separate sheet ('Supplier Data') with all the other columns I'd be interested in capturing and on the original sheet ("JLP Content") I have data validated dropdowns to select the supplier name from the 'Supplier Data' sheet.

I want my results userform to show all the columns info from the 'Supplier Data' sheet based on what supplier name has been selected from the data validation dropdown on sheet 'JLP Content'. I've attached the Excel file to make it all make sense. But here is the formula currently being used (which only returns the supplier name from sheet 'JLP Content'):

Private Sub cbResult_Click()Dim r As Long, Col As Long, i As LongDim Rng As Range, cel As RangeDim wArea4 As RangeDim WS As WorksheetDim iRes As IntegerDim strPrompt As StringDim strTitle As String ResultsScreen.rowCount = CalculateRowCount '<-------------------------    On Error Resume Next        Col = Cells(1, Columns.Count).End(xlToLeft).Column - 4        r = ResultsScreen.rowCount.Value        Set Rng = Cells(r, 4).Resize(, Col).SpecialCells(xlCellTypeConstants)        If Err Then        Me.Show        ResultsScreen.Hide        'User Prompt        strPrompt = "No supplier found."        'Messagebox Title        strTitle = "Error!"        'Messagebox Display        iRes = MsgBox(strPrompt, vbExclamation + vbOKOnly, strTitle)            Else                Me.Hide                ResultsScreen.Show                ResultsScreen.lbResults.Clear    End If         For Each cel In Rng        With lbResults        .AddItem Cells(1, cel.Column)        .List(.ListCount - 1, 1) = cel        End With    NextEnd SubPrivate Function CalculateRowCount() As LongDim Adjuster As LongWith Me  If cboArea.ListIndex = 0 Then    CalculateRowCount = 2 + cboSubCat.ListIndex    Exit Function  End If    If .cboArea.ListIndex = 1 Then    If .cboCategory.ListIndex = 0 Then      CalculateRowCount = 11      Exit Function    ElseIf .cboCategory.ListIndex = 1 Then      Adjuster = 12    ElseIf .cboCategory.ListIndex = 2 Then      Adjuster = 25    ElseIf .cboCategory.ListIndex = 3 Then      Adjuster = 38    ElseIf .cboCategory.ListIndex = 4 Then       CalculateRowCount = 43     Exit Function    ElseIf .cboCategory.ListIndex = 5 Then       CalculateRowCount = 44     Exit Function    ElseIf .cboCategory.ListIndex = 6 Then      Adjuster = 45     End If    CalculateRowCount = Adjuster + cboSubCat.ListIndex    Exit Function    ElseIf .cboArea.ListIndex = 2 Then    If .cboCategory.ListIndex = 0 Then      CalculateRowCount = 49 + cboSubCat.ListIndex      Exit Function    ElseIf .cboCategory.ListIndex = 1 Then      CalculateRowCount = 52      Exit Function    ElseIf .cboCategory.ListIndex = 2 Then      CalculateRowCount = 53      Exit Function    ElseIf .cboCategory.ListIndex = 3 Then      CalculateRowCount = 54      Exit Function    End If  End If    If .cboArea.ListIndex = 3 Then    CalculateRowCount = 55    Exit Function  End IfEnd WithEnd Function

Thanks again

Hi,

Been trying to figure this out for days. This is just a copy of the real file. The first 3 columns (area, category and sub-category) are chosen by the user via a combobox on userform - this part works perfectly, it is dynamic and selects the category and subcategory based on area if they exist.

The part I am stuck with is that based on those 3 selections (or 2 if looking at Area: IT Systems and Qualifications), I would like for a persons name to be displayed (preferably in a listbox as part of a userform)along with their level of competency i.e confident or very confident. So for example if IT Systems is selected from Area and Curtis Fitch from category, I want code that will look across that row and show the name of the person who has that skill and their level of skill in just that area.

The file I have attached does something similar but for the purpose of the example instead of having a combobox selection I've made it an inputbox (I'll adapt this to suit my real version), so it searches the whole column based on the persons name typed in so basically it works in the opposite order and I'm having a hard time adapting it to the way I require.

Any help will be greatly appreciated. The code is posted on module 1 of the file attached!

Also posted here: http://www.vbaexpress.com/foru…ction&p=357829#post357829

Cheers

Re: Show column heading and corresponding value based on dropdown selection

Oh I think you've misunderstood what I require. I have a vba code in module 1 that allows for me to input a name and it brings me back everything in that column pertaining to that name. What I want the code in module 1 to be able to do is allow for me to type in the area and bring back the name of the person who works in that area and their level of confidence. If you run the code on module 1 it will make more sense.

Thanks

Re: Show column heading and corresponding value based on dropdown selection

Hi,

Thanks for you response, but I can't see any difference in what you sent me as to what I posted. Have you attached the correct file?

Thanks again

Hi,

Been trying to figure this out for days. This is just a copy of the real file. The first 3 columns (area, category and sub-category) are chosen by the user via a combobox on userform - this part works perfectly, it is dynamic and selects the category and subcategory based on area if they exist.

The part I am stuck with is that based on those 3 selections (or 2 if looking at Area: IT Systems and Qualifications), I would like for a persons name to be displayed (preferably in a listbox as part of a userform)along with their level of competency i.e confident or very confident. So for example if IT Systems is selected from Area and Curtis Fitch from category, I want code that will look across that row and show the name of the person who has that skill and their level of skill in just that area.

The file I have attached does something similar but for the purpose of the example instead of having a combobox selection I've made it an inputbox (I'll adapt this to suit my real version), so it searches the whole column based on the persons name typed in so basically it works in the opposite order and I'm having a hard time adapting it to the way I require.

Any help will be greatly appreciated.

Cheers