RobL1982
06-17-2011, 11:41 AM
Hi,
I'm new to vba, and need some advice or clear help on how to do this. Due to enduser preference, knowledge, and software installed on our machines, I'm using Excel 2007 to do the following. I have a worksheet I am trying to make user friendly that enables the enduser to essentially search and identify material as well as create pricing after an analytical choice is made. Adjacent to material id info fields, are 2 fields per each warehouse that identify first, Available balance, and secondly identifies the unit Cost of that item at that warehouse. Beyond that are fields in which the enduser will enter cost and markup information that they choose, and there is also a field with a live formula to build the resale based on those entries.
Please see attached sample which is a very basic version of the table I am working from. Inside it you can see identifiable data such as attributes as well as complete id info such as part numbers and description from columns A-F. The inventory balances and their associated cost continue from column G-AF. Columns AG-AK are the columns that will allow entries by the enduser based on the information they analyze from the preceding columns.
Essentially what I would want to try next is to build a VBA search feature that allows the enduser to enter multiple search attributes, and then find the first best match with the ability to find the next match, the next match after that and so on. It would be similar to the built in Find function, but we are looking for multiple boxes to enter in multiple attributes. Auto-filter is just simply not enough. We will be using the filter in addition as an option for some folks, but we need a built in feature that is user friendly. I'm dealing with people that do not know Excel or Access nor do they care to learn it. Moreover, the multiple search boxes, are similar to what they use now on our system. So we are trying to simulate something close to what they already know and are comfortable using.
I'm not sure where to even start on this. I've read about inputboxes/textboxes and sort of felt like that might be the right direction, but I don't know how to build them with the proper coding. In addition, I'm not sure if we need more attribute fields in the table, as to serving a better functionality with these multiple search boxes. I do know that current searches in our system include attributes as I have mentioned. They have several fields in the search function, and if they were looking for a 2" 150# widget class A carbon steel, in 3 or so fields they would enter 2" in one, 150# in the next, and perhaps carbon steel in the last. They have up to 5 fields to enter attributes into, so that is what I am looking to mimic. The system search also "returns" the matches, however, I'm looking to find the first match in an open table of data, then find next and so on. It is because of the user needing to enter data in AG-AK that I draw my concern on how we search and arrive at the matches. We are using this attached table to easily locate material, balance, cost, and then to create pricing to upload into the system based on chosen cost/balance data. The whole idea is to consolidate what is already a cumbersome process in our system of multiple screens by single item data search, and then to use this attached table to provide easy to use similar functions that require little training in Excel. Perhaps these are conflicting ideas, and perhaps the return of matching data is the best route. These are the things I need feedback on as well as any coding that will help us achieve what we are trying to do.
Thanks,
Rob
I'm new to vba, and need some advice or clear help on how to do this. Due to enduser preference, knowledge, and software installed on our machines, I'm using Excel 2007 to do the following. I have a worksheet I am trying to make user friendly that enables the enduser to essentially search and identify material as well as create pricing after an analytical choice is made. Adjacent to material id info fields, are 2 fields per each warehouse that identify first, Available balance, and secondly identifies the unit Cost of that item at that warehouse. Beyond that are fields in which the enduser will enter cost and markup information that they choose, and there is also a field with a live formula to build the resale based on those entries.
Please see attached sample which is a very basic version of the table I am working from. Inside it you can see identifiable data such as attributes as well as complete id info such as part numbers and description from columns A-F. The inventory balances and their associated cost continue from column G-AF. Columns AG-AK are the columns that will allow entries by the enduser based on the information they analyze from the preceding columns.
Essentially what I would want to try next is to build a VBA search feature that allows the enduser to enter multiple search attributes, and then find the first best match with the ability to find the next match, the next match after that and so on. It would be similar to the built in Find function, but we are looking for multiple boxes to enter in multiple attributes. Auto-filter is just simply not enough. We will be using the filter in addition as an option for some folks, but we need a built in feature that is user friendly. I'm dealing with people that do not know Excel or Access nor do they care to learn it. Moreover, the multiple search boxes, are similar to what they use now on our system. So we are trying to simulate something close to what they already know and are comfortable using.
I'm not sure where to even start on this. I've read about inputboxes/textboxes and sort of felt like that might be the right direction, but I don't know how to build them with the proper coding. In addition, I'm not sure if we need more attribute fields in the table, as to serving a better functionality with these multiple search boxes. I do know that current searches in our system include attributes as I have mentioned. They have several fields in the search function, and if they were looking for a 2" 150# widget class A carbon steel, in 3 or so fields they would enter 2" in one, 150# in the next, and perhaps carbon steel in the last. They have up to 5 fields to enter attributes into, so that is what I am looking to mimic. The system search also "returns" the matches, however, I'm looking to find the first match in an open table of data, then find next and so on. It is because of the user needing to enter data in AG-AK that I draw my concern on how we search and arrive at the matches. We are using this attached table to easily locate material, balance, cost, and then to create pricing to upload into the system based on chosen cost/balance data. The whole idea is to consolidate what is already a cumbersome process in our system of multiple screens by single item data search, and then to use this attached table to provide easy to use similar functions that require little training in Excel. Perhaps these are conflicting ideas, and perhaps the return of matching data is the best route. These are the things I need feedback on as well as any coding that will help us achieve what we are trying to do.
Thanks,
Rob