Why would a worksheet that is normally 57 kb turn into 500kb after using
macros to enter data into it?

Example: I have a workbook that has a database in it, and a userform that
takes that data based on ssn's and places that specific data in appropriate
places in a spreadsheet that is made to look like a form. Then the user uses
a commmand button/macro to save the 'form' with the data in it, to a separate
folder. The new file is now 500 plus kbs.

I even went so far as to delete all the data, lines etc... and the file size
changed to 475 kb. Not a lot different.

Is there another solution to this file size dilemna?

Thankyou.

Your code is probably initializing cells that end up by not being used. They
have the overhad of any other cell, they just happen to be blank. This is
common if you hard code ranges into your macro. Take a look at the sheets
after your macro is done. Do the scroll bars allow you to move well beyond
the range of cells with values int them? If so all of the cells that you are
seeing beyond the last row and column of data need to be deleted. You are
best off to avoid creating these cells in the first place. To make the file
small again, delete (not clear contents) all of the blank rows at the bottom
and all of the blank column to the right. Now when you save your file you
should notice that it goes back to being small again...
--
HTH...

Jim Thomlinson


"BigPig" wrote:

Why would a worksheet that is normally 57 kb turn into 500kb after using
macros to enter data into it?

Example: I have a workbook that has a database in it, and a userform that
takes that data based on ssn's and places that specific data in appropriate
places in a spreadsheet that is made to look like a form. Then the user uses
a commmand button/macro to save the 'form' with the data in it, to a separate
folder. The new file is now 500 plus kbs.

I even went so far as to delete all the data, lines etc... and the file size
changed to 475 kb. Not a lot different.

Is there another solution to this file size dilemna?

Thankyou.

Jim,

Thankyou for your response.

I will try and create a macro to delete all the rows and columns outside of
the worksheet made to look like a form and add that to a macro that just
saves the worksheet/form.

Some more info: In my workbook, there are 4 worksheets of which 2 are only
imporant. One is the form, the other is a database that I created by pulling
data from a sharable access database. From the form sheet the user can select
the command button that brings up a user form. The user form allows the user
to select ssn via combo box, and then other macros using index match,
extracts data from the data sheet and places the data into the appropriate
textboxes in the user form. Then if the data is correct, the user selects a
command button that enters that data into the spreadsheet 'form'. After
which another command button on the user form is used to save that specific
spreadsheet form into a file in another folder without the other sheets
associated with the workbook.

I know, sounds like a lot of extra work. The end state of this process is:
to minimize the file size, reduce time and effort looking up ssns, names and
whatnot, and reduce errors. Everthing works great, except for the file size.

I think that the problem with the file size is like you mentioned. I believe
it's the macro on the user form that indexes and matches the data from the
data sheet. I don't know for sure though.

As far as I see it, I have two options.

One, try your suggestion.

Two using a macro, take the data stored in the user form and place it in
another spreadsheet and save it for reference. Create another command
button/combobox/macro that will allow the user to retrieve data previously
used to populate the spreadsheet form in the event it has to be
accessed/referenced.

The problem with option two is that I don't know how via macro to dump that
data into another spreadsheet so that it doesn't overwrite what may have been
placed there previously via the same method. What I am asking is could you
provide an example macro that would allow me to put that data into a row, so
that subsequent applicants' data would be placed in one row down from that
etc, so that the record is kept and rows aren't overwritten.

I know I am asking a lot, so any advice would be greatly appreciated.

Thankyou again for your help.


"Jim Thomlinson" wrote:

Your code is probably initializing cells that end up by not being used. They
have the overhad of any other cell, they just happen to be blank. This is
common if you hard code ranges into your macro. Take a look at the sheets
after your macro is done. Do the scroll bars allow you to move well beyond
the range of cells with values int them? If so all of the cells that you are
seeing beyond the last row and column of data need to be deleted. You are
best off to avoid creating these cells in the first place. To make the file
small again, delete (not clear contents) all of the blank rows at the bottom
and all of the blank column to the right. Now when you save your file you
should notice that it goes back to being small again...
--
HTH...

Jim Thomlinson


"BigPig" wrote:

Why would a worksheet that is normally 57 kb turn into 500kb after using
macros to enter data into it?

Example: I have a workbook that has a database in it, and a userform that
takes that data based on ssn's and places that specific data in appropriate
places in a spreadsheet that is made to look like a form. Then the user uses
a commmand button/macro to save the 'form' with the data in it, to a separate
folder. The new file is now 500 plus kbs.

I even went so far as to delete all the data, lines etc... and the file size
changed to 475 kb. Not a lot different.

Is there another solution to this file size dilemna?

Thankyou.