Log In
Register
Home
Active Member
November 9, 2022
Offline
Hi,
While using the codes for multiple select drop down list, i got some problems the first one is: when i use codes for another thing in: (Private Sub Worksheet_Change(ByVal Target As Range))
It will not work because of this:
"If more than 1 cell is being changed"
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
so i deleted these codes and when i need more than one cell with muliple-select i repeat the whole codes again but it works. And only this way other codes could work.
The other problem that i could not fix is: for example i choose data1, data2, data3 from the list and i want to delete them all but i do not want to select one after the other to delete them. so i want easily to press delete, but when i do that, codes from VBA do not work anymore and i have to close the excel file and reopen it to work again.
thanks for your time
regards
Tammy
p.s.: attached you will find the codes in text-file
See Answer
October 5, 2010
Offline
Hi Tammy,
I'm not sure what you mean by i got some problems the first one is: when i use codes for another thing
Can you provide the file you are using and explain what you are trying to do?
The purpose of this code
If Target.Count > 1 Then Exit Sub
Prevent the code running of more than 1 cell is being changed at the same time. If you are using a drop down data validation list, then you should only ever be changing 1 cell at any time, so there's no need to remove that line.
If Target.Value = "" Then Exit Sub
If the cell value has been deleted, then exit the code, there's no need to run anything. If you have deleted this line, that is why you are finding that the code doesn't run when you delete the cell contents.
If what you are trying to do is run the code for 2 different data validation lists then you need to change this line to account for the 2nd dv range
If Not Intersect(Target, ActiveSheet.Range("DV_Range")) Is Nothing Then
If you can supply your file and explain exactly what it is you want to do, I'll see if I can come up with a solution.
Regards
Phil
Active Member
November 9, 2022
Offline
Hi Phil,
I'm not allowed to send my original file but I attached an example file for the problem
in this example, i used the codes:
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
and every thing works fine but the next codes:
If Range("C6") = "yes" Then
[9:11].EntireRow.Hidden = False
Else
[9:11].EntireRow.Hidden = True
End If
are not working
regards,
Tamara
October 5, 2010
Offline
Hi Tamara,
You need to add a 'trap' to check for the cell that is being changed. As you want to check the value in C6 when it is changed, you need to add code to check for a change to that cell, like this
If Not Intersect(Target, ActiveSheet.Range("C6")) Is Nothing Then
If Range("C6") = "yes" Then
[9:11].EntireRow.Hidden = False
Else
[9:11].EntireRow.Hidden = True
End If
End If
See attached file.
Regards
Phil
Answers Post
Active Member
November 9, 2022
Offline
Thanks alot that was a big help.
But if i want to use data1, data2... in cell C4 to determine hide or unhide rows then an error will occur.
please see the attaced file
regards
Tammy
October 5, 2010
Offline
Hi Tamara,
Running the code in the last file you provided does not generate any errors.
Could you please describe in detail exactly what it is you are trying to do. Maybe provide a file or an image showing an example of the final result you want.
Since your initial request, you've described a number of different issues, which i have resolved, but I'm still not clear what it is you are actually trying to do.
Regards
Phil
Active Member
November 9, 2022
Offline
I'm sorry for not explaining myself currectly.
For example when I choose data1 in C4 then the row with data1 (B13) appears (but C4 reset itself and i can't see data1 in C4) and when i choose data2 the row with data2 (B14) appears but the row with data1 (B13) disappears
I want to see both data1 and data2, only the last one i chose will appear because C4 keep resetting itself
regards,
Tamara
October 5, 2010
Offline
OK I think I know what you mean. The code you've entered is interfering with my code that checks the selection from the validation list.
You just need to move this after my code so the DV changes are dealt with correctly, then your code can run doing what you want.
If you stepped through the code you could see that this was happening. Please read these posts which discuss debugging VBA code:
More Tips for Debugging VBA Code
regards
Phil
Trusted Members
October 17, 2018
Offline
Hi. sorry to butt in like this but I made some minor changes to pgt-example-3 to avoid the error in C4 when there is nothing to split and als when the target is cleared.
1 Guest(s)
