Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error testing combobox to variable
Please Help!!!
When Pressing the add button it continually returns the msg ouch and 0, 2 like cnt is not incrementing Any idea how to make this work? As i said before im still very new to "programming" and im not very good at it. but im trying to figure it out.. so any assistance would be greatly apreciated Private Sub CmdAdd_Click() Dim strNewPerson As String Dim NewName As String Dim cnt As Long Dim NewParent As String Dim Mynumber As Integer Dim i As Boolean i = False NewName = TxtFirstName & "," & TxtLastName Do While cnt <= CBONames.ListCount - 1 Or i = True ' CBONames Combobox 2 For cnt = 0 To CBONames.ListCount - 1 If NewName = CBONames.List(cnt) Then ' NewName is Combobox 1 MsgBox "hello" MsgBox cnt & " " & Mynumber End If If NewName < CBONames.List(i) Then MsgBox "ouch!" MsgBox cnt & " " & Mynumber End If Next Loop End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error testing combobox to variable
HI there
The code below should give you some idea of a way to do this what i have done is write a function and call it from the button click event, it will start a loop to check the values in CboNames for a match if one is found it will show the Hello msg and then stop the function exiting the loop, if it doesn't find a match it will finish the the loop and show the Ouch msg. I'm not sure what all your variables are for so i didn't use anything new other than the X integer replacing the cnt in the msg's. Option Explicit Dim NewName As String Dim X As Integer Dim MyNumber As Integer Private Sub CmdAdd_Click() CheckCboBox 'Call this function from the button click event End Sub Function CheckCboBox() NewName = TxtFirstName.Value & "," & TxtLastName.Value For X = 0 To CboNames.ListCount - 1 If CboNames.List(X) = NewName Then 'You can add your code here to use the variable i.e. 'ComboBox2.AddItem NewName MsgBox "hello" & vbNewLine & X & " " & MyNumber Exit Function 'Add this if you want to stop searching 'if a name is found End If Next X MsgBox "ouch!" & vbNewLine & X & " " & MyNumber End Function Hope this helps Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error testing combobox to variable
On Nov 14, 5:30 pm, Incidental wrote:
HI there The code below should give you some idea of a way to do this what i have done is write a function and call it from the button click event, it will start a loop to check the values in CboNames for a match if one is found it will show the Hello msg and then stop the function exiting the loop, if it doesn't find a match it will finish the the loop and show the Ouch msg. I'm not sure what all your variables are for so i didn't use anything new other than the X integer replacing the cnt in the msg's. Option Explicit Dim NewName As String Dim X As Integer Dim MyNumber As Integer Private Sub CmdAdd_Click() CheckCboBox 'Call this function from the button click event End Sub Function CheckCboBox() NewName = TxtFirstName.Value & "," & TxtLastName.Value For X = 0 To CboNames.ListCount - 1 If CboNames.List(X) = NewName Then 'You can add your code here to use the variable i.e. 'ComboBox2.AddItem NewName MsgBox "hello" & vbNewLine & X & " " & MyNumber Exit Function 'Add this if you want to stop searching 'if a name is found End If Next X MsgBox "ouch!" & vbNewLine & X & " " & MyNumber End Function Hope this helps Steve I changed my code to this, however whenever i Click the Add button, it will add it, then msgbox me for the remaining count even with the exit function in there . am i doing something wrong? Please help :) Private Function AddingIt() Dim NewName As String Dim strNewPerson As String Dim cnt As Long NewName = TxtFirstName & "," & TxtLastName For cnt = 0 To CBONames.ListCount - 1 If CBONames.List(cnt) = NewName Then MsgBox NewName & " Is already on the list" Exit Function End If Next cnt strNewPerson = TxtFirstName & "," & TxtLastName & "," & CBOMoFa & "," & TxtChild & "," _ & TxtIssued & "," & TxtServed GetArea (strNewPerson) End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error testing combobox to variable
On Nov 15, 8:32 am, wrote:
On Nov 14, 5:30 pm, Incidental wrote: HI there The code below should give you some idea of a way to do this what i have done is write a function and call it from the button click event, it will start a loop to check the values in CboNames for a match if one is found it will show the Hello msg and then stop the function exiting the loop, if it doesn't find a match it will finish the the loop and show the Ouch msg. I'm not sure what all your variables are for so i didn't use anything new other than the X integer replacing the cnt in the msg's. Option Explicit Dim NewName As String Dim X As Integer Dim MyNumber As Integer Private Sub CmdAdd_Click() CheckCboBox 'Call this function from the button click event End Sub Function CheckCboBox() NewName = TxtFirstName.Value & "," & TxtLastName.Value For X = 0 To CboNames.ListCount - 1 If CboNames.List(X) = NewName Then 'You can add your code here to use the variable i.e. 'ComboBox2.AddItem NewName MsgBox "hello" & vbNewLine & X & " " & MyNumber Exit Function 'Add this if you want to stop searching 'if a name is found End If Next X MsgBox "ouch!" & vbNewLine & X & " " & MyNumber End Function Hope this helps Steve I changed my code to this, however whenever i Click the Add button, it will add it, then msgbox me for the remaining count even with the exit function in there . am i doing something wrong? Please help :) Private Function AddingIt() Dim NewName As String Dim strNewPerson As String Dim cnt As Long NewName = TxtFirstName & "," & TxtLastName For cnt = 0 To CBONames.ListCount - 1 If CBONames.List(cnt) = NewName Then MsgBox NewName & " Is already on the list" Exit Function End If Next cnt strNewPerson = TxtFirstName & "," & TxtLastName & "," & CBOMoFa & "," & TxtChild & "," _ & TxtIssued & "," & TxtServed GetArea (strNewPerson) End Function- Hide quoted text - - Show quoted text - I was thinking maybe this, however when i run it it just freezes, and i have to close it and re-open it, its something wrong with this code, because i took all the other code out! what am i doing thats wrong? Do While i = False Or cnt <= CBONames.ListCount For cnt = 0 To CBONames.ListCount - 1 If CBONames.List(cnt) = NewName Then MsgBox NewName & " Is already on the list" i = True End If Next cnt Loop |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error testing combobox to variable
Hi
The code below should work for you it uses pretty much the same idea as your code only using a single loop rather than nesting another loop inside the initial loop. I personally find it easier to keep loops simple as the more complex they get and the more nesting is used it becomes hard to know where you are while you are debugging your code. The main problem with you loops are that the first loop will run for ever as cnt will always be 3 due to the fact it is incremented in the inner loop this is what makes it seem as excel has locked up, it is actually running the loop endlessly you can stop the loop from running by using the Ctrl + Break keystroke to break the running code. Also worth a mention is that without an exit of the loops after the correct name is found the inner loop will continue to check the rest of the combobox contents which is unnecessary. i = False NewName = TxtFirstName & "," & TxtLastName cnt = 0 Do While i = False And cnt <= CboNames.ListCount - 1 If CboNames.List(cnt) = NewName Then MsgBox NewName & " Is already on the list" i = True Else cnt = cnt + 1 End If Loop Steve |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error testing combobox to variable
On Nov 15, 10:40 am, Incidental wrote:
Hi The code below should work for you it uses pretty much the same idea as your code only using a single loop rather than nesting another loop inside the initial loop. I personally find it easier to keep loops simple as the more complex they get and the more nesting is used it becomes hard to know where you are while you are debugging your code. The main problem with you loops are that the first loop will run for ever as cnt will always be 3 due to the fact it is incremented in the inner loop this is what makes it seem as excel has locked up, it is actually running the loop endlessly you can stop the loop from running by using the Ctrl + Break keystroke to break the running code. Also worth a mention is that without an exit of the loops after the correct name is found the inner loop will continue to check the rest of the combobox contents which is unnecessary. i = False NewName = TxtFirstName & "," & TxtLastName cnt = 0 Do While i = False And cnt <= CboNames.ListCount - 1 If CboNames.List(cnt) = NewName Then MsgBox NewName & " Is already on the list" i = True Else cnt = cnt + 1 End If Loop Steve That Worked! I appreciate it, and it helped me understand loops a little better! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Testing each cell for error | Excel Programming | |||
Variable ComboBox on Userform | Excel Programming | |||
Testing for error : was 2nd Occurence | New Users to Excel | |||
testing more than one variable in an if statement | Excel Discussion (Misc queries) |