A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dynamic List Data Validation



 
 
Thread Tools Display Modes
  #1  
Old May 14th, 2010, 07:30 PM posted to microsoft.public.excel.misc
Bean Counter[_3_]
external usenet poster
 
Posts: 16
Default Dynamic List Data Validation

Hi All,

I have created a dependent list where your choice in column B will limit the
choices you have in Column C. I have also added a macro that will erase the
value selected in C if the value of B changes. Now what I would like to do
is have the value in Column B auto populate depending on whether or not there
is a value populated in Column "H". ( the two choices for Column B are
Deposit or Payment, and Column H is the Deposit Column. By default, if there
is no value in the deposit column, there will be a value in the payment
column). I can get the formula in column B to work
(=IF(H110,"Deposits","Payments")), but doing so renders my data validation
(the dependent list in column C), and also my macro useless. Any idea how I
can overcome this problem?? Any help is greatly appreciated!!!!
--
Thanks for all of the help. It is much appreciated!!!!
  #2  
Old May 14th, 2010, 09:54 PM posted to microsoft.public.excel.misc
JLatham
external usenet poster
 
Posts: 1,896
Default Dynamic List Data Validation

Are you using the code I provided, or Roger Govier's fromthe "reset column C"
help request? Not that it matters a whole lot, just so we know what we're
working with here.

As I understand your request now, the starting point for stuff would be an
entry in column H, and a change in H would then drive the contents of B,
which in turn drives the contents of C: when a change is made in B, the entry
in C is now cleared.

Also, as I recall, we're actually not concerned with specific cell "H11",
but with H, B and C on any given row? Correct?

Assuming you're using my code, which I'll modify a bit here for this use,
you could modify that this way, I believe this is what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
'this will clear the contents of the cell
'in column C on the same row when a
'change in the entry in column A of that row
'is made
'assumes row 1 has labels you don't
'want to erase
'
'also, if an entry is made in column H
'then entry in B of same row is affected:
' if H# entry 0 then B = "Deposit"
' otherwise (H# = 0 or empty) then
' B# = "Payment"
'
Application.EnableEvents = False
Select Case Target.Column
Case Is = 1 ' change in column A
'Clear entry in C if entry made in A
If Target.Row 1 Then
Target.Offset(0, 2).ClearContents
End If
Case Is = 8 ' Change in Column H
'make changes in column B on same row
If Target 0 Then
Target.Offset(0, -6) = "Deposit"
Else
Target.Offset(0, -6) = "Payment"
End If
Case Else
'do nothing
End Select
Application.EnableEvents = True
End Sub


"Bean Counter" wrote:

Hi All,

I have created a dependent list where your choice in column B will limit the
choices you have in Column C. I have also added a macro that will erase the
value selected in C if the value of B changes. Now what I would like to do
is have the value in Column B auto populate depending on whether or not there
is a value populated in Column "H". ( the two choices for Column B are
Deposit or Payment, and Column H is the Deposit Column. By default, if there
is no value in the deposit column, there will be a value in the payment
column). I can get the formula in column B to work
(=IF(H110,"Deposits","Payments")), but doing so renders my data validation
(the dependent list in column C), and also my macro useless. Any idea how I
can overcome this problem?? Any help is greatly appreciated!!!!
--
Thanks for all of the help. It is much appreciated!!!!

  #3  
Old May 14th, 2010, 10:59 PM posted to microsoft.public.excel.misc
Bean Counter[_3_]
external usenet poster
 
Posts: 16
Default Dynamic List Data Validation

Hi - yes, indeed!! I did use the code you provided to me the other day.
Thank you again for that. I changed a couple of columns around after
initally posting the question andhad to make a very slight change to the
target column, so I had this code below.

If Target.Column = 2 And Target.Row 1 Then
Target.Offset(0, 1).ClearContents
End If

I copied the new code into the worksheet, and deleted the above code, but it
is still not clearing out the contents in cell "C"... I am sure it is user
error (me), and I will take a little time to work through the logic that you
provided. I am out of time for today, though. If I need more help on
Monday, I will give you a specific shout out since you have provided so much
help on this already!!
--
Thanks for all of the help. It is much appreciated!!!!


