Userform search and update

[Ashleytaylor1702]

Morning,

I have written the code below that works fine however I need to change the following and not sure it is possible:

1) When I open the user form I have to click 'cmdadd' to 'populate' the listbox, I need the listbox to be populated when opened


2)I have the search function working however I would like to be able to usE one search box to search all the columns in the database and return the relevant textboxes


3)Once the search has been performed I need an a function that can update any changes made to the text boxes and update the database


4)When a new entry has been made I need the listbox to move to the last row so what has been entered is visible

Please Login or Register  to view this content.

Thank you for your help

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

Re 1. Use the Form open Initialise event to run the code you have currently attached to a click button. i.e. create a Module level procedure and put the code in there.

[Ashleytaylor1702]

Hi,

I have attached a sample Workbook with an explanation of what I require.

Thanks for your help

[torachan]

Hi.
Can you supply the password to the embedded Solver so that it can be removed, it has no function in this app.
Also what is the purpose of the clutter of checkboxes ? Table5 needs to be cutdown to only contain data - it will expand dynamically as data is added.
All the clutter is making for a very large file already - at this stage you would normally be looking at a file 20% of this size.
Help us to help you - this should be a relatively straight forward excercise.
torachan.

[Ashleytaylor1702]

Hi.
Can you supply the password to the embedded Solver so that it can be removed, it has no function in this app.
Also what is the purpose of the clutter of checkboxes ? Table5 needs to be cutdown to only contain data - it will expand dynamically as data is added.
All the clutter is making for a very large file already - at this stage you would normally be looking at a file 20% of this size.
Help us to help you - this should be a relatively straight forward excercise.
torachan.

Thanks for your reply:

1) This password should work

Please Login or Register  to view this content.

2) The checkboxes are there so when the database is complete, certain checkboxes can be selected then a 'group' code will copy these to another sheet along with data from a userform (not included in the sample workbook)

3)Table 5 can be reduced.

Thanks

[torachan]

I have put the basic framework together handling all data and validation with tables (far easier and cleaner than dealing with ranges)
If you can explain more on what you are trying to achieve with the checkboxes there has to be an easier solution than a sheet littered with checkboxes.
Also the initial search box (now a type-in combo) searches on column1 - it can become very slow and inefficient to search an entire sheet with thousands of rows, especially if in the planning stage you recognize the fields you are most likely to search on.
Have a try with the initial attached file - post back on this site any queries.
torachan.

[Ashleytaylor1702]

I have put the basic framework together handling all data and validation with tables (far easier and cleaner than dealing with ranges)
If you can explain more on what you are trying to achieve with the checkboxes there has to be an easier solution than a sheet littered with checkboxes.
Also the initial search box (now a type-in combo) searches on column1 - it can become very slow and inefficient to search an entire sheet with thousands of rows, especially if in the planning stage you recognize the fields you are most likely to search on.
Have a try with the initial attached file - post back on this site any queries.
torachan.

Thanks a lot that works great

Only issue I can see is the listbox does not 'focus' on the last entry when 'add tooling is pressed?

