Dynamic List Data Validation

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!!!!
 
J

JLatham

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
 
B

Bean Counter

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!!
 
J

JLatham

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
 
B

Bean Counter

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!!
 
B

Bean Counter

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!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top