"JLatham" wrote:

Are you using the code I provided, or Roger Govier's fromthe "reset column C"
help request? Not that it matters a whole lot, just so we know what we're
working with here.

As I understand your request now, the starting point for stuff would be an
entry in column H, and a change in H would then drive the contents of B,
which in turn drives the contents of C: when a change is made in B, the entry
in C is now cleared.

Also, as I recall, we're actually not concerned with specific cell "H11",
but with H, B and C on any given row? Correct?

Assuming you're using my code, which I'll modify a bit here for this use,
you could modify that this way, I believe this is what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
'this will clear the contents of the cell
'in column C on the same row when a
'change in the entry in column A of that row
'is made
'assumes row 1 has labels you don't
'want to erase
'
'also, if an entry is made in column H
'then entry in B of same row is affected:
' if H# entry 0 then B = "Deposit"
' otherwise (H# = 0 or empty) then
' B# = "Payment"
'
Application.EnableEvents = False
Select Case Target.Column
Case Is = 1 ' change in column A
'Clear entry in C if entry made in A
If Target.Row 1 Then
Target.Offset(0, 2).ClearContents
End If
Case Is = 8 ' Change in Column H
'make changes in column B on same row
If Target 0 Then
Target.Offset(0, -6) = "Deposit"
Else
Target.Offset(0, -6) = "Payment"
End If
Case Else
'do nothing
End Select
Application.EnableEvents = True
End Sub


"Bean Counter" wrote:

Hi All,

I have created a dependent list where your choice in column B will limit the
choices you have in Column C. I have also added a macro that will erase the
value selected in C if the value of B changes. Now what I would like to do
is have the value in Column B auto populate depending on whether or not there
is a value populated in Column "H". ( the two choices for Column B are
Deposit or Payment, and Column H is the Deposit Column. By default, if there
is no value in the deposit column, there will be a value in the payment
column). I can get the formula in column B to work
(=IF(H110,"Deposits","Payments")), but doing so renders my data validation
(the dependent list in column C), and also my macro useless. Any idea how I
can overcome this problem?? Any help is greatly appreciated!!!!
--
Thanks for all of the help. It is much appreciated!!!!

  #4  
Old May 15th, 2010, 09:07 PM posted to microsoft.public.excel.misc
JLatham
external usenet poster
 
Posts: 1,896
Default Dynamic List Data Validation

Maybe try it this way? Remember that this code goes on into the code module
for the sheet this is all happening on: Right-click the sheet name and choose
[View Code] to get to the right place. Replace old _Change() event code with
this code.

Private Sub Worksheet_Change(ByVal Target As Range)
'this will clear the contents of the cell
'in column C on the same row when a
'change in the entry in column A of that row
'is made
'assumes row 1 has labels you don't
'want to erase
'
'also, if an entry is made in column H
'then entry in B of same row is affected:
' if H# entry 0 then B = "Deposit"
' otherwise (H# = 0 or empty) then
' B# = "Payment"
'
Application.EnableEvents = False
Select Case Target.Column
Case Is = 2 ' change in column B
'Clear entry in C if entry made in B
If Target.Row 1 Then
Target.Offset(0, 1).ClearContents
End If
Case Is = 8 ' Change in Column H
'make changes in column B on same row
'only legitimate entries in B a
' Deposit or Payment
'remember that VBA is case sensitive
If Target 0 Then
'if it was already "Deposit" don't do anything
If Target "Deposit" Then
'wasn't Deposit, change it and clear C
Target.Offset(0, -6) = "Deposit"
Target.Offset(0, 5).ClearContents
End If
Else
'if it was already "Payment" don't do anything
If Target "Payment" Then
'wasn't Payment, change it and clear C
Target.Offset(0, -6) = "Payment"
Target.Offset(0, 5).ClearContents
End If
End If
Case Else
'do nothing
End Select
Application.EnableEvents = True
End Sub


"Bean Counter" wrote:

Hi - yes, indeed!! I did use the code you provided to me the other day.
Thank you again for that. I changed a couple of columns around after
initally posting the question andhad to make a very slight change to the
target column, so I had this code below.

If Target.Column = 2 And Target.Row 1 Then
Target.Offset(0, 1).ClearContents
End If

