Office 365 - Using ComboBox / Text Box & Lists from Dynamic Tables

[JILBO]

Hello All!





Ok i'll try to explain but hopefully the attached workbook will do abetter job!





I've a Build Book where through 'X' steps we illustrate how anassembly is built. Currently it's a manual type task, but I'd like to be ableto automate it if possible.





1 -ComboBox uses a Table within the sheet (Named[Step_BOM'x']) to populate its list.This Table name changes depending on which Step you are in so the ComboListneeds to just refer to the List object Table within the Active Sheet.


2 - Select Item


3 - Prompted for QTY used


4 - Concatenated Item & Qty value used within Text Box


5 - Item QTY used is populated within the [STEP QTY] in the Table *Appreciating this step might not be possible





I've got a little of the way, once you select the item you have todouble click the comboxbox (not the end of the world) but ideally I'd like itrun to upon selection of the item.





Also anyway of showing the selected item (concat within the comboxbox after selection?) currently just shows 1 column





Thanks in Advance!

[p45cal]

1 -ComboBox uses a Table within the sheet (Named[Step_BOM'x']) to populate its list.This Table name changes depending on which Step you are in so the ComboListneeds to just refer to the List object Table within the Active Sheet.
If you know the location of just one cell of the Table you can use the likes of:
Range("A60").ListObject.DataBodyRange
Since an unqualified range in a sheet's code-module always refers to that sheet, the right table will be referred to.





3 - Prompted for QTY used
This seems to be working already.





4 - Concatenated Item & Qty value used within Text Box
You seem to have done that already.





5 - Item QTY used is populated within the [STEP QTY] in the Table *Appreciating this step might not be possible
That can be done by say:
Range("A60").ListObject.DataBodyRange.Cells(.ListIndex + 1, 5).Value = QtyAdd





I've got a little of the way, once you select the item you have todouble click the comboxbox (not the end of the world) but ideally I'd like itrun to upon selection of the item.
Using the single click-event of combobox/listbox can be a pain because it fires so often. I've left is as a double-click.





Also anyway of showing the selected item (concat within the comboxbox after selection?) currently just shows 1 column
Not with a combobox; although you see more columns in the dropdown, you only see one column (of your choice, mind) when it closes up.
A Listbox will let you see all the columns all the time.





* How do you get the column Headers to appear in the combo box?
Set the ColumnHeads property to true but this will only work if you use the ListFillRange property to populate the listbox.

I've also used the Sheet_Activate and Listbox_GotFocus events to populate the listbox (and maintain its size).
So now, when you copy the sheet, the code should copy with it without need for adjustment.

All in the attached.

[JILBO]

Sir, you are an absolute legend!!!

Just looking at it now and it works perfectly! Have you ever worked with combobox's in the Ribbon?

Many Thanks once again P4CAL !!!!

[JILBO]

P45CAL, Wondering of the best way to approach adding this functionality to every "Step" Sheet created (I have a template that its created from). Do I need a way of dynamically naming the ListBox?

My very basic VBA Skills are being pushed....which is good as i'm starting to get it!

[p45cal]

Never done any work with the ribbon.
I don't think you need to worry about the Listbox name; when I copy a sheet the name remains. It's shouldn't be a peoblem because the code is in the sheet's own code module, so unqualified references always refer to what's on that sheet.

[p45cal]

Just has a thought - when you say you have a template do you mean a proper Excel template (with the extension .xltx/.xltm/.xlt) which is usually saved in your templates folder which you can use to insert a sheet(s) in any workbook (or even create a new document from), or are you just talking about copying an existing sheet in you workbook?

[JILBO]

Yes I did just notice the LISTBOX name doesn't change....that's one less worry.

No its just a sheet within the workbook, only problem is the table is copied and pasted into the 'Step' from another tbl within the workbook when created(Template is Blank)...so I need to figure out how to order things so it doesn't throw a wobbly!

Would you mind looking if I create another example with more data etc??

[JILBO]

The ListBox Seems to almost shrink when you copy it from one worksheet to another. Is there a way of 'Refreshing' it so comes back to full size without selecting it I wonder?

[p45cal]

only problem is the table is copied and pasted into the 'Step' from another tbl within the workbook when created(Template is Blank)...so I need to figure out how to order things so it doesn't throw a wobbly!As long as the table is pasted into the same place (eg. cell A60) each time the name of the table doesn't matter. That's what Range("A60").ListObject.DataBodyRange… is all about.




The ListBox Seems to almost shrink when you copy it from one worksheet to another. Is there a way of 'Refreshing' it so comes back to full size without selecting it I wonder? Yes, that's already in place with the likes of:

With Me.ListBox1
.ColumnCount = 4
.ListFillRange = Range("A60").ListObject.DataBodyRange.Address
.Width = 500 'activex objects are not well behaved re size on a sheet!
.Height = 100
.Top = 135
.Left = 1250
.ColumnWidths = "60;120;50"
'Debug.Print .Top, .Left ' I shouldn't have left this line in, you can delete it.
End With
and :
Private Sub Worksheet_Activate()
ListBox1_GotFocus
End Sub
You can call ListBox1_GotFocus with other events if you want.




Would you mind looking if I create another example with more data etc??Sure.

[JILBO]

Thanks again for your assistance I'm going to have a bit more of a play...its still be funny with resizing and then you have to click in the list and it resizes again. Some more head scratching!

[JILBO]

Question P45CAL, when you select an item from the ListBox and it populates within the text box, it hangs or pauses until you select back into the box...and sometimes that doesn't work. Any ideas on how to make it stop doing that, I've tried selecting the first item within VBA but no luck.

Had a bit of play and it seems the following is where it hangs/freezes
'Range("A60").ListObject.DataBodyRange.Cells(.ListIndex + 1, 5).Value = QtyAdd