The purpose of the checkboxes is to be able to select the multiple rows (there is no set number of rows and they could be anywhere within the table in the 'data' work sheet then be able to click a group button which will open a user form which will be populated from the the selected rows in 'data'. The operator will then be able to assign a quantity to each of the selected rows as well as additional data such as 'group id' and 'assembly' and 'spec' (the 'group id', 'assembly' and 'spec' would be the same for each selected row)this information will then be copied as pasted into the 'groups' sheets. I would like to have a border around each of the groups as well as a rows space between each .

see attached example

Thanks again

[Ashleytaylor1702]

Is anyone able to help with the above?

Thanks

[torachan]

Hi, Do not despair, I am going to look at it tomorrow.
It is amazing how busy life is when you are a pensioner
or is it a fact life speeds up exponentially the older you are
torachan.

[torachan]

Hi, had a quick look, see if my approach gives you what you need.
Rather than use a multitude of forms, use a multipage on one userform, you can share the variables throughout the whole form, saves setting globals and passing values.
The top three textboxes fill as required - start first group - select from upper listbox -enter quantity - downarrow transfers data to lower listbox - if wrong choice - highlight and uparrow removes-when finished transferring first group- select add another group and repeat process-when finished press transfer to GROUPS sheet - then press Save & Print.
The routine saves to USB (if stick is in port) then opens as PDF (needs AdobeReader or equivalent) then print from there.
torachan.

[Ashleytaylor1702]

Hi,

That certainly seems to be an easier solution to my problem however I seem to be having issues when I try to more the one group ? (it just seems to overwrite the first group that was entered) and if say i only entered one group then closed the form, when I open the form again I only have the option to 'build first group'
Not sure If I explained very well but the 'create tool group' process will be an ongoing tool (ie not being used once to set the tool groups up) So ideally rather than having 'build first group' and 'add another group' just having one button 'new group' would be good then when the transfer to 'group' sheet is pressed it will copy all rows within that group to the 'groups' sheet 2 rows below the the last entered group.

let me know if you have any issues

Thanks

[torachan]

Give the attached version a trial - I think I have the sequence right although I do not understand the purpose.
torachan.

[Ashleytaylor1702]

Hi Torachan,

Sorry for the late reply, your example works great thanks for your help.

Just wondering if you could have a look at the next step I require, I have included a full explanation in the attached book.

Thanks

[torachan]

A few points - consistency is important - data store all sheets as tables - do not have blank rows in data store.
In your template it is littered with merged cells - I do not work with merged cells - can be a trap for the unwary.
Best advice on how to handle merged cells in the link below.
torachan.

https://excel.solutions/2016/10/usin...ctively-excel/

[Ashleytaylor1702]

Hi,

Thanks for the advice, I have redesigned the template with 2 tables, however the tables need blank rows as the tool lists will have varying amounts of rows?

Any advice?

Thanks

[torachan]

in your template quantity and location appear to be opposed ???? which is which so that the correct data is transferred to the template.
I obviously have missed the point - if you are selecting one group only what is the purpose of selecting from a one line listbox.
When I was referring to data storage in tables it was not the template but any sheet that contains data that has to be referenced.
Blank lines are a nightmare if data has to be added or deleted your data would soon become fragmented.
torachan.

[Ashleytaylor1702]

Sorry about that the data was opposed I have fixed it in in the attached workbook.

The reason behind using a listbox was so that the user could search by either 'Assembly' OR 'Spec' and there could be multiple assembly or spec matches so therefore the user could pick the one that had the correct assembly and spec.

Thanks

[torachan]

Give the attached a try, creates pdf and unloads form, got frustrated by having to go back to first page to close form, so the close form and save code is now on the 'X'
Did not have much time to check it out so no Guarantees.
torachan

[Ashleytaylor1702]

Hi,

This works as expected however there are a few points that I would like some help with:

1) When using the 'Tool Pick List' the combo box only shows one assembly (with the same name) regardless of how many assemblies exist. Ideally I need a text box that searches column L in The 'GROUPS' sheet and then returns all matches to the listbox below (there will be multiple groups that have the same 'Assembly' but the 'Spec' will be different in that case. ie there will never but two groups with exactly the same 'Assembly' and 'Spec'

2) When on the 'New Tool Form' page and the 'Update' button is used it changes the entry stored in the 'DATA' sheet but I need it to change the entry in the 'DATA' sheet and then check if it is in the 'GROUPS' sheet and then change it if it is (So all instances of the entry are changed).

3) When using the 'Create Tool Group' page, i would like the default value in 'TextBox13' to be 1

4) I need to add the ability to be able to modify the contents of the groups within the 'GROUPS' sheet (be able to add and delete entry from the group) I have included a further explanation within the attached workbook.

Many Thanks

[torachan]

I am afraid I will have to pass on this - the data storage is developing into a 'dogs breakfast' - with more thought this could be accommodated in one data table.
List the variables that make the combinations unique, prioritise those variables, set your table with the primary key variable in column 'A' followed by secondary keys in order of priority in adjacent columns, when that format has been followed, store your data in tabular form with no blank rows.
Sorting would then become simple by cascading the sort to what level of uniqueness is required, there is no need to save separate groups, this can be done on the fly from the single database.
torachan.

[Ashleytaylor1702]