I copied the new code into the worksheet, and deleted the above code, but it
is still not clearing out the contents in cell "C"... I am sure it is user
error (me), and I will take a little time to work through the logic that you
provided. I am out of time for today, though. If I need more help on
Monday, I will give you a specific shout out since you have provided so much
help on this already!!
--
Thanks for all of the help. It is much appreciated!!!!


"JLatham" wrote:

Are you using the code I provided, or Roger Govier's fromthe "reset column C"
help request? Not that it matters a whole lot, just so we know what we're
working with here.

As I understand your request now, the starting point for stuff would be an
entry in column H, and a change in H would then drive the contents of B,
which in turn drives the contents of C: when a change is made in B, the entry
in C is now cleared.

Also, as I recall, we're actually not concerned with specific cell "H11",
but with H, B and C on any given row? Correct?

Assuming you're using my code, which I'll modify a bit here for this use,
you could modify that this way, I believe this is what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
'this will clear the contents of the cell
'in column C on the same row when a
'change in the entry in column A of that row
'is made
'assumes row 1 has labels you don't
'want to erase
'
'also, if an entry is made in column H
'then entry in B of same row is affected:
' if H# entry 0 then B = "Deposit"
' otherwise (H# = 0 or empty) then
' B# = "Payment"
'
Application.EnableEvents = False
Select Case Target.Column
Case Is = 1 ' change in column A
'Clear entry in C if entry made in A
If Target.Row 1 Then
Target.Offset(0, 2).ClearContents
End If
Case Is = 8 ' Change in Column H
'make changes in column B on same row
If Target 0 Then
Target.Offset(0, -6) = "Deposit"
Else
Target.Offset(0, -6) = "Payment"
End If
Case Else
'do nothing
End Select
Application.EnableEvents = True
End Sub


"Bean Counter" wrote:

Hi All,

I have created a dependent list where your choice in column B will limit the
choices you have in Column C. I have also added a macro that will erase the
value selected in C if the value of B changes. Now what I would like to do
is have the value in Column B auto populate depending on whether or not there
is a value populated in Column "H". ( the two choices for Column B are
Deposit or Payment, and Column H is the Deposit Column. By default, if there
is no value in the deposit column, there will be a value in the payment
column). I can get the formula in column B to work
(=IF(H110,"Deposits","Payments")), but doing so renders my data validation
(the dependent list in column C), and also my macro useless. Any idea how I
can overcome this problem?? Any help is greatly appreciated!!!!
--
Thanks for all of the help. It is much appreciated!!!!

  #5  
Old May 17th, 2010, 03:24 PM posted to microsoft.public.excel.misc
Bean Counter[_3_]
external usenet poster
 
Posts: 16
Default Dynamic List Data Validation

Hi JLatham,

Thanks again for the help. I have added the second version of the code to
the worksheet. The code works, but now it has created a new hurtle.
Originally, column "B" was set up as the first list in a dependent list
scenario...depending on what was selected in column B, then the choices for
column C were given... With the new code, the dependent list is no longer
operating... any ideas on how to fix this? Should I eliminate the data
validation altogether and go with a different scenario?
--
Thanks for all of the help. It is much appreciated!!!!


"JLatham" wrote:

Maybe try it this way? Remember that this code goes on into the code module
for the sheet this is all happening on: Right-click the sheet name and choose
[View Code] to get to the right place. Replace old _Change() event code with
this code.

Private Sub Worksheet_Change(ByVal Target As Range)
'this will clear the contents of the cell
'in column C on the same row when a
'change in the entry in column A of that row
'is made
'assumes row 1 has labels you don't
'want to erase
'
'also, if an entry is made in column H
'then entry in B of same row is affected:
' if H# entry 0 then B = "Deposit"
' otherwise (H# = 0 or empty) then
' B# = "Payment"
'
Application.EnableEvents = False
Select Case Target.Column
Case Is = 2 ' change in column B
'Clear entry in C if entry made in B
If Target.Row 1 Then
Target.Offset(0, 1).ClearContents
End If
Case Is = 8 ' Change in Column H
'make changes in column B on same row
'only legitimate entries in B a
' Deposit or Payment
'remember that VBA is case sensitive
If Target 0 Then
'if it was already "Deposit" don't do anything
If Target "Deposit" Then
'wasn't Deposit, change it and clear C
Target.Offset(0, -6) = "Deposit"
Target.Offset(0, 5).ClearContents
End If
Else
'if it was already "Payment" don't do anything
If Target "Payment" Then
'wasn't Payment, change it and clear C
Target.Offset(0, -6) = "Payment"
Target.Offset(0, 5).ClearContents
End If
End If
Case Else
'do nothing
End Select
Application.EnableEvents = True
End Sub


