Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter information in one and lock the other, OR vise versa
Hello,
I am an intermediate user of excel, however my knowledge of VBA is a little rusty. I am currently stumped on this problem for a worksheet I working on: Worksheet is "RateSheet" and already unlocks and locks certain ranges on worksheet.change event Range "A" is D12:D1000 Range "B" is E12:E1000 Validation for both ranges is positive currency values I want to compare a cell from A to the corresponding cell in B and allow information to only be entered in one OR the other cell. Therefore if the user enters a number in A, the user will not be able to enter any values in B, and ultimately B will be greyed out as well. The same goes for B, a value entered will have the same lockout results in A. If A had information and B was locked out, deleting or clearing A would unlock B. Hope this makes sense... I've searched but have not found exactly what I am looking for... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter information in one and lock the other, OR vise versa
I am not sure if this does what you want, but it does make
things interesting. Place the code in the module behind the sheet... '---------------------------- Private Sub Worksheet_Change(ByVal Target As Range) 'Jim Cone - San Francisco, USA - December 2006 'Only one cell in the row intersecting Columns D & E can have a value. On Error GoTo ErrHandler Dim rngIntersect As Excel.Range Dim blnCol As Boolean Set rngIntersect = Application.Intersect(Target, Me.Range("D12:E1000")) If Not rngIntersect Is Nothing Then Application.EnableEvents = False If rngIntersect.Count = 1 Then 'Col D returns 0, Col E returns -1 blnCol = (rngIntersect.Column = 5) If Len(rngIntersect) Then 'rngIntersect(1, 2) or rngIntersect(1, 0) rngIntersect(1, 2 + (2 * CLng(blnCol))).ClearContents End If Else 'More than one cell changed in Col D or E so undo changes... Application.Undo MsgBox "Change only one cell at a time in columns E and F ", _ vbExclamation, "Hope This Makes Sense" End If End If ErrHandler: Application.EnableEvents = True Set rngIntersect = Nothing End Sub --------------- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware wrote in message Hello, I am an intermediate user of excel, however my knowledge of VBA is a little rusty. I am currently stumped on this problem for a worksheet I working on: Worksheet is "RateSheet" and already unlocks and locks certain ranges on worksheet.change event Range "A" is D12:D1000 Range "B" is E12:E1000 Validation for both ranges is positive currency values I want to compare a cell from A to the corresponding cell in B and allow information to only be entered in one OR the other cell. Therefore if the user enters a number in A, the user will not be able to enter any values in B, and ultimately B will be greyed out as well. The same goes for B, a value entered will have the same lockout results in A. If A had information and B was locked out, deleting or clearing A would unlock B. Hope this makes sense... I've searched but have not found exactly what I am looking for... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter information in one and lock the other, OR vise versa
Jim,
First off thank you, I see exactly what your code does and never thought of this approach. I tested it and it works just fine! However, things get a little complicated now!! While I think I can do this kind of thing on my own, I end up running into problems. Here is my first problem. And check this picture for reference! http://img299.imageshack.us/img299/9003/handlingbk0.gif This sheet is a rate sheet for the movement of cargo. Now depending on whats being shipped and to where, the rates and how they are calculated change. Shipments reside in each row, and on any given shipment only one rate can be entered and charged. Rates are charged as follows: A minimum (min) or per kilogram (which ever is greater); A Flat fee per shipment; A charge per piece handled. The calculations are set, no problems. Now I need to incorporate your logic and code into this. I was going to also set the background of the "disabled" cells to black so the user knows no information is to be entered. When the user deletes information in the used cell, they should return to clear. The second problem is that I have 13 sets of charges across my sheet that follow this same exact schema. I wanted to know if the range used can somehow be selected based on which range my user is in. For example if the user clicks in the "handling" range, then that range is passed through the function. I suppose I could use a "case" selection that if true set a range variable to the current range. I will continue to work on this but any help is appreciated. I also ran into the problem of not having my vba execute properly, and me wasting hours last night. Turns out I had to restart my computer and everything worked as it should after that. Buggy I suppose. Jim Cone wrote: I am not sure if this does what you want, but it does make things interesting. Place the code in the module behind the sheet... '---------------------------- Private Sub Worksheet_Change(ByVal Target As Range) 'Jim Cone - San Francisco, USA - December 2006 'Only one cell in the row intersecting Columns D & E can have a value. On Error GoTo ErrHandler Dim rngIntersect As Excel.Range Dim blnCol As Boolean Set rngIntersect = Application.Intersect(Target, Me.Range("D12:E1000")) If Not rngIntersect Is Nothing Then Application.EnableEvents = False If rngIntersect.Count = 1 Then 'Col D returns 0, Col E returns -1 blnCol = (rngIntersect.Column = 5) If Len(rngIntersect) Then 'rngIntersect(1, 2) or rngIntersect(1, 0) rngIntersect(1, 2 + (2 * CLng(blnCol))).ClearContents End If Else 'More than one cell changed in Col D or E so undo changes... Application.Undo MsgBox "Change only one cell at a time in columns E and F ", _ vbExclamation, "Hope This Makes Sense" End If End If ErrHandler: Application.EnableEvents = True Set rngIntersect = Nothing End Sub --------------- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware wrote in message Hello, I am an intermediate user of excel, however my knowledge of VBA is a little rusty. I am currently stumped on this problem for a worksheet I working on: Worksheet is "RateSheet" and already unlocks and locks certain ranges on worksheet.change event Range "A" is D12:D1000 Range "B" is E12:E1000 Validation for both ranges is positive currency values I want to compare a cell from A to the corresponding cell in B and allow information to only be entered in one OR the other cell. Therefore if the user enters a number in A, the user will not be able to enter any values in B, and ultimately B will be greyed out as well. The same goes for B, a value entered will have the same lockout results in A. If A had information and B was locked out, deleting or clearing A would unlock B. Hope this makes sense... I've searched but have not found exactly what I am looking for... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter information in one and lock the other, OR vise versa
If you interrupt the code before it completes the EnableEvents statement
will not be reset to True. That prevents the code from running again when a change is made. While developing your code, you should create a separate one line piece of code and run it as necessary to EnableEvents. Note that the error handling code always runs. There is no Exit sub line before the error handling. That guarantees that if the code completes or an error occurs that EnableEvents gets reset to True. The problem appears only if something (you) stops the code midstream. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware wrote in message Jim, First off thank you, I see exactly what your code does and never thought of this approach. I tested it and it works just fine! However, things get a little complicated now!! While I think I can do this kind of thing on my own, I end up running into problems. Here is my first problem. And check this picture for reference! http://img299.imageshack.us/img299/9003/handlingbk0.gif This sheet is a rate sheet for the movement of cargo. Now depending on whats being shipped and to where, the rates and how they are calculated change. Shipments reside in each row, and on any given shipment only one rate can be entered and charged. Rates are charged as follows: A minimum (min) or per kilogram (which ever is greater); A Flat fee per shipment; A charge per piece handled. The calculations are set, no problems. Now I need to incorporate your logic and code into this. I was going to also set the background of the "disabled" cells to black so the user knows no information is to be entered. When the user deletes information in the used cell, they should return to clear. The second problem is that I have 13 sets of charges across my sheet that follow this same exact schema. I wanted to know if the range used can somehow be selected based on which range my user is in. For example if the user clicks in the "handling" range, then that range is passed through the function. I suppose I could use a "case" selection that if true set a range variable to the current range. I will continue to work on this but any help is appreciated. I also ran into the problem of not having my vba execute properly, and me wasting hours last night. Turns out I had to restart my computer and everything worked as it should after that. Buggy I suppose. Jim Cone wrote: I am not sure if this does what you want, but it does make things interesting. Place the code in the module behind the sheet... '---------------------------- Private Sub Worksheet_Change(ByVal Target As Range) 'Jim Cone - San Francisco, USA - December 2006 'Only one cell in the row intersecting Columns D & E can have a value. On Error GoTo ErrHandler Dim rngIntersect As Excel.Range Dim blnCol As Boolean Set rngIntersect = Application.Intersect(Target, Me.Range("D12:E1000")) If Not rngIntersect Is Nothing Then Application.EnableEvents = False If rngIntersect.Count = 1 Then 'Col D returns 0, Col E returns -1 blnCol = (rngIntersect.Column = 5) If Len(rngIntersect) Then 'rngIntersect(1, 2) or rngIntersect(1, 0) rngIntersect(1, 2 + (2 * CLng(blnCol))).ClearContents End If Else 'More than one cell changed in Col D or E so undo changes... Application.Undo MsgBox "Change only one cell at a time in columns E and F ", _ vbExclamation, "Hope This Makes Sense" End If End If ErrHandler: Application.EnableEvents = True Set rngIntersect = Nothing End Sub --------------- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware wrote in message Hello, I am an intermediate user of excel, however my knowledge of VBA is a little rusty. I am currently stumped on this problem for a worksheet I working on: Worksheet is "RateSheet" and already unlocks and locks certain ranges on worksheet.change event Range "A" is D12:D1000 Range "B" is E12:E1000 Validation for both ranges is positive currency values I want to compare a cell from A to the corresponding cell in B and allow information to only be entered in one OR the other cell. Therefore if the user enters a number in A, the user will not be able to enter any values in B, and ultimately B will be greyed out as well. The same goes for B, a value entered will have the same lockout results in A. If A had information and B was locked out, deleting or clearing A would unlock B. Hope this makes sense... I've searched but have not found exactly what I am looking for... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lock Cell After Enter Command | Excel Discussion (Misc queries) | |||
Outlook Calendar Reminders link with Excel, vise versa | Excel Discussion (Misc queries) | |||
Lock information | Setting up and Configuration of Excel | |||
Office 2000/Office 2003 Excel not printing landscape vise versa | Setting up and Configuration of Excel | |||
Change rows to columns and vise versa | New Users to Excel |