URGENT!!! shared workbook prob

I have set up a custom form in excel for users to select or type data into
that then sends the information to a new sheet in a list format at button
click e.g

Email only recontact size Budget
yes no 100 5000
no yes 200 6000 #1

I then made it a shared workbook with the necessary ranges unprotected. but
say for instance i am adding new data into the form thus creating a new
record it places it underneath the previous record, but if my partner is also
adding new records in the shared workbook at the same time abviously some of
his cells and my cells conflict so when saved you have to change the previous
persons work thus loosing the records the other user made. is there a macro
or an option or somthing to say when saved if data in cells in range #1
(example)do not change make saved entry go to unused cells underneath the
last record, and i dont want to have to start again because i have several
complex cost, losses and estimate calculations running off that data being
stored their to be able to remember wich i need to change. Can you help

A.G.,

Keep in mind that with a shared workbook, each user has a copy of the
workbook the way it was when it was opened. It isn't really multiuser.
Perhaps you should have your macro put the data in a separate workbook,
which it opens, appends, then closes. Then the other one can do the same.
Just use the original workbook as a front end. You'll get an error if a
second user's macro tries to open it while the first one has it, but the
code can try again later, or whatever.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"A.G.M ash" <[email protected]> wrote in message
news:[email protected]...
>I have set up a custom form in excel for users to select or type data into
> that then sends the information to a new sheet in a list format at button
> click e.g
>
> Email only recontact size Budget
> yes no 100 5000
> no yes 200 6000 #1
>
> I then made it a shared workbook with the necessary ranges unprotected.
> but
> say for instance i am adding new data into the form thus creating a new
> record it places it underneath the previous record, but if my partner is
> also
> adding new records in the shared workbook at the same time abviously some
> of
> his cells and my cells conflict so when saved you have to change the
> previous
> persons work thus loosing the records the other user made. is there a
> macro
> or an option or somthing to say when saved if data in cells in range #1
> (example)do not change make saved entry go to unused cells underneath the
> last record, and i dont want to have to start again because i have several
> complex cost, losses and estimate calculations running off that data being
> stored their to be able to remember wich i need to change. Can you help
>

earl
I started thinkin that afta i posted the question, which creates a couple
more could i do that using a web page form if so how? it doesnt run the
macros or show the command buttons if done in excel then saved as a .html or
..mht becuase if it did i wouldnt be in this sittuation cos then i could
upload it and it would be updated instanly! something for microsoft to think
about,

also does it HAVE to open then append then save and close?

DO YOU KNOW WHAT I WOULD NEED TO ADD TO THIS CODE TO SEND IT TO THE WORKBOOK

Private Sub CommandButton1_Click()
Dim LastRow As Object

Set LastRow = Sheet2.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = ComboBox1.Text
LastRow.Offset(1, 1).Value = ComboBox2.Text
LastRow.Offset(1, 2).Value = TextBox1.Text
LastRow.Offset(1, 3).Value = TextBox2.Text

MsgBox "stored"



End Sub

any comments will help enormously

"Earl Kiosterud" wrote:

> A.G.,
>
> Keep in mind that with a shared workbook, each user has a copy of the
> workbook the way it was when it was opened. It isn't really multiuser.
> Perhaps you should have your macro put the data in a separate workbook,
> which it opens, appends, then closes. Then the other one can do the same.
> Just use the original workbook as a front end. You'll get an error if a
> second user's macro tries to open it while the first one has it, but the
> code can try again later, or whatever.
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "A.G.M ash" <[email protected]> wrote in message
> news:[email protected]...
> >I have set up a custom form in excel for users to select or type data into
> > that then sends the information to a new sheet in a list format at button
> > click e.g
> >
> > Email only recontact size Budget
> > yes no 100 5000
> > no yes 200 6000 #1
> >
> > I then made it a shared workbook with the necessary ranges unprotected.
> > but
> > say for instance i am adding new data into the form thus creating a new
> > record it places it underneath the previous record, but if my partner is
> > also
> > adding new records in the shared workbook at the same time abviously some
> > of
> > his cells and my cells conflict so when saved you have to change the
> > previous
> > persons work thus loosing the records the other user made. is there a
> > macro
> > or an option or somthing to say when saved if data in cells in range #1
> > (example)do not change make saved entry go to unused cells underneath the
> > last record, and i dont want to have to start again because i have several
> > complex cost, losses and estimate calculations running off that data being
> > stored their to be able to remember wich i need to change. Can you help
> >
>
>
>

A.G.,

If you have Access, why not use it? It's already multiuser. You don't have
to write any code, just make a table and a form. Or if you don't want
frills, skip the form and just enter directly into the table, . Multiple
users can open the Access database file. You can't use a table that's a
linked Excel sheet if more than one Access user will have the file open, but
you can easily link from Excel (to the table in the Access database file).
It's Data - Import external data - Import data.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"A.G.M ash" <[email protected]> wrote in message
news:[email protected]...
> earl
> I started thinkin that afta i posted the question, which creates a couple
> more could i do that using a web page form if so how? it doesnt run the
> macros or show the command buttons if done in excel then saved as a .html
> or
> .mht becuase if it did i wouldnt be in this sittuation cos then i could
> upload it and it would be updated instanly! something for microsoft to
> think
> about,
>
> also does it HAVE to open then append then save and close?
>
> DO YOU KNOW WHAT I WOULD NEED TO ADD TO THIS CODE TO SEND IT TO THE
> WORKBOOK
>
> Private Sub CommandButton1_Click()
> Dim LastRow As Object
>
> Set LastRow = Sheet2.Range("a65536").End(xlUp)
>
> LastRow.Offset(1, 0).Value = ComboBox1.Text
> LastRow.Offset(1, 1).Value = ComboBox2.Text
> LastRow.Offset(1, 2).Value = TextBox1.Text
> LastRow.Offset(1, 3).Value = TextBox2.Text
>
> MsgBox "stored"
>
>
>
> End Sub
>
> any comments will help enormously
>
> "Earl Kiosterud" wrote:
>
>> A.G.,
>>
>> Keep in mind that with a shared workbook, each user has a copy of the
>> workbook the way it was when it was opened. It isn't really multiuser.
>> Perhaps you should have your macro put the data in a separate workbook,
>> which it opens, appends, then closes. Then the other one can do the
>> same.
>> Just use the original workbook as a front end. You'll get an error if a
>> second user's macro tries to open it while the first one has it, but the
>> code can try again later, or whatever.
>> --
>> Earl Kiosterud
>> mvpearl omitthisword at verizon period net
>> -------------------------------------------
>>
>> "A.G.M ash" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have set up a custom form in excel for users to select or type data
>> >into
>> > that then sends the information to a new sheet in a list format at
>> > button
>> > click e.g
>> >
>> > Email only recontact size Budget
>> > yes no 100 5000
>> > no yes 200 6000 #1
>> >
>> > I then made it a shared workbook with the necessary ranges unprotected.
>> > but
>> > say for instance i am adding new data into the form thus creating a new
>> > record it places it underneath the previous record, but if my partner
>> > is
>> > also
>> > adding new records in the shared workbook at the same time abviously
>> > some
>> > of
>> > his cells and my cells conflict so when saved you have to change the
>> > previous
>> > persons work thus loosing the records the other user made. is there a
>> > macro
>> > or an option or somthing to say when saved if data in cells in range #1
>> > (example)do not change make saved entry go to unused cells underneath
>> > the
>> > last record, and i dont want to have to start again because i have
>> > several
>> > complex cost, losses and estimate calculations running off that data
>> > being
>> > stored their to be able to remember wich i need to change. Can you help
>> >
>>
>>
>>