Show column headings and corresponding values/text based on dropdown selection

[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 Long
Dim Rng As Range, cel As Range
Dim wArea4 As Range
Dim WS As Worksheet
Dim iRes As Integer
Dim strPrompt As String
Dim strTitle As String


Set WS = Worksheets("JLP Content")


With WS
For Each wArea4 In .Range("StrategyConsultancySubC")
If Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 2
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 3
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 4
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 3 Then
ResultsScreen.rowCount = 5
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 4 Then
ResultsScreen.rowCount = 6
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 5 Then
ResultsScreen.rowCount = 7
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 6 Then
ResultsScreen.rowCount = 8
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 7 Then
ResultsScreen.rowCount = 9
ElseIf Me.cboArea.ListIndex = 0 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 8 Then
ResultsScreen.rowCount = 10
End If
Next

For Each wArea4 In .Range("ConceptDevSubC")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 11
End If
Next

For Each wArea4 In .Range("DesignArtDirectionSubC")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 12
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 13
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 14
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 3 Then
ResultsScreen.rowCount = 15
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 4 Then
ResultsScreen.rowCount = 16
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 5 Then
ResultsScreen.rowCount = 17
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 6 Then
ResultsScreen.rowCount = 18
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 7 Then
ResultsScreen.rowCount = 19
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 8 Then
ResultsScreen.rowCount = 20
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 9 Then
ResultsScreen.rowCount = 21
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 10 Then
ResultsScreen.rowCount = 22
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 11 Then
ResultsScreen.rowCount = 23
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 12 Then
ResultsScreen.rowCount = 24
End If
Next

For Each wArea4 In .Range("CopyWritingSubC")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 25
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 26
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 27
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 3 Then
ResultsScreen.rowCount = 28
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 4 Then
ResultsScreen.rowCount = 29
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 5 Then
ResultsScreen.rowCount = 30
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 6 Then
ResultsScreen.rowCount = 31
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 7 Then
ResultsScreen.rowCount = 32
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 8 Then
ResultsScreen.rowCount = 33
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 9 Then
ResultsScreen.rowCount = 34
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 10 Then
ResultsScreen.rowCount = 35
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 11 Then
ResultsScreen.rowCount = 36
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 12 Then
ResultsScreen.rowCount = 37
End If
Next

For Each wArea4 In .Range("PhotographySubC")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 38
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 39
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 40
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 3 Then
ResultsScreen.rowCount = 41
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 4 Then
ResultsScreen.rowCount = 42
End If
Next

For Each wArea4 In .Range("StylistsSubCat")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 4 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 43
End If
Next

For Each wArea4 In .Range("SetDesignBuildSubC")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 5 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 44
End If
Next

For Each wArea4 In .Range("VideographySubCat")
If Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 6 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 45
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 6 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 46
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 6 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 47
ElseIf Me.cboArea.ListIndex = 1 And Me.cboCategory.ListIndex = 6 And Me.cboSubCat.ListIndex = 3 Then
ResultsScreen.rowCount = 48
End If
Next

For Each wArea4 In .Range("ArtWorkingSubC")
If Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 49
ElseIf Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 1 Then
ResultsScreen.rowCount = 50
ElseIf Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 2 Then
ResultsScreen.rowCount = 51
End If
Next

For Each wArea4 In .Range("RetouchAndRepSubC")
If Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 1 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 52
End If
Next


For Each wArea4 In .Range("AudioAndVisSubC")
If Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 2 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 53
End If
Next


For Each wArea4 In .Range("DigitalBuildSubC")
If Me.cboArea.ListIndex = 2 And Me.cboCategory.ListIndex = 3 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 54
End If
Next


For Each wArea4 In .Range("QuickTAroundSubC")
If Me.cboArea.ListIndex = 3 And cboCategory.ListIndex = 0 And Me.cboSubCat.ListIndex = 0 Then
ResultsScreen.rowCount = 55
End If
Next
End With


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
Next

End Sub




Thank you in advanced :)

[SamT]

What do all the wArea4 ranges have to do with ResultsScreen.rowCount? Only the 3 comboBox values are looked at. I think all those If...ElseIf's can be rewritten nto a lot fewer lines.

