Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code please?
Hi all I am using this code so when a person puts number into text box a it
gives the details in textbox's 2&3. How can I get this code to look in column "f" in this column I have a yes/no section. If the corresponding number is NO I want a pop up to mention this person is not financial. How do I change the code below to do this? Private Sub TextBox1_Change() Dim ans On Error Resume Next ans = Application.Match(CLng(TextBox1.Text), Range("A1:A100"), 0) If Not IsError(ans) Then TextBox2.Text = Application.Index(Range("B1:B100"), ans) TextBox3.Text = Application.Index(Range("C1:C100"), ans) R = 1 'or whatever desired row variable Sheets("sheet5").Cells(R, 1).Value = UCase(TextBox1.Text) Else UserForm2.Show End If On Error GoTo 0 End Sub Thanks again Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code please?
Hi Greg,
not sure I fully understand but try this... .... If Not IsError(ans) Then If cells(ans,6).value = "NO" Then MsgBox "Person is not Financial!" TextBox2.Text = Application.Index(Range("B1:B1*00"), ans)...etc OJ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code please?
Hi Greg
(how's sunny adelaide this morning - did you get any sleep?) how about: Private Sub TextBox1_Change() Dim ans On Error Resume Next ans = Application.Match(CLng(TextBox1.Text), Range("A1:A100"), 0) If Not IsError(ans) Then TextBox2.Text = Application.Index(Range("B1:B100"), ans) TextBox3.Text = Application.Index(Range("C1:C100"), ans) If UCase(Application.Index(Range("F1:F100"), ans)) = "NO" Then _ MsgBox "Warning: This person is not financial!" R = 1 'or whatever desired row variable Sheets("sheet5").Cells(R, 1).Value = UCase(TextBox1.Text) Else UserForm2.Show End If On Error GoTo 0 End Sub --- cheers JulieD "Greg" wrote in message ... Hi all I am using this code so when a person puts number into text box a it gives the details in textbox's 2&3. How can I get this code to look in column "f" in this column I have a yes/no section. If the corresponding number is NO I want a pop up to mention this person is not financial. How do I change the code below to do this? Private Sub TextBox1_Change() Dim ans On Error Resume Next ans = Application.Match(CLng(TextBox1.Text), Range("A1:A100"), 0) If Not IsError(ans) Then TextBox2.Text = Application.Index(Range("B1:B100"), ans) TextBox3.Text = Application.Index(Range("C1:C100"), ans) R = 1 'or whatever desired row variable Sheets("sheet5").Cells(R, 1).Value = UCase(TextBox1.Text) Else UserForm2.Show End If On Error GoTo 0 End Sub Thanks again Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code please?
Yeah Thanks Julie, Slept Well for how little I got, It is very hot here
today. Thanks Julie it works Perfectly Greg |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code please?
Thanks OJ
Greg |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code please?
you're welcome
we've got rain here today (yipeee, yipee, yay!) .. so you should get it in a day or two good luck with the rest of your excel project - it seems quite a major undertaking "Greg" wrote in message ... Yeah Thanks Julie, Slept Well for how little I got, It is very hot here today. Thanks Julie it works Perfectly Greg |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with code please?
maybe:
Private Sub TextBox1_Change() Dim lngAns As Long On Error Resume Next 'assuming Sheet1.Range("A1:A100") only have unique value lngAns = CLng(Application.WorksheetFunction.Match(CLng(Text Box1.Text), Sheet1.Range("A1:A100"), 0)) If Not IsError(ans) Then TextBox2.Text = Sheet1.Cells(lngAns, "B").Value TextBox3.Text = Sheet1.Cells(lngAns, "C").Value R = 1 'or whatever desired row variable Sheets("sheet5").Cells(R, 1).Value = UCase(TextBox1.Text) Else UserForm2.Show End If On Error GoTo 0 End Sub "Greg" wrote: Hi all I am using this code so when a person puts number into text box a it gives the details in textbox's 2&3. How can I get this code to look in column "f" in this column I have a yes/no section. If the corresponding number is NO I want a pop up to mention this person is not financial. How do I change the code below to do this? Private Sub TextBox1_Change() Dim ans On Error Resume Next ans = Application.Match(CLng(TextBox1.Text), Range("A1:A100"), 0) If Not IsError(ans) Then TextBox2.Text = Application.Index(Range("B1:B100"), ans) TextBox3.Text = Application.Index(Range("C1:C100"), ans) R = 1 'or whatever desired row variable Sheets("sheet5").Cells(R, 1).Value = UCase(TextBox1.Text) Else UserForm2.Show End If On Error GoTo 0 End Sub Thanks again Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
stubborn Excel crash when editing code with code, one solution | Excel Programming | |||
option buttons run Click code when value is changed via VBA code | Excel Programming | |||
VBA code delete code but ask for password and unlock VBA protection | Excel Programming |