Passing a Variable from Worksheet to Workbook

  • Thread starter Thread starter Susan Lammi
  • Start date Start date
S

Susan Lammi

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
 
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
 
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.
 
This would be alot easier if you had all the info....

The comboboxes are getting their values from another worksheet.... I think
this has something to do which why the combobox_Change events are firing....

Should I start a new thread on this subject????

Thanks
Sue
 
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.
 
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
 
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
 
Back
Top