[Daph1990]

@samT wArea4 refers to the ranges I've named on the sheet 'JLP content' and that determines which row data to bring back based on the users selections. I am with you that there is most likely an easier way...when I get something to work I just go with it

[SamT]

THe thing is, since no cell is referenced when computing ResultsScreen.rowCount, All that is happening is that it is recomputed once again for each cell, without making any difference to its value.

See if this code works
Option Explicit

Private Sub cbResult_Click()

Dim r As Long, Col As Long, i As Long
Dim Rng As Range, cel As Range
Dim wArea4 As Range
Dim WS As Worksheet
Dim iRes As Integer
Dim strPrompt As String
Dim 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
Next

End Sub


Private Function CalculateRowCount() As Long
Dim Adjuster As Long

With 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

ElseIf .cboArea.ListIndex = 3 Then
CalculateRowCount = 55
Exit Function
End If

End With
End Function

[Daph1990]

Hi @samT I appreciate the tidy up of the code, but that doesn’t actually solve the issue that I’m having. I want to be able to view the info about each supplier. Have you opened the attachment?

Sorry to be a pain but I was after a solution not a tidy up of the current code. Thanks a lot

[SamT]

I had to "tidy up" the code in order to understand the code.


Have you opened the attachment?Not yet... One step at a time.

[SamT]

OK, I tried to edit your File, but it has killed my Excel, and I will need to reinstall it.

In the meantime, here is a generic code that goes in the Suppliers Data Sheet. It is not tested or compiled. I hope it gives you some ideas.


Public Function SupplierDetails(SupplierName As String) As String
Dim Found As Range

Set Found = Columns("A").Find(SupplierName)
If Found Is Nothing Then
SupplierDetails = "Supplier " & SupplierName & " Not Found!"
Exit Function

SupplierDetails = Join(Found.Resize(1, LastColumn(Found)), "; ")
End Function



Private Function LastColumn(Optional Rng as Range) As Long
If Rng is Nothing, Then Set Rng = Cells(1)
LastColumn = Cells(Rng.Row, Columns.Count).End(xlToLeft).Column
End Function



Sub Test_SupplierDetails()
Dim X
X = SupplierDetails("John Brown Media")
End Sub



To use this code pattern, (basically a Worksheet Method,) USe a form of

szVar =Sheets("Supplier Data").SupplierDetails(SupplierName)

[Daph1990]

Thank you @SamT I'll give it a go now!

[Daph1990]

The first bit of "tidy up" code works but is a little temperamental with when it returns the right values, must admit I'm not quite sure what parts of the code are saying? I.e the use of adjuster + list index

[SamT]

"Adjuster" provides the starting point to add the ListIndex to

[Daph1990]

