Hi All,

Easy question (I hope). I have some code that sets the values of a bunch of
combo boxes, set to fire on Worksheet_Open. Obviously, this causes a
problem, as users will edit the form, save it , and then re-open the form to
find that the data they have input in the combo boxes is gone. I presume the
best way to prevent this is to only run the code that sets the values when
the sheet is clean/virgin/whatever you want to call it. Is there a way to do
that, or is there a better way?

TIA,
Harry

I guess I am having a hard time understanding why it would be a problem if
the user's input in no longer as they left it, when the program is designed
to do something with the data after it is entered. Should they not expect
their data to be moved, calculated or manipulated in some form? Is it a
program design flaw? If not, Just have a pop up text message that tells them
when they open the file that their data will be manipulated and they need not
expect to see it in the form that they enter it.

"Harry F." wrote:

Hi All,

Easy question (I hope). I have some code that sets the values of a bunch of
combo boxes, set to fire on Worksheet_Open. Obviously, this causes a
problem, as users will edit the form, save it , and then re-open the form to
find that the data they have input in the combo boxes is gone. I presume the
best way to prevent this is to only run the code that sets the values when
the sheet is clean/virgin/whatever you want to call it. Is there a way to do
that, or is there a better way?

TIA,
Harry

The code that presently runs on open sets the selectable ranges - I guess I
should have clarified that. For instance:

With Sheet2.ComboBox1
.ListFillRange = "REF!A1:A5"
.ListIndex = 0
End With

The user would then select a value from the box, save the sheet, and perhaps
want to change it later before submitting. However when he/she re-enters the
sheet, it overwrites the selection to reset the list index. I need the list
index to reset to 0 everytime a new sheet is opened, though.

"JLGWhiz" wrote:

I guess I am having a hard time understanding why it would be a problem if
the user's input in no longer as they left it, when the program is designed
to do something with the data after it is entered. Should they not expect
their data to be moved, calculated or manipulated in some form? Is it a
program design flaw? If not, Just have a pop up text message that tells them
when they open the file that their data will be manipulated and they need not
expect to see it in the form that they enter it.

"Harry F." wrote:

Hi All,

Easy question (I hope). I have some code that sets the values of a bunch of
combo boxes, set to fire on Worksheet_Open. Obviously, this causes a
problem, as users will edit the form, save it , and then re-open the form to
find that the data they have input in the combo boxes is gone. I presume the
best way to prevent this is to only run the code that sets the values when
the sheet is clean/virgin/whatever you want to call it. Is there a way to do
that, or is there a better way?

TIA,
Harry

It still comes down to, the user is going to revise their input anyhow. So I
don't see why it matters that their original input is no longer there. Their
second input would override the firsts anyhow, wouldn't it? Or am I all
screwed up in my logic?

"Harry F." wrote:

The code that presently runs on open sets the selectable ranges - I guess I
should have clarified that. For instance:

With Sheet2.ComboBox1
.ListFillRange = "REF!A1:A5"
.ListIndex = 0
End With

The user would then select a value from the box, save the sheet, and perhaps
want to change it later before submitting. However when he/she re-enters the
sheet, it overwrites the selection to reset the list index. I need the list
index to reset to 0 everytime a new sheet is opened, though.

"JLGWhiz" wrote:

I guess I am having a hard time understanding why it would be a problem if
the user's input in no longer as they left it, when the program is designed
to do something with the data after it is entered. Should they not expect
their data to be moved, calculated or manipulated in some form? Is it a
program design flaw? If not, Just have a pop up text message that tells them
when they open the file that their data will be manipulated and they need not
expect to see it in the form that they enter it.

"Harry F." wrote:

Hi All,

Easy question (I hope). I have some code that sets the values of a bunch of
combo boxes, set to fire on Worksheet_Open. Obviously, this causes a
problem, as users will edit the form, save it , and then re-open the form to
find that the data they have input in the combo boxes is gone. I presume the
best way to prevent this is to only run the code that sets the values when
the sheet is clean/virgin/whatever you want to call it. Is there a way to do
that, or is there a better way?

TIA,
Harry

They would not want to change the inputs they have already made, only those
that havent been made.

For instance, if there's 10 combo boxes, and 10 free text cells, the user
would input 5 combo boxes and 8 cells. He would save the sheet, then perhaps
get the remainder of the information he needed to complete the form (fill the
remaining 5 combo boxes and 2 cells). When he opens the sheet, the 5 combo
boxes he's filled out have been overwritten.

"JLGWhiz" wrote:

It still comes down to, the user is going to revise their input anyhow. So I
don't see why it matters that their original input is no longer there. Their
second input would override the firsts anyhow, wouldn't it? Or am I all
screwed up in my logic?

"Harry F." wrote:

The code that presently runs on open sets the selectable ranges - I guess I
should have clarified that. For instance:

With Sheet2.ComboBox1
.ListFillRange = "REF!A1:A5"
.ListIndex = 0
End With

The user would then select a value from the box, save the sheet, and perhaps
want to change it later before submitting. However when he/she re-enters the
sheet, it overwrites the selection to reset the list index. I need the list
index to reset to 0 everytime a new sheet is opened, though.

"JLGWhiz" wrote:

I guess I am having a hard time understanding why it would be a problem if
the user's input in no longer as they left it, when the program is designed
to do something with the data after it is entered. Should they not expect
their data to be moved, calculated or manipulated in some form? Is it a
program design flaw? If not, Just have a pop up text message that tells them
when they open the file that their data will be manipulated and they need not
expect to see it in the form that they enter it.

"Harry F." wrote:

Hi All,

Easy question (I hope). I have some code that sets the values of a bunch of
combo boxes, set to fire on Worksheet_Open. Obviously, this causes a
problem, as users will edit the form, save it , and then re-open the form to
find that the data they have input in the combo boxes is gone. I presume the
best way to prevent this is to only run the code that sets the values when
the sheet is clean/virgin/whatever you want to call it. Is there a way to do
that, or is there a better way?

TIA,
Harry