I am afraid I will have to pass on this - the data storage is developing into a 'dogs breakfast' - with more thought this could be accommodated in one data table.
List the variables that make the combinations unique, prioritise those variables, set your table with the primary key variable in column 'A' followed by secondary keys in order of priority in adjacent columns, when that format has been followed, store your data in tabular form with no blank rows.
Sorting would then become simple by cascading the sort to what level of uniqueness is required, there is no need to save separate groups, this can be done on the fly from the single database.
torachan.

Hi,

I do realize it is becoming a messy spreadsheet, I like the idea you have suggested however I'm not sure I fully understand.

Some of the entries in the in the Data sheet will be used in as many as 30 different groups so therefor do you mean I should add 30 columns of Group ID,Assembly,Spec,quantity (total 120) to the left of the table that contains the data?

I'm not opposed to the idea just want to make sure I understand fully

Thanks

[torachan]

Back to basics - am I right this is a typical everyday engineering problem - i.e. a number of 'widgets' and 'thingy's' assembled in various combinations to form different 'wotsit's' ?
Initially you would have a component level register (one table) and an assembly level register (second table).
The first table would have everything listed relevant to producing the individual 'widget', the second table would have an individual primary key 'wotsit's' id followed by a secondary 'key' calling in the number of 'widgets' & 'thingy's' needed to make up the 'wotsit'.
The data should be the minimum needed to perform the task (i.e. you do not repeat in table two any data that is in table one that can be retrieved by cross referencing the primary key relationship.
I have attached a couple of illustrations, firstly a tabular listing that enables the component selection for setting up of the packaging machinery at a pharmaceutical company.
Secondly the method of filtering a table by cascading filter comboboxes - this requires the data columns to be in order and adjacent.
Data needs to be stored in a flat tabular form - once you have the data you can produce the 'pretty' reports afterwards on the fly.
torachan.

[Ashleytaylor1702]

Back to basics - am I right this is a typical everyday engineering problem - i.e. a number of 'widgets' and 'thingy's' assembled in various combinations to form different 'wotsit's' ?
Initially you would have a component level register (one table) and an assembly level register (second table).
The first table would have everything listed relevant to producing the individual 'widget', the second table would have an individual primary key 'wotsit's' id followed by a secondary 'key' calling in the number of 'widgets' & 'thingy's' needed to make up the 'wotsit'.
The data should be the minimum needed to perform the task (i.e. you do not repeat in table two any data that is in table one that can be retrieved by cross referencing the primary key relationship.
I have attached a couple of illustrations, firstly a tabular listing that enables the component selection for setting up of the packaging machinery at a pharmaceutical company.
Secondly the method of filtering a table by cascading filter comboboxes - this requires the data columns to be in order and adjacent.
Data needs to be stored in a flat tabular form - once you have the data you can produce the 'pretty' reports afterwards on the fly.
torachan.

Thanks for your reply, What your saying is indeed what I am trying to do.
I have entered data in table format on 'Parts' sheet of my example, I have then created a second sheet called 'Assemblies' which will have the second table in.

The problem I have is for example for some assemblies I may need the following from 'Parts' sheet
x2 row 3
x1 row 4
x1 row 2
x3 row 8

What I am struggling with is how to store the data in a table style format?

Ashley

[Ashleytaylor1702]

Back to basics - am I right this is a typical everyday engineering problem - i.e. a number of 'widgets' and 'thingy's' assembled in various combinations to form different 'wotsit's' ?
Initially you would have a component level register (one table) and an assembly level register (second table).
The first table would have everything listed relevant to producing the individual 'widget', the second table would have an individual primary key 'wotsit's' id followed by a secondary 'key' calling in the number of 'widgets' & 'thingy's' needed to make up the 'wotsit'.
The data should be the minimum needed to perform the task (i.e. you do not repeat in table two any data that is in table one that can be retrieved by cross referencing the primary key relationship.
I have attached a couple of illustrations, firstly a tabular listing that enables the component selection for setting up of the packaging machinery at a pharmaceutical company.
Secondly the method of filtering a table by cascading filter comboboxes - this requires the data columns to be in order and adjacent.
Data needs to be stored in a flat tabular form - once you have the data you can produce the 'pretty' reports afterwards on the fly.
torachan.

Just had a re-read of my last message and realised it makes no sense nor does explain my issue very well

In your Tooling Database example you attached I can only see one table? Is this the assembly level register (Second table) ?

The issue I have is that in your example in Cell J2 You have defined the name of the 'Carton Tooling' as 'UKC-OO6'. I really need the abiltiy to assign other information than just the 'name' of the piece of tooling (Each piece of tooling has around 6 more fields of information as well as quantities of each piece of tooling)

Thanks

[torachan]

I will send a basic demo later today - should get a few minutes to throw something together - hopefully we will then both understand each other.
torachan.

[torachan]

Two basic lists - one contains the relevant component detail - second contains just the components per assembly - from these two you should be able build a 'build list'.
If this proves OK I would then develop the 'report template' once the data storage format was finalised.
torachan.

[Ashleytaylor1702]

Two basic lists - one contains the relevant component detail - second contains just the components per assembly - from these two you should be able build a 'build list'.
If this proves OK I would then develop the 'report template' once the data storage format was finalised.
torachan.

Just had a look and this makes sense now, it works ok however I have a couple of requests

I need to have multiple assembly ID's - Assembly ID in A, Assembly ID2 in B, Assembly ID3 in C, PartID in D, Quantity in E

I have added a third page to the userform for editing/new assemblies as I would like the ability to see all the component
information in a listbox's when building/editing a group. I have used a similar design as the one you designed previously
but I would like to be able to remove components from the assembly (Possibly with the 'Up' button) I also need to be
able 'Edit' the 'Assembly ID's (These where greyed out in your example?)

Many Thanks

[torachan]

I have to understand the logic to be able to produce something workable.
Please explain the relative makeup of assemblies in A & B - how/why ? - I am having difficulty picturing the relationship.
torachan.

[Ashleytaylor1702]

I have to understand the logic to be able to produce something workable.
Please explain the relative makeup of assemblies in A & B - how/why ? - I am having difficulty picturing the relationship.
torachan.

I have to understand the logic to be able to produce something workable.
Please explain the relative makeup of assemblies in A & B - how/why ? - I am having difficulty picturing the relationship.
torachan.

It is just more identifiers for the same assembly (these are unique to every assembly) that are used in other internal systems

For example
Attachment 715897

Thanks

[torachan]

Hi, attachment invalid - paperclip does not work.
Please use 'post quick reply' as all the quotes just take up room and cause clutter.
I await attachment so that I can understand why/what and how to fit it in logical data storage.
torachan.

[Ashleytaylor1702]

Thanks

Attachment 715908

[torachan]

Hi, attachment still invalid, cannot open.

[Ashleytaylor1702]

Try this

Thanks

[torachan]

I see it now - the 'penny has dropped' - designed to confuse - they are alternatives !!! - depends whether you are English, French or German.
See what I can come up with on Monday.
torachan.

[FDibbins]

Ashleytaylor1702 Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

[Ashleytaylor1702]

I see it now - the 'penny has dropped' - designed to confuse - they are alternatives !!! - depends whether you are English, French or German.
See what I can come up with on Monday.
torachan.

Great, I would appreciate that

[torachan]

Better late than never - life got in the way.
Give the attached a trial.
torachan.

[Ashleytaylor1702]

No problem, I have had a look and works as expected just a couple of points:

1) Ideally I need an extra page on the userform to be able to view assemblies and then generate a pdf from a template (Essentially the same as 'Edit Assemblies' page but with just the combobox and the listbox)

2) I need an extra 'search' combobox on both the 'Edit Assemblies' and 'View Assemblies' page that searches by 'Assembly ID3 as well as the original that searches by 'Assembly ID1'.

3)In the 'Edit Assemblies' I need to be able to delete an entire assembly (I'm unsure if this would be easier on a dedicated new page on the userform)

4)As per your previous spreadsheets I would like combo boxes in place of textboxes 4,6 and 8 on the components page - these will be driven by the 'VALID' sheet.

