Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms and Data
Hello Group
I know you are all busy people so thanks for taking the time to help! I have a workbook that basically generates manifests for pallets - customer address and product details. The user is presented with various userforms that captures data and writes the information to a temporary sheet. The various controls are linked to a cell reference and then once an Okay button is pressed a macro then copies the information to a destination cell where everything is made pretty. I know I'm duplicating the work but can't figure out how to copy information straight into the pretty cells. For instance; if a new customer needs to be added to the customer dbase: Userform2 (information written via linking to the temp sheet) Okay button (Macro find first empty cell, copy and paste the values to the pretty sheet, sort the customers, clear original information and return control to user) Userform1 I am only up to customer 20 and the macro has slowed considerably. Would I be better off using external data source for customers? Thank you Mark (In Wales) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms and Data
I think we would need to know more about the design of
your application. Have you considered migrating to a relational database. Access is fairly easy to setup (depending on your database) and is designed for just such a problem. The hardest part is normalizing the data and determining what data needs to be in each table. If your handling VBA in excel, Access is up a level in complexity, but the Wizards provided make things easier. The code generated from the wizards is not all that elegent and is sometimes inefficient, but it works. As far as solving your problem immediately, with just 20 customers, you should not be bogging down. I guess you know that! Have you traced your code through a routine execution? Put a breakpoint in to your code at the start of the code your having problems with. Initiate the macro. Use the F8 key to move from step to step in your code. Watch carefully and note any parts of the code that are slow in executing. When you determine what parts of the code cause you problems, post back with more detail and code if you can. Kevin -----Original Message----- Hello Group I know you are all busy people so thanks for taking the time to help! I have a workbook that basically generates manifests for pallets - customer address and product details. The user is presented with various userforms that captures data and writes the information to a temporary sheet. The various controls are linked to a cell reference and then once an Okay button is pressed a macro then copies the information to a destination cell where everything is made pretty. I know I'm duplicating the work but can't figure out how to copy information straight into the pretty cells. For instance; if a new customer needs to be added to the customer dbase: Userform2 (information written via linking to the temp sheet) Okay button (Macro find first empty cell, copy and paste the values to the pretty sheet, sort the customers, clear original information and return control to user) Userform1 I am only up to customer 20 and the macro has slowed considerably. Would I be better off using external data source for customers? Thank you Mark (In Wales) . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms and Data
Hello Kevin
Have thought about the Access road but the program needs to be used with both Office 97 & XP (across a network drive). I have been led to believe that the programs are not compatable. As far as the database is concerned I am keeping things simple, Customer Name, Address 1 - 4, Post code and three drop downs. The product database is quick enough but I am not using Macros (apart from Sort and hide/protect coloums - perhaps that is the answer - give the user more freedom!?) Will run the code at work on their machines Mark (InWales) "Kevin" wrote in message ... I think we would need to know more about the design of your application. Have you considered migrating to a relational database. Access is fairly easy to setup (depending on your database) and is designed for just such a problem. The hardest part is normalizing the data and determining what data needs to be in each table. If your handling VBA in excel, Access is up a level in complexity, but the Wizards provided make things easier. The code generated from the wizards is not all that elegent and is sometimes inefficient, but it works. As far as solving your problem immediately, with just 20 customers, you should not be bogging down. I guess you know that! Have you traced your code through a routine execution? Put a breakpoint in to your code at the start of the code your having problems with. Initiate the macro. Use the F8 key to move from step to step in your code. Watch carefully and note any parts of the code that are slow in executing. When you determine what parts of the code cause you problems, post back with more detail and code if you can. Kevin -----Original Message----- Hello Group I know you are all busy people so thanks for taking the time to help! I have a workbook that basically generates manifests for pallets - customer address and product details. The user is presented with various userforms that captures data and writes the information to a temporary sheet. The various controls are linked to a cell reference and then once an Okay button is pressed a macro then copies the information to a destination cell where everything is made pretty. I know I'm duplicating the work but can't figure out how to copy information straight into the pretty cells. For instance; if a new customer needs to be added to the customer dbase: Userform2 (information written via linking to the temp sheet) Okay button (Macro find first empty cell, copy and paste the values to the pretty sheet, sort the customers, clear original information and return control to user) Userform1 I am only up to customer 20 and the macro has slowed considerably. Would I be better off using external data source for customers? Thank you Mark (In Wales) . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userforms and Data
Found the culprit - macro calls for the worksheet changes to be saved but
for some reason it is not showing on the status bar? Have now added a button control on in the sheet so the user can see what is happening. "Mark (In Wales)" wrote in message ... Hello Kevin Have thought about the Access road but the program needs to be used with both Office 97 & XP (across a network drive). I have been led to believe that the programs are not compatable. As far as the database is concerned I am keeping things simple, Customer Name, Address 1 - 4, Post code and three drop downs. The product database is quick enough but I am not using Macros (apart from Sort and hide/protect coloums - perhaps that is the answer - give the user more freedom!?) Will run the code at work on their machines Mark (InWales) "Kevin" wrote in message ... I think we would need to know more about the design of your application. Have you considered migrating to a relational database. Access is fairly easy to setup (depending on your database) and is designed for just such a problem. The hardest part is normalizing the data and determining what data needs to be in each table. If your handling VBA in excel, Access is up a level in complexity, but the Wizards provided make things easier. The code generated from the wizards is not all that elegent and is sometimes inefficient, but it works. As far as solving your problem immediately, with just 20 customers, you should not be bogging down. I guess you know that! Have you traced your code through a routine execution? Put a breakpoint in to your code at the start of the code your having problems with. Initiate the macro. Use the F8 key to move from step to step in your code. Watch carefully and note any parts of the code that are slow in executing. When you determine what parts of the code cause you problems, post back with more detail and code if you can. Kevin -----Original Message----- Hello Group I know you are all busy people so thanks for taking the time to help! I have a workbook that basically generates manifests for pallets - customer address and product details. The user is presented with various userforms that captures data and writes the information to a temporary sheet. The various controls are linked to a cell reference and then once an Okay button is pressed a macro then copies the information to a destination cell where everything is made pretty. I know I'm duplicating the work but can't figure out how to copy information straight into the pretty cells. For instance; if a new customer needs to be added to the customer dbase: Userform2 (information written via linking to the temp sheet) Okay button (Macro find first empty cell, copy and paste the values to the pretty sheet, sort the customers, clear original information and return control to user) Userform1 I am only up to customer 20 and the macro has slowed considerably. Would I be better off using external data source for customers? Thank you Mark (In Wales) . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA to run every month and sorting data from userforms | Excel Discussion (Misc queries) | |||
Userforms | Excel Programming | |||
Saving data in userforms on action | Excel Programming | |||
UserForms and Variables and Types of data | Excel Programming | |||
Userforms - Dealing with zeros and non-entry of data | Excel Programming |