"Bean Counter" wrote:

Hi - yes, indeed!! I did use the code you provided to me the other day.
Thank you again for that. I changed a couple of columns around after
initally posting the question andhad to make a very slight change to the
target column, so I had this code below.

If Target.Column = 2 And Target.Row 1 Then
Target.Offset(0, 1).ClearContents
End If

I copied the new code into the worksheet, and deleted the above code, but it
is still not clearing out the contents in cell "C"... I am sure it is user
error (me), and I will take a little time to work through the logic that you
provided. I am out of time for today, though. If I need more help on
Monday, I will give you a specific shout out since you have provided so much
help on this already!!
--
Thanks for all of the help. It is much appreciated!!!!


"JLatham" wrote:

Are you using the code I provided, or Roger Govier's fromthe "reset column C"
help request? Not that it matters a whole lot, just so we know what we're
working with here.

As I understand your request now, the starting point for stuff would be an
entry in column H, and a change in H would then drive the contents of B,
which in turn drives the contents of C: when a change is made in B, the entry
in C is now cleared.

Also, as I recall, we're actually not concerned with specific cell "H11",
but with H, B and C on any given row? Correct?

Assuming you're using my code, which I'll modify a bit here for this use,
you could modify that this way, I believe this is what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
'this will clear the contents of the cell
'in column C on the same row when a
'change in the entry in column A of that row
'is made
'assumes row 1 has labels you don't
'want to erase
'
'also, if an entry is made in column H
'then entry in B of same row is affected:
' if H# entry 0 then B = "Deposit"
' otherwise (H# = 0 or empty) then
' B# = "Payment"
'
Application.EnableEvents = False
Select Case Target.Column
Case Is = 1 ' change in column A
'Clear entry in C if entry made in A
If Target.Row 1 Then
Target.Offset(0, 2).ClearContents
End If
Case Is = 8 ' Change in Column H
'make changes in column B on same row
If Target 0 Then
Target.Offset(0, -6) = "Deposit"
Else
Target.Offset(0, -6) = "Payment"
End If
Case Else
'do nothing
End Select
Application.EnableEvents = True
End Sub


"Bean Counter" wrote:

Hi All,

I have created a dependent list where your choice in column B will limit the
choices you have in Column C. I have also added a macro that will erase the
value selected in C if the value of B changes. Now what I would like to do
is have the value in Column B auto populate depending on whether or not there
is a value populated in Column "H". ( the two choices for Column B are
Deposit or Payment, and Column H is the Deposit Column. By default, if there
is no value in the deposit column, there will be a value in the payment
column). I can get the formula in column B to work
(=IF(H110,"Deposits","Payments")), but doing so renders my data validation
(the dependent list in column C), and also my macro useless. Any idea how I
can overcome this problem?? Any help is greatly appreciated!!!!
--
Thanks for all of the help. It is much appreciated!!!!

  #6  
Old May 17th, 2010, 03:35 PM posted to microsoft.public.excel.misc
Bean Counter[_3_]
external usenet poster
 
Posts: 16
Default Dynamic List Data Validation

NEVERMIND the last post... I had used "deposits" and "payments" in my list
scenario, but the code was only looking at "deposit" or "payment", no "s" on
the end. I have fixed it, and it now works!!!!
--
Thanks for all of the help. It is much appreciated!!!!


"JLatham" wrote:

Maybe try it this way? Remember that this code goes on into the code module
for the sheet this is all happening on: Right-click the sheet name and choose
[View Code] to get to the right place. Replace old _Change() event code with
this code.

