Updating the list will probably trigger the click event, regardless of how
you do it. If you do it with code, however, you can check the status of
your boolean variable before you update (in the update code), and then after
the update, reset it to what it was.

Sub OpenBook()
dim bHold as Boolean
dim rng as Range
bHold = blnchgseg
Workbooks.Open "C:\MyDocuments\"book1.xls"
With Workbooks("Book1.xls).Worksheets("sheet1")
set rng = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End With
Thisworkbook.Sheet1.Combobox1.List = rng.Value
blnchgseg = bHold
End Sub

as an example. And you shouldn't get secondary effects with other
comboboxes.

regards,
Tom Ogilvy




Susan Lammi wrote in message
...
Sorry for being unclear, the comboboxes are getting their list from
another
workbook. This needs to be somewhat dynamic (if there is such a thing),
as
long as the list is refreshed each time the workbook is opened that is
enough. I think I will try filling the comboboxes via code as you
suggest.

thanks again

Sue
"Tom Ogilvy" wrote in message
...
If i do a full recalc, I get a click event (or change event), so I would
say
a recalculate does cause the the list of the combobox to be refreshed or
at
least it could. Perhaps you need to set your list with code rather than
using the listfillrange. my listfillrange was on the same sheet, so
just
being on another sheet or in another workbook (you said both) wouldn't
necessarily be the sole cause.

Regards,
Tom Ogilvy

Susan Lammi wrote in message
...
You're right about my old code... It wouldn't have worked anyway but

I think the comboboxes are being refreshed (excel sees it as changed)
because they get their values from another workbook

Does calculation automatically refresh links to other workbooks???

If calculation is automatic does it occur when a workbook is
deactivated
(ie
another workbook is opened)???

"Tom Ogilvy" wrote in message
...
Use the click event for the combobox.

although I don't see how opening another workbook would cause the
change
event to fire unless you have run aground of some bug.

In any event, the code you posted fires the Worksheet_Change event
from
the
combobox_change event, so if the combobox change event is firing,
that
wouldn't have prevented anything.


--
Regards,
Tom Ogilvy


Susan Lammi wrote in message
...
I guess I spoke too soon.....
Although the variable is now working properly a new issue has
surfaced...
The change event for the comboboxes are firing whenever a new
workbook
is
opened. causing the variable to be set to true incorrectly....

If I remember correctly this is how I got into forcing the
worksheet_Change
event to determine if it was really a change......

I'd rather know why the combobox change events fire.

The application is opening these workbooks via VBACode.
"Susan Lammi" wrote in message
...
Ok, so I'm an idiot.....
I cleaned up my code, you're right setting the variable in the
combobox_Change event is simpler.... I guess I got caught up in
using
the
code I wrote initially.....

In any case it is working as you said.....
Sorry for being dense.....

Thanks for your patience....

Susan

"Tom Ogilvy" wrote in message
...
you must have a unique copy of Excel. It works fine for
eveyone
else.
As
an
example.

In a sheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As
Excel.Range)
myvar = Target.Address
End Sub


in a general module

Public myvar As String

Sub PrintCell()
MsgBox myvar
End Sub

Everytime I ran PrintCell, is told me the last cell selected
on
sheet1.

A Static variable is only visible to the procedure in which it
is
called.
A
public variable in a general module is static by definition -
it
exists
as
long as the workbook is open. Not to say you can't cause it
to
lose
its
value, but in a normal production environment, it shouldn't be
a
problem.

Also, it would seem simpler to change you Change event to just
set
the
variable:

Private Sub ComboBox1_Change()
blnchgseg = True
if Me.Combobox1.value = "" then Me.ComboBox1 = Range("aY7")
End Sub

Isnull doesn't test for an empty string - so your test would
never
be
passed.

testing from the immediate window:

? isnull("")
False
sheet3.combobox1.Listindex = -1
? sheet3.ComboBox1.Value

? isnull(sheet3.ComboBox1)
False




Regards,
Tom Ogilvy



Susan Lammi wrote in message
...
Been there....

If I declare a Public variable in a general module it does
not
retain
the
value assigned by the code in the worksheet..... I think I
need
a
static
variable or some other solution.....

Any suggestions are welcome

"Tom Ogilvy" wrote in message
...
At the top of a general module, put your declaration

Public blnchgseg As Boolean


Remove any other declarations.

There is a workbook_deactivate event. You might need to
use
that
as
part
of
your solution.

Regards,
Tom Ogilvy


Susan Lammi wrote in message
...
I have a Sheet with several comboboxes (with linked
cells).
When
the
user
leaves the workbook I need to display a message if they
have
made
changes
to
any of the combo boxes. Each has a change event like
below......
The first line just fills in a default if it is null
The second line forces the worksheet change event which
determines
if
the
change is for one of the designated cells and sets the
value
of
blnchgseg
to
True.

Now I need to ..... If blnchgseg = True Then SegMsg
(display
the
message)..... but the workbook does not know about
blnchgseg
and
the
worksheet_deactivate does not fire when the user move to
another
workbook.

Can I define a static variable so both worksheet and
workbook
know
about
it
?
Have I gone 'round the bend on this one????

Private Sub ComboBox1_Change()
If IsNull(Me.ComboBox1) Then Me.ComboBox1 =
Range("aY7")
Worksheet_Change Range(Me.ComboBox1.LinkedCell)
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Static blnchgseg As Boolean
For intx = 1 To Range("trsegsmapped").Rows.Count
If Target.Address =
Range("trsegsmapped")(intx).Address
Then
blnchgseg = True
Exit For
End If
Next intx
End Sub