Userforms and Data

[Mark \(In Wales\)]

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)

 

[Kevin]

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

 

[Mark \(In Wales\)]

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)

 

[Mark \(In Wales\)]

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.