Hi, been playing around with this for most of the morning and the amendment to my code works perfectly so thank you very much (it's so much shorter) but still having issues getting the info from the supplier data tab to show up.

Thanks for your help so far!

[Daph1990]

The code entered into the supplier data sheet using the worksheet method doesn't seem to be doing anything. It's still bringing back just the supplier name from the JLP content sheet. Anymore guidance you have would be greatly appreciated.

Thanks

[SamT]

Edit as shown

LastColumn = Cells(Rng.Row, Columns.Count).End(xlToLeft).Column
MsgBox Cells(Rng.Row, Columns.Count).End(xlToLeft).Address
End Function


Does the Message show the Row and last used column Address?

[Daph1990]

Nothing happens at all, I added the
szVar =Sheets("Supplier Data").SupplierDetails(SupplierName)

as a worksheet function to the button itself which doesn't work. Also, when I attempt to run the sub test supplier details nothing happens either. Not sure if I've put this code in the right place or not but all attempts seem to not work.
Thank you

[SamT]

Fixed, Improved, Compiled, and tested

THis code goes in the Suppliers Data Code page


Option Explicit

'---------------------------------------------------------------------
Public Function SupplierDetails(SupplierName As String) As String
Dim Found As Range

If SupplierName = "" Then
SupplierDetails = "No Supplier Name provide to Function"
Exit Function
End If

Set Found = Columns("A").Find(SupplierName)
If Found Is Nothing Then
SupplierDetails = "Supplier " & SupplierName & " Not Found!"
Exit Function
End If '<----- Added

SupplierDetails = Join(sourcearray:=Application.Transpose(Application.Transpose(Found.Resize( 1, LastColumn(Found)))), delimiter:="; ")
End Function


'-----------------------------------------------------------------
Private Function LastColumn(Optional Rng As Range) As Long
If Rng Is Nothing Then Set Rng = Cells(1)
LastColumn = Cells(Rng.Row, Columns.Count).End(xlToLeft).Column

End Function

'------------------------------------------------------------------------
'Run this sub to see results of Above Function
Sub Test_SupplierDetails()
MsgBox Me.SupplierDetails("John Brown Media")

MsgBox Me.SupplierDetails("Wrong Name Input")
End Sub


In any other Code module

Sub Test_SupplierDetails()
'If you use Sheet CodeNames, replace Sheets("Supplier Data") with just the Code Name
MsgBox Sheets("Supplier Data").SupplierDetails("John Brown Media")

MsgBox Sheets("Supplier Data").SupplierDetails("Wrong Name Input")
End Sub

[Daph1990]

This is amazing it works in terms of returning the row of detail, however I want these supplier details to correspond with the user selections so in all instances there will be more than one supplier that falls into each of the drop down categories as can be seen from the JLP content sheet and I'd want it returned in a listbox as it currently is.

So for e.g. if a user selected strategy from the first drop down, then strategy consultancy for the second drop down, the content for the third drop down there (all shown on JLP content sheet) there could be 5 suppliers that service those needs and it would be those suppliers that I would want the detail for those suppliers so it wouldn't necessarily just be 'John Brown' or 'Adam & Eve' it could be both of them and more so I'd want the 6 column headings returned in my listbox.

I can't keep saying thank you enough!

[SamT]

ListBox.List and ComboBox.List can be 2D arrays. However, for your Categories, Sub-Categories, and Areas, I would just us a Listbox, since they all just use one column. The Result, I would show in a Combobox, since it makes it easier to separate values if you want to e.g. send eMail or open Word with some values auto filled in.

It is my personal preference to have Worksheet Objects do all the work on the sheet, and just provide the results of that work to any requesting code, as with my previous "SupplierDetails" function. Note that you won't have to midify that Function, We can merely loop thru the list of Supplier names and the Worksheet Object will return the details for each. My goal with this style of programming is that if you have to change a worksheet's layout, the only code you have to change is in that worksheet.

Because any User can change the Sheet's Tab Name, I always edit the sheet's CodeName. In the VBA Project Explorer, (Press Ctrl+r to see,) the Tab Name is in Parentheses, and follows the CodeName. In the VBA Properties Window, (Press F4 to see,) the CodeName Property Is called "Name," but it is in Parenthesewss, (Not the Property Value, Just the Property name,) and the name of the Tab Name Property is also "Name" but it is not in Parentheses, and neither is the Property Value. (Blame MS for the two names to be reversed as to which is in Parentheses.) For example, in your attachment, Sheet Tab Name "JLP Content" has the CodeName "Sheet4". I suggest that you change the CodeNames to "SupData" and "JLP" or "JLPContent."

The useage in code is simple... Use the CodeName without the Sheets("???") and use the Tab Name with Sheets("???"). E.g. JLP = Sheets("JLP Content"), then you can change Tab Names without effecting any code you may write.


Your two Data Sheets are well designed for use with Code... There are no, none, zero, Merged Cells, and there are no Blank Rows or columns between any Sections. Merged Cells are difficult to code around, but Blank Rows can be easily dealt with, although Blank Rows and Columns usually delineate different Tables and can by very useful in the right circumstances.

as to the UserForm "StartScreen" I strongly suggest that you delete the Category Command buttons and just use a Category ListBox. Both can work, but using CommandButtons means that if you modify Sheets("JLP Content"), you might have to rewrite the code in the UserForm. This is not the case with a ListBox. I would also Drag the Areas ListBox below the SubCategory ListBox since the Dependent Hierarchy is Category, then SubCategory, then Areas.

While you think about all this, I will start work on the JLP code for the Categories List. When that works, we can do the Dependent SubCategories list. Code, Test, Perfect, Retest, Rinse and Repeat for the next bit of code.

[SamT]

Here's what I've one so far. I changed the ComboBoxes to Listboxes, because I can make them do thing Combos can't E.g. Show the entire list without clicking a dropdown arrow.

[Daph1990]

This is great @SamT I've made the changes that you suggested above and it makes a lot of sense! I would have never thought to make it work like that...thank you so much. Can't wait to see how the dependent sub-cats work etc.:beerchug::beerchug:

[SamT]

I had Version 2 working, then I modified it to provide more robustness and features, It's still under construction ATT.

[Daph1990]

Thanks @SamT!!!

[SamT]

I haven't forgotten, just been really sore and really busy, which busyness ain't helping the soreness.

:crying:

[Daph1990]

Oh no...hope it's nothing to serious?? :boohoo:boohoo

[Daph1990]

Hi Sam, do hope you're feeling better - any luck with a potential fix?

Thank you

[SamT]

Here's what I have so far. Next: Working on the Suppliers Details List box.

[Daph1990]

That's great Sam, look forward to seeing the final bit, the supplier detail part which has been the bane of my life lol!

[Daph1990]

Hi Sam, any indication of when this final part will be completed as I really want testing time...sorry to be a pain but have still been attempting work on it the background but to no avail.

Look forward to hearing back.

Thanks again!

[SamT]

Oops, I need to modify the rest of the code to work t=with the new Supplier details sheet and Form.

One more day. Please!

I hope:ipray:

[Daph1990]

Okay Sam...thanks!

[SamT]

:crying: :banghead:

The code looks like it should work... But there are (at least) two bugs.

Bug one: When selecting a category or sub category, the control shows the next selection, not the one selected. Why don't you work on this. This one is in the various Change Event Subs. It might not even happen in Excel > 2003

Bug two: When The Areas Change Event is triggered to Load the Results (frmSupplierDetails,) Form It Raises a VBIDE Notice that "This will reset your project." I will work on this disturbing bug.

[Daph1990]

Hi Sam,
Thanks for this, not sure if I'm doing something totally wrong here but nothing happens when I press the 'Results' button, is there now another way of generating the results from the selections? As I'm not even able to see Bug 2 because of this.

Bug 1 is happening with my version of excel but it doesn't happen every time, I'm looking this code now,

[Daph1990]

Hi Sam,

Anymore of an update on this as I'm still unable to run the code?

[SamT]

I'm so sorry... My head must be in the right place for Program Engineering and right now, it's full of Mechanical Engineering and Fabrication that I must get done ASAP.

I've gotten this far. The mechanics are working, but the layout and control design need tweaking.

In fact, all the code could be more elegant, but I don't have time for elegance right now.

[Daph1990]

Please, don't be sorry I'm grateful for all the help. I'm just looking through it now, but the mechanics don't quite work in that it still isn't taking any info from the supplier detail sheet it's pretty much the same as it was beforehand which is showing the info from the content sheet only. Is this the right version?

Cheers!

[SamT]

No, that version was just to fix the bug I mentioned before. I'm still working on the Supplier Details Form. I just now found an issue that's driving me buggy. Somehow, the code is skipping a bunch of executions :banghead::banghead::banghead:


But only when running normally, Iyt worls fine when stepping thru with F8

Be back later.

[Daph1990]

I'm having some issues when stepping through on F8 but I can work on that. How is the supplier detail part coming along?

[Daph1990]

Is it still awful, and not coming together?

[SamT]

I'm working on what should be the last sub for getting the Supplier Details into an array.

I work on your project most mornings while I am still a bit sane.

[Daph1990]

Hehe I know the feeling. Have you got an estimated time frame of when things should be ready?

[Daph1990]

Hi Sam,

Hope you're well. Sorry to chase again but really keen to know when this will be ready?

Thank you