Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox input
Hi
The code seeks to establish whether data exists already on a given date and selected vehicle registration. It works ok when using the mouse to select any registration from a userform combobox list but fails sometimes with a manual input. The reason being the required string is entered and tested as a whole. To shorten the typing I have added an index column to the control. But if this index is not known, it will be necessary for the registration to be typed in as per frmMatchEntryComplete. A manual input €˜failure occurs if the target vehicle is index 10 = Y123 ABC. The Do Until Loop causes an escape as soon as 1 is typed. Though the cursor remains in the box for further input a message will have been triggered before the second digit 0 can be entered. 1. How can I amend the code to suit both mouse and manual input? 2. After making a selection using the mouse, the input becomes selected. This makes it easy to change. With a typed input neither an index nor registration becomes selected. How can this be made similar? SelStart and SelLength do not appear to work here. 3. With a mouse input it is easy to move the focus to the next control by making the test if cbo1.textlength 0 then go. How could this be done with a manual input without having to manually tab? Id be grateful for advice. T.I.A. Geoff To see how the code fails with manual input, put a default combobox on a form and name it cbo1 On wksheet1 put the following data starting in cell A1 Date 10 May 2005 11 May 2005 12 May 2005 12 May 2005 12 May 2005 12 May 2005 13 May 2005 This starting in B1 Veh Reg Y123 ABC Y123 ABC Y123 ABC Y127 ABC Y129 ABC Y132 ABC Y126 ABC This starting in D1 (this is the database to populate the control) Veh Reg ?? Y123 ABC Y124 ABC Y125 ABC Y126 ABC Y127 ABC Y128 ABC Y129 ABC Y130 ABC Y131 ABC Y132 ABC And finally this into the form module Option Explicit Dim VehReg() As String Dim tbl As Range, rng As Range Dim i As Integer Dim cbo1cntr As Integer Dim firstfind Private Sub UserForm_Initialize() With UserForm1 .Left = 250 .Top = 65 .startupposition = 0 End With With cbo1 .ColumnCount = 2 .ColumnWidths = 15 & ";" & 30 .ListRows = 15 End With Set tbl = Sheets(1).Range("D1").CurrentRegion.Offset(1, 0) ReDim VehReg(0 To tbl.Rows.Count - 2, 1) For i = 0 To tbl.Rows.Count - 2 'create index VehReg(i, 0) = i Next i For i = 0 To tbl.Rows.Count - 2 'populate box VehReg(i, 1) = Sheets(1).Cells(i + 2, 4) Next i cbo1.List() = VehReg Range("A20").Select End Sub Private Sub cbo1_Change() Set rng = Sheets(1).Range("A23") 'find date, example 12/05/2005 Set rng = Sheets(1).Columns(1).Find _ (what:=CDate("12 May 2005"), _ After:=rng, _ LookIn:=xlFormulas, _ Lookat:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext) firstfind = "" If Not rng Is Nothing And Not cbo1.Value = "" And Not cbo1.Value _ cbo1.ListCount Then Do Until rng.Offset(, 1).Value = cbo1.Column(1) Or rng.Address = firstfind If firstfind = "" Then firstfind = rng.Address Set rng = Sheets(1).Columns(1).FindNext(After:=rng) Loop If rng.Offset(, 1).Value = cbo1.Column(1) Then Range(rng.Address, Cells(rng.Row, rng.Column + 1)).Select MsgBox "A vehicle record exists on that date " & vbCr & _ "Do something" Else Range("A20").Select MsgBox "No record exists " & vbCr & _ "Do something else " End If Else If Not cbo1cntr = 1 Then 'if index is greater than list cbo1cntr = 1 Range("A20").Select MsgBox "Index does not exist " cbo1.Value = "" End If End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox input
Geoff,
You may need to look at the combo box events. The change event triggers when you either select an item in the list or when you type a single character. The exit event will trigger when you leave the combobox. -- steveB Remove "AYN" from email to respond "Geoff" wrote in message ... Hi The code seeks to establish whether data exists already on a given date and selected vehicle registration. It works ok when using the mouse to select any registration from a userform combobox list but fails sometimes with a manual input. The reason being the required string is entered and tested as a whole. To shorten the typing I have added an index column to the control. But if this index is not known, it will be necessary for the registration to be typed in as per frmMatchEntryComplete. A manual input 'failure' occurs if the target vehicle is index 10 = Y123 ABC. The Do Until Loop causes an escape as soon as 1 is typed. Though the cursor remains in the box for further input a message will have been triggered before the second digit 0 can be entered. 1. How can I amend the code to suit both mouse and manual input? 2. After making a selection using the mouse, the input becomes selected. This makes it easy to change. With a typed input neither an index nor registration becomes selected. How can this be made similar? SelStart and SelLength do not appear to work here. 3. With a mouse input it is easy to move the focus to the next control by making the test if cbo1.textlength 0 then go. How could this be done with a manual input without having to manually tab? I'd be grateful for advice. T.I.A. Geoff To see how the code fails with manual input, put a default combobox on a form and name it cbo1 On wksheet1 put the following data starting in cell A1 Date 10 May 2005 11 May 2005 12 May 2005 12 May 2005 12 May 2005 12 May 2005 13 May 2005 This starting in B1 Veh Reg Y123 ABC Y123 ABC Y123 ABC Y127 ABC Y129 ABC Y132 ABC Y126 ABC This starting in D1 (this is the database to populate the control) Veh Reg ?? Y123 ABC Y124 ABC Y125 ABC Y126 ABC Y127 ABC Y128 ABC Y129 ABC Y130 ABC Y131 ABC Y132 ABC And finally this into the form module Option Explicit Dim VehReg() As String Dim tbl As Range, rng As Range Dim i As Integer Dim cbo1cntr As Integer Dim firstfind Private Sub UserForm_Initialize() With UserForm1 .Left = 250 .Top = 65 .startupposition = 0 End With With cbo1 .ColumnCount = 2 .ColumnWidths = 15 & ";" & 30 .ListRows = 15 End With Set tbl = Sheets(1).Range("D1").CurrentRegion.Offset(1, 0) ReDim VehReg(0 To tbl.Rows.Count - 2, 1) For i = 0 To tbl.Rows.Count - 2 'create index VehReg(i, 0) = i Next i For i = 0 To tbl.Rows.Count - 2 'populate box VehReg(i, 1) = Sheets(1).Cells(i + 2, 4) Next i cbo1.List() = VehReg Range("A20").Select End Sub Private Sub cbo1_Change() Set rng = Sheets(1).Range("A23") 'find date, example 12/05/2005 Set rng = Sheets(1).Columns(1).Find _ (what:=CDate("12 May 2005"), _ After:=rng, _ LookIn:=xlFormulas, _ Lookat:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext) firstfind = "" If Not rng Is Nothing And Not cbo1.Value = "" And Not cbo1.Value _ cbo1.ListCount Then Do Until rng.Offset(, 1).Value = cbo1.Column(1) Or rng.Address = firstfind If firstfind = "" Then firstfind = rng.Address Set rng = Sheets(1).Columns(1).FindNext(After:=rng) Loop If rng.Offset(, 1).Value = cbo1.Column(1) Then Range(rng.Address, Cells(rng.Row, rng.Column + 1)).Select MsgBox "A vehicle record exists on that date " & vbCr & _ "Do something" Else Range("A20").Select MsgBox "No record exists " & vbCr & _ "Do something else " End If Else If Not cbo1cntr = 1 Then 'if index is greater than list cbo1cntr = 1 Range("A20").Select MsgBox "Index does not exist " cbo1.Value = "" End If End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox input
I am aware.
The prime question remains, how is it possible to prevent the do loop test whist inputting single characters. How can I stop the test after the digit 1 and wait until 10? Geoff "STEVE BELL" wrote: Geoff, You may need to look at the combo box events. The change event triggers when you either select an item in the list or when you type a single character. The exit event will trigger when you leave the combobox. -- steveB Remove "AYN" from email to respond "Geoff" wrote in message ... Hi The code seeks to establish whether data exists already on a given date and selected vehicle registration. It works ok when using the mouse to select any registration from a userform combobox list but fails sometimes with a manual input. The reason being the required string is entered and tested as a whole. To shorten the typing I have added an index column to the control. But if this index is not known, it will be necessary for the registration to be typed in as per frmMatchEntryComplete. A manual input 'failure' occurs if the target vehicle is index 10 = Y123 ABC. The Do Until Loop causes an escape as soon as 1 is typed. Though the cursor remains in the box for further input a message will have been triggered before the second digit 0 can be entered. 1. How can I amend the code to suit both mouse and manual input? 2. After making a selection using the mouse, the input becomes selected. This makes it easy to change. With a typed input neither an index nor registration becomes selected. How can this be made similar? SelStart and SelLength do not appear to work here. 3. With a mouse input it is easy to move the focus to the next control by making the test if cbo1.textlength 0 then go. How could this be done with a manual input without having to manually tab? I'd be grateful for advice. T.I.A. Geoff To see how the code fails with manual input, put a default combobox on a form and name it cbo1 On wksheet1 put the following data starting in cell A1 Date 10 May 2005 11 May 2005 12 May 2005 12 May 2005 12 May 2005 12 May 2005 13 May 2005 This starting in B1 Veh Reg Y123 ABC Y123 ABC Y123 ABC Y127 ABC Y129 ABC Y132 ABC Y126 ABC This starting in D1 (this is the database to populate the control) Veh Reg ?? Y123 ABC Y124 ABC Y125 ABC Y126 ABC Y127 ABC Y128 ABC Y129 ABC Y130 ABC Y131 ABC Y132 ABC And finally this into the form module Option Explicit Dim VehReg() As String Dim tbl As Range, rng As Range Dim i As Integer Dim cbo1cntr As Integer Dim firstfind Private Sub UserForm_Initialize() With UserForm1 .Left = 250 .Top = 65 .startupposition = 0 End With With cbo1 .ColumnCount = 2 .ColumnWidths = 15 & ";" & 30 .ListRows = 15 End With Set tbl = Sheets(1).Range("D1").CurrentRegion.Offset(1, 0) ReDim VehReg(0 To tbl.Rows.Count - 2, 1) For i = 0 To tbl.Rows.Count - 2 'create index VehReg(i, 0) = i Next i For i = 0 To tbl.Rows.Count - 2 'populate box VehReg(i, 1) = Sheets(1).Cells(i + 2, 4) Next i cbo1.List() = VehReg Range("A20").Select End Sub Private Sub cbo1_Change() Set rng = Sheets(1).Range("A23") 'find date, example 12/05/2005 Set rng = Sheets(1).Columns(1).Find _ (what:=CDate("12 May 2005"), _ After:=rng, _ LookIn:=xlFormulas, _ Lookat:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext) firstfind = "" If Not rng Is Nothing And Not cbo1.Value = "" And Not cbo1.Value _ cbo1.ListCount Then Do Until rng.Offset(, 1).Value = cbo1.Column(1) Or rng.Address = firstfind If firstfind = "" Then firstfind = rng.Address Set rng = Sheets(1).Columns(1).FindNext(After:=rng) Loop If rng.Offset(, 1).Value = cbo1.Column(1) Then Range(rng.Address, Cells(rng.Row, rng.Column + 1)).Select MsgBox "A vehicle record exists on that date " & vbCr & _ "Do something" Else Range("A20").Select MsgBox "No record exists " & vbCr & _ "Do something else " End If Else If Not cbo1cntr = 1 Then 'if index is greater than list cbo1cntr = 1 Range("A20").Select MsgBox "Index does not exist " cbo1.Value = "" End If End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox input
Again -
You appear to be using a combobox change event. This will fire each time you enter a single character... Use the combobox exit event to make the event wait until you are finished with the combobox. Or you can add an if then statement in the change event macro to check the value of the combobox. (or the length of the combobox value, or ....). And use this check to exit the sub routine... -- steveB Remove "AYN" from email to respond "Geoff" wrote in message ... I am aware. The prime question remains, how is it possible to prevent the do loop test whist inputting single characters. How can I stop the test after the digit 1 and wait until 10? Geoff "STEVE BELL" wrote: Geoff, You may need to look at the combo box events. The change event triggers when you either select an item in the list or when you type a single character. The exit event will trigger when you leave the combobox. -- steveB Remove "AYN" from email to respond "Geoff" wrote in message ... Hi The code seeks to establish whether data exists already on a given date and selected vehicle registration. It works ok when using the mouse to select any registration from a userform combobox list but fails sometimes with a manual input. The reason being the required string is entered and tested as a whole. To shorten the typing I have added an index column to the control. But if this index is not known, it will be necessary for the registration to be typed in as per frmMatchEntryComplete. A manual input 'failure' occurs if the target vehicle is index 10 = Y123 ABC. The Do Until Loop causes an escape as soon as 1 is typed. Though the cursor remains in the box for further input a message will have been triggered before the second digit 0 can be entered. 1. How can I amend the code to suit both mouse and manual input? 2. After making a selection using the mouse, the input becomes selected. This makes it easy to change. With a typed input neither an index nor registration becomes selected. How can this be made similar? SelStart and SelLength do not appear to work here. 3. With a mouse input it is easy to move the focus to the next control by making the test if cbo1.textlength 0 then go. How could this be done with a manual input without having to manually tab? I'd be grateful for advice. T.I.A. Geoff To see how the code fails with manual input, put a default combobox on a form and name it cbo1 On wksheet1 put the following data starting in cell A1 Date 10 May 2005 11 May 2005 12 May 2005 12 May 2005 12 May 2005 12 May 2005 13 May 2005 This starting in B1 Veh Reg Y123 ABC Y123 ABC Y123 ABC Y127 ABC Y129 ABC Y132 ABC Y126 ABC This starting in D1 (this is the database to populate the control) Veh Reg ?? Y123 ABC Y124 ABC Y125 ABC Y126 ABC Y127 ABC Y128 ABC Y129 ABC Y130 ABC Y131 ABC Y132 ABC And finally this into the form module Option Explicit Dim VehReg() As String Dim tbl As Range, rng As Range Dim i As Integer Dim cbo1cntr As Integer Dim firstfind Private Sub UserForm_Initialize() With UserForm1 .Left = 250 .Top = 65 .startupposition = 0 End With With cbo1 .ColumnCount = 2 .ColumnWidths = 15 & ";" & 30 .ListRows = 15 End With Set tbl = Sheets(1).Range("D1").CurrentRegion.Offset(1, 0) ReDim VehReg(0 To tbl.Rows.Count - 2, 1) For i = 0 To tbl.Rows.Count - 2 'create index VehReg(i, 0) = i Next i For i = 0 To tbl.Rows.Count - 2 'populate box VehReg(i, 1) = Sheets(1).Cells(i + 2, 4) Next i cbo1.List() = VehReg Range("A20").Select End Sub Private Sub cbo1_Change() Set rng = Sheets(1).Range("A23") 'find date, example 12/05/2005 Set rng = Sheets(1).Columns(1).Find _ (what:=CDate("12 May 2005"), _ After:=rng, _ LookIn:=xlFormulas, _ Lookat:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext) firstfind = "" If Not rng Is Nothing And Not cbo1.Value = "" And Not cbo1.Value _ cbo1.ListCount Then Do Until rng.Offset(, 1).Value = cbo1.Column(1) Or rng.Address = firstfind If firstfind = "" Then firstfind = rng.Address Set rng = Sheets(1).Columns(1).FindNext(After:=rng) Loop If rng.Offset(, 1).Value = cbo1.Column(1) Then Range(rng.Address, Cells(rng.Row, rng.Column + 1)).Select MsgBox "A vehicle record exists on that date " & vbCr & _ "Do something" Else Range("A20").Select MsgBox "No record exists " & vbCr & _ "Do something else " End If Else If Not cbo1cntr = 1 Then 'if index is greater than list cbo1cntr = 1 Range("A20").Select MsgBox "Index does not exist " cbo1.Value = "" End If End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox input
Again..
Believe me I have not posted this question at the first sign of a problem. I have tried every logical test i can think of both in the change event and the exit event. The whole thing hinges on the length of string to be tested be it 1 or 2 characters of an index or the whole registration. I was hoping for specifics rather than generalities in this case. Geoff "STEVE BELL" wrote: Again - You appear to be using a combobox change event. This will fire each time you enter a single character... Use the combobox exit event to make the event wait until you are finished with the combobox. Or you can add an if then statement in the change event macro to check the value of the combobox. (or the length of the combobox value, or ....). And use this check to exit the sub routine... -- steveB Remove "AYN" from email to respond "Geoff" wrote in message ... I am aware. The prime question remains, how is it possible to prevent the do loop test whist inputting single characters. How can I stop the test after the digit 1 and wait until 10? Geoff "STEVE BELL" wrote: Geoff, You may need to look at the combo box events. The change event triggers when you either select an item in the list or when you type a single character. The exit event will trigger when you leave the combobox. -- steveB Remove "AYN" from email to respond "Geoff" wrote in message ... Hi The code seeks to establish whether data exists already on a given date and selected vehicle registration. It works ok when using the mouse to select any registration from a userform combobox list but fails sometimes with a manual input. The reason being the required string is entered and tested as a whole. To shorten the typing I have added an index column to the control. But if this index is not known, it will be necessary for the registration to be typed in as per frmMatchEntryComplete. A manual input 'failure' occurs if the target vehicle is index 10 = Y123 ABC. The Do Until Loop causes an escape as soon as 1 is typed. Though the cursor remains in the box for further input a message will have been triggered before the second digit 0 can be entered. 1. How can I amend the code to suit both mouse and manual input? 2. After making a selection using the mouse, the input becomes selected. This makes it easy to change. With a typed input neither an index nor registration becomes selected. How can this be made similar? SelStart and SelLength do not appear to work here. 3. With a mouse input it is easy to move the focus to the next control by making the test if cbo1.textlength 0 then go. How could this be done with a manual input without having to manually tab? I'd be grateful for advice. T.I.A. Geoff To see how the code fails with manual input, put a default combobox on a form and name it cbo1 On wksheet1 put the following data starting in cell A1 Date 10 May 2005 11 May 2005 12 May 2005 12 May 2005 12 May 2005 12 May 2005 13 May 2005 This starting in B1 Veh Reg Y123 ABC Y123 ABC Y123 ABC Y127 ABC Y129 ABC Y132 ABC Y126 ABC This starting in D1 (this is the database to populate the control) Veh Reg ?? Y123 ABC Y124 ABC Y125 ABC Y126 ABC Y127 ABC Y128 ABC Y129 ABC Y130 ABC Y131 ABC Y132 ABC And finally this into the form module Option Explicit Dim VehReg() As String Dim tbl As Range, rng As Range Dim i As Integer Dim cbo1cntr As Integer Dim firstfind Private Sub UserForm_Initialize() With UserForm1 .Left = 250 .Top = 65 .startupposition = 0 End With With cbo1 .ColumnCount = 2 .ColumnWidths = 15 & ";" & 30 .ListRows = 15 End With Set tbl = Sheets(1).Range("D1").CurrentRegion.Offset(1, 0) ReDim VehReg(0 To tbl.Rows.Count - 2, 1) For i = 0 To tbl.Rows.Count - 2 'create index VehReg(i, 0) = i Next i For i = 0 To tbl.Rows.Count - 2 'populate box VehReg(i, 1) = Sheets(1).Cells(i + 2, 4) Next i cbo1.List() = VehReg Range("A20").Select End Sub Private Sub cbo1_Change() Set rng = Sheets(1).Range("A23") 'find date, example 12/05/2005 Set rng = Sheets(1).Columns(1).Find _ (what:=CDate("12 May 2005"), _ After:=rng, _ LookIn:=xlFormulas, _ Lookat:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext) firstfind = "" If Not rng Is Nothing And Not cbo1.Value = "" And Not cbo1.Value _ cbo1.ListCount Then Do Until rng.Offset(, 1).Value = cbo1.Column(1) Or rng.Address = firstfind If firstfind = "" Then firstfind = rng.Address Set rng = Sheets(1).Columns(1).FindNext(After:=rng) Loop If rng.Offset(, 1).Value = cbo1.Column(1) Then Range(rng.Address, Cells(rng.Row, rng.Column + 1)).Select MsgBox "A vehicle record exists on that date " & vbCr & _ "Do something" Else Range("A20").Select MsgBox "No record exists " & vbCr & _ "Do something else " End If Else If Not cbo1cntr = 1 Then 'if index is greater than list cbo1cntr = 1 Range("A20").Select MsgBox "Index does not exist " cbo1.Value = "" End If End If End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox input
Geoff,
Sorry if I wasn't any help. (I meant no offense). I figured you knew how to add if statements to the code and make them work. sounds like you are looking for x = len(combobox1.value) or x = combobox1.listindex -- steveB Remove "AYN" from email to respond "Geoff" wrote in message ... Again.. Believe me I have not posted this question at the first sign of a problem. I have tried every logical test i can think of both in the change event and the exit event. The whole thing hinges on the length of string to be tested be it 1 or 2 characters of an index or the whole registration. I was hoping for specifics rather than generalities in this case. Geoff "STEVE BELL" wrote: Again - You appear to be using a combobox change event. This will fire each time you enter a single character... Use the combobox exit event to make the event wait until you are finished with the combobox. Or you can add an if then statement in the change event macro to check the value of the combobox. (or the length of the combobox value, or ....). And use this check to exit the sub routine... -- steveB Remove "AYN" from email to respond "Geoff" wrote in message ... I am aware. The prime question remains, how is it possible to prevent the do loop test whist inputting single characters. How can I stop the test after the digit 1 and wait until 10? Geoff "STEVE BELL" wrote: Geoff, You may need to look at the combo box events. The change event triggers when you either select an item in the list or when you type a single character. The exit event will trigger when you leave the combobox. -- steveB Remove "AYN" from email to respond "Geoff" wrote in message ... Hi The code seeks to establish whether data exists already on a given date and selected vehicle registration. It works ok when using the mouse to select any registration from a userform combobox list but fails sometimes with a manual input. The reason being the required string is entered and tested as a whole. To shorten the typing I have added an index column to the control. But if this index is not known, it will be necessary for the registration to be typed in as per frmMatchEntryComplete. A manual input 'failure' occurs if the target vehicle is index 10 = Y123 ABC. The Do Until Loop causes an escape as soon as 1 is typed. Though the cursor remains in the box for further input a message will have been triggered before the second digit 0 can be entered. 1. How can I amend the code to suit both mouse and manual input? 2. After making a selection using the mouse, the input becomes selected. This makes it easy to change. With a typed input neither an index nor registration becomes selected. How can this be made similar? SelStart and SelLength do not appear to work here. 3. With a mouse input it is easy to move the focus to the next control by making the test if cbo1.textlength 0 then go. How could this be done with a manual input without having to manually tab? I'd be grateful for advice. T.I.A. Geoff To see how the code fails with manual input, put a default combobox on a form and name it cbo1 On wksheet1 put the following data starting in cell A1 Date 10 May 2005 11 May 2005 12 May 2005 12 May 2005 12 May 2005 12 May 2005 13 May 2005 This starting in B1 Veh Reg Y123 ABC Y123 ABC Y123 ABC Y127 ABC Y129 ABC Y132 ABC Y126 ABC This starting in D1 (this is the database to populate the control) Veh Reg ?? Y123 ABC Y124 ABC Y125 ABC Y126 ABC Y127 ABC Y128 ABC Y129 ABC Y130 ABC Y131 ABC Y132 ABC And finally this into the form module Option Explicit Dim VehReg() As String Dim tbl As Range, rng As Range Dim i As Integer Dim cbo1cntr As Integer Dim firstfind Private Sub UserForm_Initialize() With UserForm1 .Left = 250 .Top = 65 .startupposition = 0 End With With cbo1 .ColumnCount = 2 .ColumnWidths = 15 & ";" & 30 .ListRows = 15 End With Set tbl = Sheets(1).Range("D1").CurrentRegion.Offset(1, 0) ReDim VehReg(0 To tbl.Rows.Count - 2, 1) For i = 0 To tbl.Rows.Count - 2 'create index VehReg(i, 0) = i Next i For i = 0 To tbl.Rows.Count - 2 'populate box VehReg(i, 1) = Sheets(1).Cells(i + 2, 4) Next i cbo1.List() = VehReg Range("A20").Select End Sub Private Sub cbo1_Change() Set rng = Sheets(1).Range("A23") 'find date, example 12/05/2005 Set rng = Sheets(1).Columns(1).Find _ (what:=CDate("12 May 2005"), _ After:=rng, _ LookIn:=xlFormulas, _ Lookat:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext) firstfind = "" If Not rng Is Nothing And Not cbo1.Value = "" And Not cbo1.Value _ cbo1.ListCount Then Do Until rng.Offset(, 1).Value = cbo1.Column(1) Or rng.Address = firstfind If firstfind = "" Then firstfind = rng.Address Set rng = Sheets(1).Columns(1).FindNext(After:=rng) Loop If rng.Offset(, 1).Value = cbo1.Column(1) Then Range(rng.Address, Cells(rng.Row, rng.Column + 1)).Select MsgBox "A vehicle record exists on that date " & vbCr & _ "Do something" Else Range("A20").Select MsgBox "No record exists " & vbCr & _ "Do something else " End If Else If Not cbo1cntr = 1 Then 'if index is greater than list cbo1cntr = 1 Range("A20").Select MsgBox "Index does not exist " cbo1.Value = "" End If End If End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox input
Steve
This is just leading to frustration and not moving the issue on at all but thank you for your attention. Geoff "STEVE BELL" wrote: Geoff, Sorry if I wasn't any help. (I meant no offense). I figured you knew how to add if statements to the code and make them work. sounds like you are looking for x = len(combobox1.value) or x = combobox1.listindex -- steveB Remove "AYN" from email to respond "Geoff" wrote in message ... Again.. Believe me I have not posted this question at the first sign of a problem. I have tried every logical test i can think of both in the change event and the exit event. The whole thing hinges on the length of string to be tested be it 1 or 2 characters of an index or the whole registration. I was hoping for specifics rather than generalities in this case. Geoff "STEVE BELL" wrote: Again - You appear to be using a combobox change event. This will fire each time you enter a single character... Use the combobox exit event to make the event wait until you are finished with the combobox. Or you can add an if then statement in the change event macro to check the value of the combobox. (or the length of the combobox value, or ....). And use this check to exit the sub routine... -- steveB Remove "AYN" from email to respond "Geoff" wrote in message ... I am aware. The prime question remains, how is it possible to prevent the do loop test whist inputting single characters. How can I stop the test after the digit 1 and wait until 10? Geoff "STEVE BELL" wrote: Geoff, You may need to look at the combo box events. The change event triggers when you either select an item in the list or when you type a single character. The exit event will trigger when you leave the combobox. -- steveB Remove "AYN" from email to respond "Geoff" wrote in message ... Hi The code seeks to establish whether data exists already on a given date and selected vehicle registration. It works ok when using the mouse to select any registration from a userform combobox list but fails sometimes with a manual input. The reason being the required string is entered and tested as a whole. To shorten the typing I have added an index column to the control. But if this index is not known, it will be necessary for the registration to be typed in as per frmMatchEntryComplete. A manual input 'failure' occurs if the target vehicle is index 10 = Y123 ABC. The Do Until Loop causes an escape as soon as 1 is typed. Though the cursor remains in the box for further input a message will have been triggered before the second digit 0 can be entered. 1. How can I amend the code to suit both mouse and manual input? 2. After making a selection using the mouse, the input becomes selected. This makes it easy to change. With a typed input neither an index nor registration becomes selected. How can this be made similar? SelStart and SelLength do not appear to work here. 3. With a mouse input it is easy to move the focus to the next control by making the test if cbo1.textlength 0 then go. How could this be done with a manual input without having to manually tab? I'd be grateful for advice. T.I.A. Geoff To see how the code fails with manual input, put a default combobox on a form and name it cbo1 On wksheet1 put the following data starting in cell A1 Date 10 May 2005 11 May 2005 12 May 2005 12 May 2005 12 May 2005 12 May 2005 13 May 2005 This starting in B1 Veh Reg Y123 ABC Y123 ABC Y123 ABC Y127 ABC Y129 ABC Y132 ABC Y126 ABC This starting in D1 (this is the database to populate the control) Veh Reg ?? Y123 ABC Y124 ABC Y125 ABC Y126 ABC Y127 ABC Y128 ABC Y129 ABC Y130 ABC Y131 ABC Y132 ABC And finally this into the form module Option Explicit Dim VehReg() As String Dim tbl As Range, rng As Range Dim i As Integer Dim cbo1cntr As Integer Dim firstfind Private Sub UserForm_Initialize() With UserForm1 .Left = 250 .Top = 65 .startupposition = 0 End With With cbo1 .ColumnCount = 2 .ColumnWidths = 15 & ";" & 30 .ListRows = 15 End With Set tbl = Sheets(1).Range("D1").CurrentRegion.Offset(1, 0) ReDim VehReg(0 To tbl.Rows.Count - 2, 1) For i = 0 To tbl.Rows.Count - 2 'create index VehReg(i, 0) = i Next i For i = 0 To tbl.Rows.Count - 2 'populate box VehReg(i, 1) = Sheets(1).Cells(i + 2, 4) Next i cbo1.List() = VehReg Range("A20").Select End Sub Private Sub cbo1_Change() Set rng = Sheets(1).Range("A23") 'find date, example 12/05/2005 Set rng = Sheets(1).Columns(1).Find _ (what:=CDate("12 May 2005"), _ After:=rng, _ LookIn:=xlFormulas, _ Lookat:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext) firstfind = "" If Not rng Is Nothing And Not cbo1.Value = "" And Not cbo1.Value _ cbo1.ListCount Then Do Until rng.Offset(, 1).Value = cbo1.Column(1) Or rng.Address = firstfind If firstfind = "" Then firstfind = rng.Address Set rng = Sheets(1).Columns(1).FindNext(After:=rng) Loop If rng.Offset(, 1).Value = cbo1.Column(1) Then Range(rng.Address, Cells(rng.Row, rng.Column + 1)).Select MsgBox "A vehicle record exists on that date " & vbCr & _ "Do something" Else Range("A20").Select MsgBox "No record exists " & vbCr & _ "Do something else " End If Else If Not cbo1cntr = 1 Then 'if index is greater than list cbo1cntr = 1 Range("A20").Select MsgBox "Index does not exist " cbo1.Value = "" End If End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable length of input area in a combobox | Excel Discussion (Misc queries) | |||
fill combobox depending on selection from another combobox | Excel Discussion (Misc queries) | |||
Horizontal Input for Combobox | Excel Discussion (Misc queries) | |||
Having problems with adding input from combobox | Excel Programming | |||
Compare input from ComboBox | Excel Programming |