Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox RowSource Question
Greetings,
I have two TextBoxes, TB1 and TB2, on a UserForm, UF1. I need to enter a number in TB1 and on Exit have it find the name that is associated with the entered number from a 2 column list in a named range called List1, this list is sorted alphabetically. The difficult part is I need to go in either direction. (ie put a number in TB1 and TB2 shows the corresponding name OR put a name into TB2 and the corresponding number shows up in TB1). The real problem is that the names maybe duplicated with different numbers or some numbers may have more then one name. Anyone have an idea on where start? Any help is appreciated. TIA -Minitman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox RowSource Question
That isn't a programming problem - that is a business rule problem. sounds
like you need a combobox for each where, when a value is put in one combobox, the other combobox fills with the matching possibilities. You just need to loop through the matching column and do additem for the matching cell in the other column. You will need to check in the click event of each if there is already a value in the other combobox so you don't get circular events. -- Regards, Tom Ogilvy "Minitman" wrote in message ... Greetings, I have two TextBoxes, TB1 and TB2, on a UserForm, UF1. I need to enter a number in TB1 and on Exit have it find the name that is associated with the entered number from a 2 column list in a named range called List1, this list is sorted alphabetically. The difficult part is I need to go in either direction. (ie put a number in TB1 and TB2 shows the corresponding name OR put a name into TB2 and the corresponding number shows up in TB1). The real problem is that the names maybe duplicated with different numbers or some numbers may have more then one name. Anyone have an idea on where start? Any help is appreciated. TIA -Minitman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox RowSource Question
Hey Tom,
Thanks for the reply. What is a business rule? I see the ComboBoxes for TextBoxes. But not sure how to get the matching possibilities. Could you be a bit more specific, please? -Minitman On Sun, 13 Feb 2005 19:47:04 -0500, "Tom Ogilvy" wrote: That isn't a programming problem - that is a business rule problem. sounds like you need a combobox for each where, when a value is put in one combobox, the other combobox fills with the matching possibilities. You just need to loop through the matching column and do additem for the matching cell in the other column. You will need to check in the click event of each if there is already a value in the other combobox so you don't get circular events. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox RowSource Question
Public bBlockEvent as Boolean
Private Sub Userform_Initialize() Dim cell as Range bBlockEvents = True combobox1.Clear combobox2.Clear for each cell in Range("List").Columns(1).Cells Combobox1.AddItem cell.Value Combobox2.AddItem cell.Offset(0,1).Value Next bBlockEvents = False End Sub Private Sub Combobox1_Click() if bBlockEvents = True then exit sub If Combobox1.Value = "" then exit sub if Trim(Combobox2.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(1).Cells if lcase(cell.Value) = Combobox1.Value then combobox2.AddItem cell.Offset(0,1).Value end if Next End if combobox2.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub Private Sub Combobox2_Click() if bBlockEvents = True then exit sub If Combobox2.Value = "" then exit sub If Trim(Combobox1.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(2).Cells if lcase(cell.Value) = Combobox2.Value then combobox1.AddItem cell.Offset(0,-1).Value end if Next End if combobox1.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub Untested, but it should give you some ideas. -- Regards, Tom Ogilvy "Minitman" wrote in message ... Hey Tom, Thanks for the reply. What is a business rule? I see the ComboBoxes for TextBoxes. But not sure how to get the matching possibilities. Could you be a bit more specific, please? -Minitman On Sun, 13 Feb 2005 19:47:04 -0500, "Tom Ogilvy" wrote: That isn't a programming problem - that is a business rule problem. sounds like you need a combobox for each where, when a value is put in one combobox, the other combobox fills with the matching possibilities. You just need to loop through the matching column and do additem for the matching cell in the other column. You will need to check in the click event of each if there is already a value in the other combobox so you don't get circular events. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox RowSource Question
Hey Tom,
There are a few items in your code that I am not familiar with. And that is great! I will now attempt to understand how they works and make use of them. Thank you, I would never have even suspected that some of these commands existed, let alone what they do. As always, I am in your debt. -Minitman On Sun, 13 Feb 2005 20:45:52 -0500, "Tom Ogilvy" wrote: Public bBlockEvent as Boolean Private Sub Userform_Initialize() Dim cell as Range bBlockEvents = True combobox1.Clear combobox2.Clear for each cell in Range("List").Columns(1).Cells Combobox1.AddItem cell.Value Combobox2.AddItem cell.Offset(0,1).Value Next bBlockEvents = False End Sub Private Sub Combobox1_Click() if bBlockEvents = True then exit sub If Combobox1.Value = "" then exit sub if Trim(Combobox2.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(1).Cells if lcase(cell.Value) = Combobox1.Value then combobox2.AddItem cell.Offset(0,1).Value end if Next End if combobox2.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub Private Sub Combobox2_Click() if bBlockEvents = True then exit sub If Combobox2.Value = "" then exit sub If Trim(Combobox1.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(2).Cells if lcase(cell.Value) = Combobox2.Value then combobox1.AddItem cell.Offset(0,-1).Value end if Next End if combobox1.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub Untested, but it should give you some ideas. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox RowSource Question
Hey Tom,
I just went looking for bBlockEvent and could find no reference anywhere. So I have to ask, what is it? -Minitman On Sun, 13 Feb 2005 20:45:52 -0500, "Tom Ogilvy" wrote: Public bBlockEvent as Boolean Private Sub Userform_Initialize() Dim cell as Range bBlockEvents = True combobox1.Clear combobox2.Clear for each cell in Range("List").Columns(1).Cells Combobox1.AddItem cell.Value Combobox2.AddItem cell.Offset(0,1).Value Next bBlockEvents = False End Sub Private Sub Combobox1_Click() if bBlockEvents = True then exit sub If Combobox1.Value = "" then exit sub if Trim(Combobox2.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(1).Cells if lcase(cell.Value) = Combobox1.Value then combobox2.AddItem cell.Offset(0,1).Value end if Next End if combobox2.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub Private Sub Combobox2_Click() if bBlockEvents = True then exit sub If Combobox2.Value = "" then exit sub If Trim(Combobox1.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(2).Cells if lcase(cell.Value) = Combobox2.Value then combobox1.AddItem cell.Offset(0,-1).Value end if Next End if combobox1.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub Untested, but it should give you some ideas. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox RowSource Question
Hey Tom,
Opps - I misinterpreted what I saw. I get the bBlockEvent! I am having a bit of an identity problem! ComboBox1 is showing what should be in ComboBox2 and visa-versa. I tried playing with it, but nothing reacted as I thought it should! Any suggestions? -Minitman On Sun, 13 Feb 2005 20:45:52 -0500, "Tom Ogilvy" wrote: Public bBlockEvent as Boolean Private Sub Userform_Initialize() Dim cell as Range bBlockEvents = True combobox1.Clear combobox2.Clear for each cell in Range("List").Columns(1).Cells Combobox1.AddItem cell.Value Combobox2.AddItem cell.Offset(0,1).Value Next bBlockEvents = False End Sub Private Sub Combobox1_Click() if bBlockEvents = True then exit sub If Combobox1.Value = "" then exit sub if Trim(Combobox2.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(1).Cells if lcase(cell.Value) = Combobox1.Value then combobox2.AddItem cell.Offset(0,1).Value end if Next End if combobox2.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub Private Sub Combobox2_Click() if bBlockEvents = True then exit sub If Combobox2.Value = "" then exit sub If Trim(Combobox1.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(2).Cells if lcase(cell.Value) = Combobox2.Value then combobox1.AddItem cell.Offset(0,-1).Value end if Next End if combobox1.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub Untested, but it should give you some ideas. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox RowSource Question
I believe I have made the changes to reverse what appears in which combobox.
Public bBlockEvent as Boolean Private Sub Userform_Initialize() Dim cell as Range bBlockEvents = True combobox1.Clear combobox2.Clear for each cell in Range("List").Columns(1).Cells Combobox1.AddItem cell.Offet(0,1).Value Combobox2.AddItem cell.Value Next bBlockEvents = False End Sub Private Sub Combobox1_Click() if bBlockEvents = True then exit sub If Combobox1.Value = "" then exit sub if Trim(Combobox2.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(2).Cells if lcase(cell.Value) = Combobox1.Value then combobox2.AddItem cell.Offset(0,-1).Value end if Next End if combobox2.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub Private Sub Combobox2_Click() if bBlockEvents = True then exit sub If Combobox2.Value = "" then exit sub If Trim(Combobox1.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(1).Cells if lcase(cell.Value) = Combobox2.Value then combobox1.AddItem cell.Offset(0,1).Value end if Next End if combobox1.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub -- Regards, Tom Ogilvy "Minitman" wrote in message ... Hey Tom, Opps - I misinterpreted what I saw. I get the bBlockEvent! I am having a bit of an identity problem! ComboBox1 is showing what should be in ComboBox2 and visa-versa. I tried playing with it, but nothing reacted as I thought it should! Any suggestions? -Minitman On Sun, 13 Feb 2005 20:45:52 -0500, "Tom Ogilvy" wrote: Public bBlockEvent as Boolean Private Sub Userform_Initialize() Dim cell as Range bBlockEvents = True combobox1.Clear combobox2.Clear for each cell in Range("List").Columns(1).Cells Combobox1.AddItem cell.Value Combobox2.AddItem cell.Offset(0,1).Value Next bBlockEvents = False End Sub Private Sub Combobox1_Click() if bBlockEvents = True then exit sub If Combobox1.Value = "" then exit sub if Trim(Combobox2.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(1).Cells if lcase(cell.Value) = Combobox1.Value then combobox2.AddItem cell.Offset(0,1).Value end if Next End if combobox2.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub Private Sub Combobox2_Click() if bBlockEvents = True then exit sub If Combobox2.Value = "" then exit sub If Trim(Combobox1.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(2).Cells if lcase(cell.Value) = Combobox2.Value then combobox1.AddItem cell.Offset(0,-1).Value end if Next End if combobox1.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub Untested, but it should give you some ideas. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox RowSource Question
Hey Tom.
I tried this and it somewhat works. I still need help to get it to be interactive. Each ComboBox will now show the correct list, but if I choose any of the items in either ComboBox the other ComboBox is supposed to show the corresponding entry in it or better yet show this in the corresponding TextBox. (I have 2 ComboBoxes and 2 TextBoxes) Is this even possible? Any help is appreciated. TIA -Minitman On Mon, 14 Feb 2005 08:35:10 -0500, "Tom Ogilvy" wrote: I believe I have made the changes to reverse what appears in which combobox. Public bBlockEvent as Boolean Private Sub Userform_Initialize() Dim cell as Range bBlockEvents = True combobox1.Clear combobox2.Clear for each cell in Range("List").Columns(1).Cells Combobox1.AddItem cell.Offet(0,1).Value Combobox2.AddItem cell.Value Next bBlockEvents = False End Sub Private Sub Combobox1_Click() if bBlockEvents = True then exit sub If Combobox1.Value = "" then exit sub if Trim(Combobox2.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(2).Cells if lcase(cell.Value) = Combobox1.Value then combobox2.AddItem cell.Offset(0,-1).Value end if Next End if combobox2.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub Private Sub Combobox2_Click() if bBlockEvents = True then exit sub If Combobox2.Value = "" then exit sub If Trim(Combobox1.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(1).Cells if lcase(cell.Value) = Combobox2.Value then combobox1.AddItem cell.Offset(0,1).Value end if Next End if combobox1.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox RowSource Question
Hey Tom,
I thought I should be a bit more exact in my decryption of what is happening, Which ever ComboBox I pick an entry from the other show hot the corresponding entry, but only the first item in it's list. Example: List: Column 1 Column 2 ABC Group 458-5584 DEG Group 325-4515 m&m Group 235-4589 ComboBox 2 choose m&m Group and ComboBox 1 should show 235-4589 instead it shows 458-5584. Or in ComboBox1 choose 325-4515 and you should see DEG Group in ComboBox 2, instead you see ABC Group. Can this be fixed? Any help would be appreciated. TIA -Minitman On Tue, 15 Feb 2005 08:15:55 -0600, Minitman wrote: Hey Tom. I tried this and it somewhat works. I still need help to get it to be interactive. Each ComboBox will now show the correct list, but if I choose any of the items in either ComboBox the other ComboBox is supposed to show the corresponding entry in it or better yet show this in the corresponding TextBox. (I have 2 ComboBoxes and 2 TextBoxes) Is this even possible? Any help is appreciated. TIA -Minitman On Mon, 14 Feb 2005 08:35:10 -0500, "Tom Ogilvy" wrote: I believe I have made the changes to reverse what appears in which combobox. Public bBlockEvent as Boolean Private Sub Userform_Initialize() Dim cell as Range bBlockEvents = True combobox1.Clear combobox2.Clear for each cell in Range("List").Columns(1).Cells Combobox1.AddItem cell.Offet(0,1).Value Combobox2.AddItem cell.Value Next bBlockEvents = False End Sub Private Sub Combobox1_Click() if bBlockEvents = True then exit sub If Combobox1.Value = "" then exit sub if Trim(Combobox2.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(2).Cells if lcase(cell.Value) = Combobox1.Value then combobox2.AddItem cell.Offset(0,-1).Value end if Next End if combobox2.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub Private Sub Combobox2_Click() if bBlockEvents = True then exit sub If Combobox2.Value = "" then exit sub If Trim(Combobox1.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(1).Cells if lcase(cell.Value) = Combobox2.Value then combobox1.AddItem cell.Offset(0,1).Value end if Next End if combobox1.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox RowSource Question
If there is only a single choice in the other combobo for the selected item,
then both comboboxes will have values. If there are multiple choices, then the other combobox will appear blank and the user can make a restricted selection from the other combobox. Public bBlockEvents As Boolean Private Sub Userform_Initialize() Dim cell As Range bBlockEvents = True ComboBox1.Clear ComboBox2.Clear For Each cell In Range("List").Columns(1).Cells ComboBox1.AddItem cell.Offset(0, 1).Value ComboBox2.AddItem cell.Value Next bBlockEvents = False End Sub Private Sub Combobox1_Click() If bBlockEvents = True Then Exit Sub If ComboBox1.Value = "" Then Exit Sub If Trim(ComboBox2.Value) = "" Then On Error GoTo ErrHandler bBlockEvents = True ComboBox2.Clear For Each cell In Range("List").Columns(2).Cells If LCase(cell.Value) = LCase(ComboBox1.Value) Then ComboBox2.AddItem cell.Offset(0, -1).Value End If Next If ComboBox2.ListCount 1 Then ComboBox2.ListIndex = -1 Else ComboBox2.ListIndex = 0 End If End If ErrHandler: bBlockEvents = False End Sub Private Sub Combobox2_Click() If bBlockEvents = True Then Exit Sub If ComboBox2.Value = "" Then Exit Sub If Trim(ComboBox1.Value) = "" Then On Error GoTo ErrHandler bBlockEvents = True ComboBox1.Clear For Each cell In Range("List").Columns(1).Cells If LCase(cell.Value) = LCase(ComboBox2.Value) Then ComboBox1.AddItem cell.Offset(0, 1).Value End If Next If ComboBox1.ListCount 1 Then ComboBox1.ListIndex = -1 Else ComboBox1.ListIndex = 0 End If End If ErrHandler: bBlockEvents = False End Sub I made some modifications and I tested it and it worked for me. -- Regards, Tom Ogilvy "Minitman" wrote in message ... Hey Tom, I thought I should be a bit more exact in my decryption of what is happening, Which ever ComboBox I pick an entry from the other show hot the corresponding entry, but only the first item in it's list. Example: List: Column 1 Column 2 ABC Group 458-5584 DEG Group 325-4515 m&m Group 235-4589 ComboBox 2 choose m&m Group and ComboBox 1 should show 235-4589 instead it shows 458-5584. Or in ComboBox1 choose 325-4515 and you should see DEG Group in ComboBox 2, instead you see ABC Group. Can this be fixed? Any help would be appreciated. TIA -Minitman On Tue, 15 Feb 2005 08:15:55 -0600, Minitman wrote: Hey Tom. I tried this and it somewhat works. I still need help to get it to be interactive. Each ComboBox will now show the correct list, but if I choose any of the items in either ComboBox the other ComboBox is supposed to show the corresponding entry in it or better yet show this in the corresponding TextBox. (I have 2 ComboBoxes and 2 TextBoxes) Is this even possible? Any help is appreciated. TIA -Minitman On Mon, 14 Feb 2005 08:35:10 -0500, "Tom Ogilvy" wrote: I believe I have made the changes to reverse what appears in which combobox. Public bBlockEvent as Boolean Private Sub Userform_Initialize() Dim cell as Range bBlockEvents = True combobox1.Clear combobox2.Clear for each cell in Range("List").Columns(1).Cells Combobox1.AddItem cell.Offet(0,1).Value Combobox2.AddItem cell.Value Next bBlockEvents = False End Sub Private Sub Combobox1_Click() if bBlockEvents = True then exit sub If Combobox1.Value = "" then exit sub if Trim(Combobox2.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(2).Cells if lcase(cell.Value) = Combobox1.Value then combobox2.AddItem cell.Offset(0,-1).Value end if Next End if combobox2.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub Private Sub Combobox2_Click() if bBlockEvents = True then exit sub If Combobox2.Value = "" then exit sub If Trim(Combobox1.Value) = "" then on Error goto ErrHandler bBlockEvents = True for each cell in Range("List").columns(1).Cells if lcase(cell.Value) = Combobox2.Value then combobox1.AddItem cell.Offset(0,1).Value end if Next End if combobox1.ListIndex = 0 ErrHandler: bBlockEvents = False End Sub |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox RowSource Question
Hey Tom,
Thanks Tom, I really appreciate your assistance. It worked for me as well, but in only one direction. If I choose a number from ComboBox1, the corresponding name does show up in ComboBox2 - This is good. However, if I first choose a name from ComboBox2, I don't get anything in ComboBox1, it stays empty, it should show the corresponding number. Any suggestions? TIA -Minitman On Tue, 15 Feb 2005 21:16:20 -0500, "Tom Ogilvy" wrote: If there is only a single choice in the other combobo for the selected item, then both comboboxes will have values. If there are multiple choices, then the other combobox will appear blank and the user can make a restricted selection from the other combobox. Public bBlockEvents As Boolean Private Sub Userform_Initialize() Dim cell As Range bBlockEvents = True ComboBox1.Clear ComboBox2.Clear For Each cell In Range("List").Columns(1).Cells ComboBox1.AddItem cell.Offset(0, 1).Value ComboBox2.AddItem cell.Value Next bBlockEvents = False End Sub Private Sub Combobox1_Click() If bBlockEvents = True Then Exit Sub If ComboBox1.Value = "" Then Exit Sub If Trim(ComboBox2.Value) = "" Then On Error GoTo ErrHandler bBlockEvents = True ComboBox2.Clear For Each cell In Range("List").Columns(2).Cells If LCase(cell.Value) = LCase(ComboBox1.Value) Then ComboBox2.AddItem cell.Offset(0, -1).Value End If Next If ComboBox2.ListCount 1 Then ComboBox2.ListIndex = -1 Else ComboBox2.ListIndex = 0 End If End If ErrHandler: bBlockEvents = False End Sub Private Sub Combobox2_Click() If bBlockEvents = True Then Exit Sub If ComboBox2.Value = "" Then Exit Sub If Trim(ComboBox1.Value) = "" Then On Error GoTo ErrHandler bBlockEvents = True ComboBox1.Clear For Each cell In Range("List").Columns(1).Cells If LCase(cell.Value) = LCase(ComboBox2.Value) Then ComboBox1.AddItem cell.Offset(0, 1).Value End If Next If ComboBox1.ListCount 1 Then ComboBox1.ListIndex = -1 Else ComboBox1.ListIndex = 0 End If End If ErrHandler: bBlockEvents = False End Sub I made some modifications and I tested it and it worked for me. -- Regards, Tom Ogilvy |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox RowSource Question
Opps,,,,
Sorry Tom, please ignore my last post. I had accidentally chosen a name that had multiple numbers and I forgot your warning about that situation. I just rechecked and it is working and working very well (if you discount operator error) ;- } Thanks again for your help. -Minitman On Tue, 15 Feb 2005 21:16:20 -0500, "Tom Ogilvy" wrote: If there is only a single choice in the other combobo for the selected item, then both comboboxes will have values. If there are multiple choices, then the other combobox will appear blank and the user can make a restricted selection from the other combobox. Public bBlockEvents As Boolean Private Sub Userform_Initialize() Dim cell As Range bBlockEvents = True ComboBox1.Clear ComboBox2.Clear For Each cell In Range("List").Columns(1).Cells ComboBox1.AddItem cell.Offset(0, 1).Value ComboBox2.AddItem cell.Value Next bBlockEvents = False End Sub Private Sub Combobox1_Click() If bBlockEvents = True Then Exit Sub If ComboBox1.Value = "" Then Exit Sub If Trim(ComboBox2.Value) = "" Then On Error GoTo ErrHandler bBlockEvents = True ComboBox2.Clear For Each cell In Range("List").Columns(2).Cells If LCase(cell.Value) = LCase(ComboBox1.Value) Then ComboBox2.AddItem cell.Offset(0, -1).Value End If Next If ComboBox2.ListCount 1 Then ComboBox2.ListIndex = -1 Else ComboBox2.ListIndex = 0 End If End If ErrHandler: bBlockEvents = False End Sub Private Sub Combobox2_Click() If bBlockEvents = True Then Exit Sub If ComboBox2.Value = "" Then Exit Sub If Trim(ComboBox1.Value) = "" Then On Error GoTo ErrHandler bBlockEvents = True ComboBox1.Clear For Each cell In Range("List").Columns(1).Cells If LCase(cell.Value) = LCase(ComboBox2.Value) Then ComboBox1.AddItem cell.Offset(0, 1).Value End If Next If ComboBox1.ListCount 1 Then ComboBox1.ListIndex = -1 Else ComboBox1.ListIndex = 0 End If End If ErrHandler: bBlockEvents = False End Sub I made some modifications and I tested it and it worked for me. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TextBox Question | Excel Discussion (Misc queries) | |||
Textbox question? | Excel Discussion (Misc queries) | |||
Textbox question | Excel Programming | |||
textbox question | Excel Programming | |||
Textbox question 2 | Excel Programming |