Asp Forum - Enter information in one and lock the other, OR vise versa

heyitsthechad

12/12/2006 3:53:00 PM

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 Answers

Jim Cone

12/13/2006 1:51:00 AM

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/primiti...

<heyitsthechad@gmail.com>
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...

heyitsthechad

12/13/2006 4:12:00 PM

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/handl...
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/primiti...
>
>
>
> <heyitsthechad@gmail.com>
> 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...

Jim Cone

12/13/2006 5:39:00 PM

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/primiti...

<heyitsthechad@gmail.com>
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/handl...
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/primiti...
>
>
>
> <heyitsthechad@gmail.com>
> 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...

microsoft.public.excel.programming

Enter information in one and lock the other, OR vise versa

heyitsthechad

Jim Cone

heyitsthechad

Jim Cone

x Login to ForumsZone