Excel version being used: Excel 2000
Using Microsoft XP
I have been attempting to provide an updating drop-down list in my spreadsheet with various methods but have failed. I have a method that works but requires a ton of formulas in every possible cell in the required columns&this adds up to an extremely large and unacceptable excel file.
The spreadsheet attached is a clipping of my master spreadsheet (reduced the clutter to make it simple for illustration on what is trying to be done. The spreadsheet tracks the work progress. I would like to add the ability to assign drawing numbers that are assigned per discipline and as you use a number it is remove from the drop list. This would also help to eliminate the possibility of duplicates being assigned. I have attempted VBA and managed for a short time to get the concept to work but with many undesirable side effects and errors. One effect is if a user accidentally chooses the wrong number it is already to late because the VBA already removed it from the list.
I example attached spreadsheet (the example does not contain any vba) is similar to my master xls, in that majority of the rows are hidden. As a user needs a row they click a button to add a row (a button that unhides a row and does various other things (copies formulas down a row, changes the print area, etc.) &. This keeps my number of formulas down, thus keeps the xls file size down. Some projects may only need 100 rows and others may need 7,000.
Basics:
On PDSR sheet tab:
1. User chooses a discipline (column C) in the row they are filling out.
2. Upon selecting a discipline, this basically enables the cell in column G which has a validation set to: =INDIRECT(C2)
So, on DWGList sheet tab, I have the columns assigned with the name field the same name as the discipline.
This gives me my different drop lists depending on the discipline chosen in that row.
Problem:
Trying to make the drop list dynamic and have the already used drawing numbers unavailable in the drop list. This would show the user the numbers that are left and gives the user only the chose in the drop list. I would not have a problem with the use of a msgbox that asks the user if the number that chose is the correct one&.if the user selects no, the cell contents would clear and the number would still appear back in the drop list.
As a bonus, it would be cool if someone assigns a number, then days later deletes it (not needing the drawing number) and the drop list is automatically update to now include that number.
I have found some code online that I tried to use as a starting point but ran into many troubles and side effects that I mentioned above.
The code from the Internet:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strVal As String
Dim strEntry As String
On Error Resume Next
strVal = Target.Validation.Formula1
If Not strVal = vbNullString Then
strEntry = Target
Application.EnableEvents = False
With Sheet1.Range("MyList")
.Replace What:=strEntry, _
Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
.Range("A1", .Range("A65536").End(xlUp)
).Name = "MyList"
End With
End If
Application.EnableEvents = True
On Error GoTo 0
End Sub
