PDA

View Full Version : Solved: any way to delete & refresh contents in a dropdownbox?



lvito001
09-05-2007, 07:42 AM
Hello all! I'm a newbie to the forum & to VBA, so I apologize in advance for any inarticulate phrasing I may use.

I've got a problem that I've been like this: :banghead: for the past week. Here's my scenario...

I have a userform with 2 drop down boxes. (namely, objectbox & subjecttypebox). objectbox contains a list: hospital, dmat, ambulance, dispatcher. everytime a user selects one option from the list... i would like the subjecttypebox to have a selection of particular options (such as, for hospital: bon secours hospital, sentara hospital... etc. | such as, for dmat: norfolk southern, ngc,... etc. | and so on).

my data comes from a spreadsheet and column B has the list of objects that i am linking to the objectbox. i would like column D to link to the subjecttypebox, but! I need to parse the information so that only specific hospitals show in the subjectypebox when the user selects hospital or specific dmats if dmat is selected... and so on. there are about 3400 rows of data and the column lengths are not equal.

if anyone can help me, i'd bow down to you such as this smiley is doing: :bow: . because I have been struggling over this for the past few days now, and frankly... I'm over it!

Thanks very much,
Lena

rory
09-05-2007, 07:54 AM
It might be easier if you posted a sample workbook (use the Go Advanced button and the Manage Attachments option) , but generally speaking, you can use the change event of the first combo to clear the second combo, apply an autofilter to the sheet, iterate through the visible cells in column D and use AddItem to add them to the combo, or put them in an array and assing that to the List property.

lvito001
09-05-2007, 08:16 AM
:dunno as intelligent as that sounds, its a foreign language to me! Anyway to simplify your words and maybe giving a sample code? I would post a sample of my code, but unfortunately I can't... confidentiality reasons. My code could not possibly be any better than anyone's on this forum. Thanks for helping though.

rory
09-05-2007, 08:24 AM
Don't really need a copy of your code - more a question of how your data is laid out: which info is in which columns, are there duplicates to worry about, and things like that. If you can give details for that, or provide a dummy workbook with garbage data but in the right places and with the right headings, we can provide the code.

lvito001
09-05-2007, 08:56 AM
I have posted a condensed version than what I am dealing with. Hope this helps you see my viewpoint. Thanks again for your help!

lvito001
09-05-2007, 09:04 AM
Also, I've just noticed an error I've made in my description... I meant Column D contains the objects for objectbox... and Column B contains what is meant to be in the subjecttypebox

rory
09-05-2007, 09:06 AM
Looking at that it's not clear to me whether you want column B data for the second combo or column D as you said?

rory
09-05-2007, 09:06 AM
Ah, that answers that then! :)

lucas
09-05-2007, 09:08 AM
Lena condensed the userform so much that I can't see it.:devil2:

lvito001
09-05-2007, 09:12 AM
I didn't post the userform but I did post the data! Is it necessary to post the GUI in order to do the code?

like I said, I'm a newbie... I'm pretty much wearing a VBA dunce hat right now! sorry, but thanks

lucas
09-05-2007, 09:15 AM
It would just save us having to recreate it.

lvito001
09-05-2007, 09:26 AM
oh I see! Ok, I've condensed my userform way down also to make sure I'm not breaking any confidentiality rules. Let me know if anymore info is needed. Thanks!

lucas
09-05-2007, 09:52 AM
Here's an example using additem: see attached

lvito001
09-05-2007, 10:01 AM
a ha! you know what? I do have that code... I did use .additem and everything possible but you made me realize that I've left out one very important point that I forgot to make...

I need the code to work with any data. If i were to change all the data in column B, I'd need the code to be versatile enough to work so I wouldn't have to change the code. For example: I can't set anything equal to a particular "Medical Center" or anything in parentheses. It would have to be a cell reference (I think thats the term?). Because if I changed all the Medical Centers in column B to something else, then the code would not work. I know, its frustrating huh? Well it is to me! I did use additem before, but then I was told that the code has to be versatile and I can set anything equal to anything specific and in " ".

I really appreciate everyones help and if nothing can be done then its ok! Thanks for taking the time to help me out :wavey:

lucas
09-05-2007, 10:07 AM
Are you willing to restructure the layout of the sheet?

lucas
09-05-2007, 10:16 AM
You see, you can get a unique list for the ObjectBox using something like this:
Private Sub UserForm_Initialize()
Dim UniqueList() As String
Dim x As Long
Dim Rng1 As Range
Dim c As Range
Dim Unique As Boolean
Dim y As Long

Set Rng1 = Sheets("Sheet1").Range("B1:B1103")
y = 1

ReDim UniqueList(1 To Rng1.Rows.Count)

