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