Dynamic List Data Validation

  • Thread starter Thread starter Bean Counter
  • Start date Start date
B

Bean Counter

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(H11>0,"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!!!!
 
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
 
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!!
 
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 are:
' 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
 
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 said:
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 are:
' 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 said:
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!!
Click to expand...
Click to expand...
 
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 said:
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 are:
' 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 said:
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!!
Click to expand...
Click to expand...
 
Back
Top