For Each c In Rng1
If Not c.Value = vbNullString Then
Unique = True
For x = 1 To y
If UniqueList(x) = c.Text Then
Unique = False
End If
Next
If Unique Then
y = y + 1
Me.ObjectBox.AddItem (c.Text)
UniqueList(y) = c.Text
End If
End If
Next

End Sub

But I don't see the other selections for the second combo box(SubjectTypeBox) anywhere on the sheet...all you show is "hospital" so where would excel get that information?

lucas
09-05-2007, 10:19 AM
rory,
I hope I didn't offend by jumping in here with you....was not my intention.

lvito001
09-05-2007, 12:07 PM
I'm not too sure I understand your confusion. Here's how I view the problem. Column B contains the specific object that it is related to from Column D. Example is: Column D says "hospital" so whatever is in Column B is a specific type of hospital -- ex: "Medical Center". Also could go for when Column D says "dmat" which indicates that whatever is in Column B is a dmat -- ex: "lyon".

I would like to see that when the ObjectBox selection is say, "dmat" then the SubjectTypeBox only contains what Column B has for that specific dmat row that is active. and when the user changes his/her mind to say, "dispatcher"... then the SubjectTypeBox would delete the contents and refresh to display the Column B contents for that specific dispatcher row that is active. All the while, parsing so that there are no duplicates and making sure there is no specific setting the output to a particular "Medical Center" per say.

I realize this is very complicated and maybe you can see why a rookie, such as myself, is having such a hard time dealing with this particular problem. Again, thanks for your help!

lvito001
09-05-2007, 12:10 PM
If reconstructing the spreadsheet is the answer to parsing this info and outputting it in the right drop down box, then by all means!!

I'll try anything cause everything I have tried just doesn't lead me to any accurate results. Thanks!

lucas
09-05-2007, 12:18 PM
So you want column D to show in the first combobox...ObjectBox

rory
09-06-2007, 07:23 AM
Steve,
No problem at all - helps to have people in different time zones! :)

rory
09-06-2007, 08:07 AM
Try this - I've used a hidden sheet to store the data for the comboboxes.

lvito001
09-07-2007, 05:24 AM
That. Is. Perfect!

Thank you everyone so much for helping me. I really appreciate the help! Two very enthusiastic thumbs up!

:clap: :bow: :thumb

lvito001
09-17-2007, 05:53 AM
is there a way to fix this code to get it to work when the columns have changed from b:d to c:d?

Sub GetObjectList()
Dim lngRow As Long, lngRowCount As Long
Intersect(Dump.Columns("A"), Dump.UsedRange).ClearContents
With Sheets("DB")
lngRowCount = .Cells(.Rows.Count, "D").End(xlUp).Row
UniqueList .Range("D2", .Cells(lngRowCount, "D")), Dump.Range("A1")
End With
With Dump
lngRowCount = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:A" & lngRowCount).Sort key1:=.Cells(1, "A"), header:=xlYes
For lngRow = lngRowCount To 2 Step -1
With .Cells(lngRow, "A")
If .Value = "SUBJ_TYPE" Or .Value = "INCIDENT" Or Len(Trim$(.Value)) = 0 Then _
.Delete shift:=xlShiftUp
End With
Next lngRow
End With
End Sub
Sub GetSubjectList(strObject As String)
Dim lngRow As Long, lngRowCount As Long, lngOutputRow As Long
Dim dicSubjects As Object
Dim varData
lngOutputRow = 1
If Not Intersect(Dump.Columns("C"), Dump.UsedRange) Is Nothing Then Intersect(Dump.Columns("C"), Dump.UsedRange).ClearContents
Set dicSubjects = CreateObject("Scripting.Dictionary")
With Sheets("DB")
lngRowCount = .Cells(.Rows.Count, "B").End(xlUp).Row
For lngRow = 2 To lngRowCount
If .Cells(lngRow, "D").Value = strObject Then
If Not dicSubjects.exists(.Cells(lngRow, "B").Value) Then
Dump.Cells(lngOutputRow, "C").Value = .Cells(lngRow, "B").Value
dicSubjects.Add .Cells(lngRow, "B").Value, .Cells(lngRow, "B")
lngOutputRow = lngOutputRow + 1
End If
End If
Next lngRow
End With
Set dicSubjects = Nothing
With Dump
lngRowCount = .Cells(.Rows.Count, "C").End(xlUp).Row
.Range("C1:C" & lngRowCount).Sort key1:=.Cells(1, "C"), header:=xlNo
For lngRow = lngRowCount To 1 Step -1
With .Cells(lngRow, "A")
If .Value = "SUBJ_TYPE" Or .Value = "INCIDENT" Or Len(Trim$(.Value)) = 0 Then _
.Delete shift:=xlShiftUp
End With
Next lngRow
End With
End Sub

rory
09-17-2007, 06:09 AM
Try replacing all instances of "B" in the second routine to "C".

lvito001
09-17-2007, 06:24 AM
thanks rory! you're a genius!