Private Sub Worksheet_Change(ByVal Target As Range)
'this will clear the contents of the cell
'in column C on the same row when a
'change in the entry in column A of that row
'is made
'assumes row 1 has labels you don't
'want to erase
'
'also, if an entry is made in column H
'then entry in B of same row is affected:
' if H# entry 0 then B = "Deposit"
' otherwise (H# = 0 or empty) then
' B# = "Payment"
'
Application.EnableEvents = False
Select Case Target.Column
Case Is = 2 ' change in column B
'Clear entry in C if entry made in B
If Target.Row 1 Then
Target.Offset(0, 1).ClearContents
End If
Case Is = 8 ' Change in Column H
'make changes in column B on same row
'only legitimate entries in B a
' Deposit or Payment
'remember that VBA is case sensitive
If Target 0 Then
'if it was already "Deposit" don't do anything
If Target "Deposit" Then
'wasn't Deposit, change it and clear C
Target.Offset(0, -6) = "Deposit"
Target.Offset(0, 5).ClearContents
End If
Else
'if it was already "Payment" don't do anything
If Target "Payment" Then
'wasn't Payment, change it and clear C
Target.Offset(0, -6) = "Payment"
Target.Offset(0, 5).ClearContents
End If
End If
Case Else
'do nothing
End Select
Application.EnableEvents = True
End Sub


"Bean Counter" wrote:

Hi - yes, indeed!! I did use the code you provided to me the other day.
Thank you again for that. I changed a couple of columns around after
initally posting the question andhad to make a very slight change to the
target column, so I had this code below.

If Target.Column = 2 And Target.Row 1 Then
Target.Offset(0, 1).ClearContents
End If

I copied the new code into the worksheet, and deleted the above code, but it
is still not clearing out the contents in cell "C"... I am sure it is user
error (me), and I will take a little time to work through the logic that you
provided. I am out of time for today, though. If I need more help on
Monday, I will give you a specific shout out since you have provided so much
help on this already!!
--
Thanks for all of the help. It is much appreciated!!!!


"JLatham" wrote:

Are you using the code I provided, or Roger Govier's fromthe "reset column C"
help request? Not that it matters a whole lot, just so we know what we're
working with here.

As I understand your request now, the starting point for stuff would be an
entry in column H, and a change in H would then drive the contents of B,
which in turn drives the contents of C: when a change is made in B, the entry
in C is now cleared.

Also, as I recall, we're actually not concerned with specific cell "H11",
but with H, B and C on any given row? Correct?

Assuming you're using my code, which I'll modify a bit here for this use,
you could modify that this way, I believe this is what you want:

Private Sub Worksheet_Change(ByVal Target As Range)
'this will clear the contents of the cell
'in column C on the same row when a
'change in the entry in column A of that row
'is made
'assumes row 1 has labels you don't
'want to erase
'
'also, if an entry is made in column H
'then entry in B of same row is affected:
' if H# entry 0 then B = "Deposit"
' otherwise (H# = 0 or empty) then
' B# = "Payment"
'
Application.EnableEvents = False
Select Case Target.Column
Case Is = 1 ' change in column A
'Clear entry in C if entry made in A
If Target.Row 1 Then
Target.Offset(0, 2).ClearContents
End If
Case Is = 8 ' Change in Column H
'make changes in column B on same row
If Target 0 Then
Target.Offset(0, -6) = "Deposit"
Else
Target.Offset(0, -6) = "Payment"
End If
Case Else
'do nothing
End Select
Application.EnableEvents = True
End Sub


"Bean Counter" wrote:

Hi All,

I have created a dependent list where your choice in column B will limit the
choices you have in Column C. I have also added a macro that will erase the
value selected in C if the value of B changes. Now what I would like to do
is have the value in Column B auto populate depending on whether or not there
is a value populated in Column "H". ( the two choices for Column B are
Deposit or Payment, and Column H is the Deposit Column. By default, if there
is no value in the deposit column, there will be a value in the payment
column). I can get the formula in column B to work
(=IF(H110,"Deposits","Payments")), but doing so renders my data validation
(the dependent list in column C), and also my macro useless. Any idea how I
can overcome this problem?? Any help is greatly appreciated!!!!
--
Thanks for all of the help. It is much appreciated!!!!

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:48 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.