Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a Variable from Worksheet to Workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a Variable from Worksheet to Workbook
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a Variable from Worksheet to Workbook
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a Variable from Worksheet to Workbook
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 "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a Variable from Worksheet to Workbook
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a Variable from Worksheet to Workbook
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a Variable from Worksheet to Workbook
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing Variable Number of Arguments to a Sub | Excel Discussion (Misc queries) | |||
Passing Variable to LINEST | Excel Worksheet Functions | |||
passing a variable as an argument to a function | Excel Programming | |||
UserForm not passing Boolean variable | Excel Programming | |||
Passing a value to a variable from Userform | Excel Programming |