Thanks again.

[Ashleytaylor1702]

Just wondering if you are able to help with the above?

Thanks

[torachan]

Slowly, between the rigors of been a pensioner.
Taxing my one active brain cell,
attached part complete, 2 & 4, and half of 3.

[Ashleytaylor1702]

Ok, I appreciate it

[torachan]

Attached latest (final ?) version.
The pdf's consists of two tables constructed on the fly.
These can be replicated from the minimal data stored and are not permanently stored.
torachan.

[Ashleytaylor1702]

Working well now thanks - Slight problem that no more than 14 assemblies appear in the comboboxes on the View/Print page of the user form?

[torachan]

The listbox vertical scrollbar only came into view when the list was scrolled right over to the right.
I have changed the listbox setup to accommodate expanding lists.
The listbox is in a frame and this acts as a mask - now with the two external scrollbars the list can contain as many lines as you wish.
torachan.

[Ashleytaylor1702]

Sorry - I don't think I explained very well - the problem is with the 2 comboboxes (12 & 13) on the View and print page
I have lots of different assemblies stored in the 'assemblies' sheet however only the first 14 appear in the comboboxes?
I'm struggling to get my head round it

Thanks

[torachan]

Strange, I have loaded upto 20 named Assemblies, do you have the same fault in the other two pages using the similar comboboxes.
Upload the file with the fault for me to see.
torachan.

[Ashleytaylor1702]

I think I have found the problem on my workbook the assemblies sheet has 167 rows but I've noticed that the following code only looks at 100 rows

Please Login or Register  to view this content.

I tried changing the code from 100 to 1000 rows (As I anticipate i will need this amount going forward) but I'm getting an 'Out of range error'


Thanks

[torachan]

We have hit the limitations of 'transpose' - what version of Excel are you using as I was under the impression from 2010 the limitation was 3,000 rows.
I will try with other methods to filter the unique 'assemblies'.
Back later in the day.
torachan.

[Ashleytaylor1702]

This is surprising I have Office 365 Enterprise but

Attachment 717998

Does this mean I have Excel 2008?

[AliGW]

Version 2008 of Excel 365 - you are on the twice-yearly update cycle.

Please update your forum profile.

[torachan]

Attachment invalid - not sure what you have - however

replace the transpose code

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

if it works O.K. transfer the two extra declared variables to the head of the page with the rest (just to keep things neat & tidy).
torachan.

[Ashleytaylor1702]

All sorted works great - Only things I think would be helpful in the day to day use would be

1) In the 'Edit Assemblies' page it would be good to be able to add a component to an assembly on top of the existing features (Currently if a new component needs to be added I have to erase the assembly and start again)

2)Be able to change the order in which the components are stored in on the 'Assemblies' sheet - this would make the generated pdf more 'readable' if it was in a logical order

Thanks

[torachan]

1) I think is doable.
2) An illustration please - of the logical order.
torachan.

[Ashleytaylor1702]

Basically it would be the same layout as the view/print page with the comboboxes to choose the assembly and then populate a listbox (Same columns as ListBox8) - the operator can select a component and move it up or down using up and down command buttons (The same way 'Tab Order' works)
The order will change depending on the assembly so this way gives me the flexibilty to move components as needed.

[torachan]

Give the attached a trial - the insert places the new row in above the row selected in the listbox.
The reordering of the list first reorders the listbox then the data sheet is resaved with the confirm button.
I originally had the sheet updating with every move of the listbox and it was clunky.
As you have said there is likely to be 1,000 + rows I settled for saving after movement as I felt it could become slow reading back and forth to the sheet.
torachan.

[torachan]

You will get an error occur.
Debug will show the line below.
Delete it - It was an aid to check values while cycling through procedure - I deleted the label but omitted to remove code.
torachan.

Please Login or Register  to view this content.

[Ashleytaylor1702]

Just had a look,

1) Would it be possible to use a separate and new combobox to add new components as I feel it will be clearer

2)To prevent operator error, Would it be possible to use the the same combo boxes and listbox (12 Columns) on the 'View/Print Assy' page and return only 1 assembly at a time (Rather than all assemblies as there will be hundreds)

Thanks

[torachan]

Final version attached.
Most mods accommodated .
Code is beginning to get disjointed with additions.
This is about as far as I am prepared to go at present as it needs capacity checking for speed before any more additions.
torachan.

[Ashleytaylor1702]

OK I've have tested and all seems okay but would you be able to accommodate the 12 Columns instead of 5 used on the View/Print Assy' page as these columns contains the information that will be used to reorder the assemblies?

Thanks

[torachan]

Singing, dancing version, with bells & whistles.
torachan.