[RESOLVED] Code Dont work

This Code wont actually enter the data in the workbook any ideas?

Code:

---

Private Sub Userform_Initialize()
    With ComboBox3         
        .AddItem ""
        .AddItem "Mr"
        .AddItem "Ms"
        .AddItem "Mrs"
        .AddItem "Miss"
    End With
    With ComboBox2         
        .AddItem ""
        .AddItem "Mon"
        .AddItem "Tue"
        .AddItem "Wed"
        .AddItem "Thu"
        .AddItem "Fri"
    End With
    With ComboBox1         
        .AddItem ""
        .AddItem "P"
        .AddItem "R"
        .AddItem "W"
    End With
    TextBox1.Value = Worksheets("Main").Range("A1").Value
    TextBox2.Value = Worksheets("Main").Range("B1").Value
    TextBox3.Value = Worksheets("Main").Range("C1").Value
    TextBox4.Value = Worksheets("Main").Range("D1").Value
    TextBox5.Value = Worksheets("Main").Range("E1").Value
    End Sub
Private Sub CommandButton1_Click()
    If Len(Trim(TextBox1.Text)) = o Then
        MsgBox "**."
        TextBox1.SetFocus
        Exit Sub
    End If
    If Len(Trim(TextBox2.Text)) = o Then
        MsgBox "**."
        TextBox2.SetFocus
        Exit Sub
    End If
    If Len(Trim(TextBox3.Text)) = o Then
        MsgBox "**."
        TextBox3.SetFocus
        Exit Sub
    End If
        If Len(Trim(TextBox4.Text)) = o Then
        MsgBox "**."
        TextBox4.SetFocus
        Exit Sub
    End If
        If Len(Trim(TextBox5.Text)) = o Then
        MsgBox "**."
        TextBox5.SetFocus
        Exit Sub
    End If
    If Len(Trim(ComboBox1.Text)) = o Then
        MsgBox "**."
        ComboBox1.SetFocus
        Exit Sub
    End If
        If Len(Trim(ComboBox2.Text)) = o Then
        MsgBox "**."
        ComboBox2.SetFocus
        Exit Sub
    End If
        If Len(Trim(ComboBox3.Text)) = o Then
        MsgBox "**."
        ComboBox3.SetFocus
        Exit Sub
    End If
    Set fnd = Sheets("Database").UsedRange.Find(TextBox3.Text)
      If Not fnd Is Nothing Then
        MsgBox TextBox3.Text & " **"
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
        ComboBox1.Text = ""
        ComboBox2.Text = ""
        ComboBox3.Text = ""
        Exit Sub
    End If
    Select Case ComboBox1.Text
    Case "Mr": col = 6
    Case "Ms": col = 6
    Case "Mrs": col = 6
    Case "Miss": col = 6
    End Select
    Select Case ComboBox2.Text
    Case "Mon": col = 7
    Case "Tue": col = 7
    Case "Wed": col = 7
    Case "Thu": col = 7
    Case "Fri": col = 7
    End Select
    Select Case ComboBox3.Text
    Case "P": col = 8
    Case "R": col = 8
    Case "W": col = 8
    End Select

    lrow = nrow + 49

    Do Until IsEmpty(Worksheets("Database").Cells(nrow, col))
        nrow = nrow + 1
    Loop

    Worksheets("Database").Cells(nrow, col).Value = TextBox1.Value
    Worksheets("Database").Cells(nrow, col + 1).Value = TextBox2.Value
    Worksheets("Database").Cells(nrow, col + 2).Value = TextBox3.Value
    Worksheets("Database").Cells(nrow, col + 3).Value = TextBox4.Value
    Worksheets("Database").Cells(nrow, col + 4).Value = TextBox5.Value
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
    ComboBox1.Text = ""
    ComboBox2.Text = ""
    ComboBox3.Text = ""
    TextBox1.SetFocus
End Sub

---

Quote:

---

If Len(Trim(TextBox1.Text)) = o Then

---

a length will never = o
try 0

other than that what actually happens?

I added some comments in your code

Code:

---

Private Sub Userform_Initialize()
    With ComboBox3         
        .AddItem ""
        .AddItem "Mr"
        .AddItem "Ms"
        .AddItem "Mrs"
        .AddItem "Miss"
    End With
    With ComboBox2         
        .AddItem ""
        .AddItem "Mon"
        .AddItem "Tue"
        .AddItem "Wed"
        .AddItem "Thu"
        .AddItem "Fri"
    End With
    With ComboBox1         
        .AddItem ""
        .AddItem "P"
        .AddItem "R"
        .AddItem "W"
    End With
    TextBox1.Value = Worksheets("Main").Range("A1").Value
    TextBox2.Value = Worksheets("Main").Range("B1").Value
    TextBox3.Value = Worksheets("Main").Range("C1").Value
    TextBox4.Value = Worksheets("Main").Range("D1").Value
    TextBox5.Value = Worksheets("Main").Range("E1").Value
    End Sub
Private Sub CommandButton1_Click()
    If Len(Trim(TextBox1.Text)) = o Then 'I guess you wnated the check wether the Length is 0 (spoken ZERO) not o (spoken o like ossi osborborne)
        MsgBox "**."
        TextBox1.SetFocus
        Exit Sub
    End If
    If Len(Trim(TextBox2.Text)) = o Then
        MsgBox "**."
        TextBox2.SetFocus
        Exit Sub
    End If
    If Len(Trim(TextBox3.Text)) = o Then
        MsgBox "**."
        TextBox3.SetFocus
        Exit Sub
    End If
        If Len(Trim(TextBox4.Text)) = o Then
        MsgBox "**."
        TextBox4.SetFocus
        Exit Sub
    End If
        If Len(Trim(TextBox5.Text)) = o Then
        MsgBox "**."
        TextBox5.SetFocus
        Exit Sub
    End If
    If Len(Trim(ComboBox1.Text)) = o Then
        MsgBox "**."
        ComboBox1.SetFocus
        Exit Sub
    End If
        If Len(Trim(ComboBox2.Text)) = o Then
        MsgBox "**."
        ComboBox2.SetFocus
        Exit Sub
    End If
        If Len(Trim(ComboBox3.Text)) = o Then
        MsgBox "**."
        ComboBox3.SetFocus
        Exit Sub
    End If
    Set fnd = Sheets("Database").UsedRange.Find(TextBox3.Text)
      If Not fnd Is Nothing Then
        MsgBox TextBox3.Text & " **"
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
        ComboBox1.Text = ""
        ComboBox2.Text = ""
        ComboBox3.Text = ""
        Exit Sub
    End If
    Select Case ComboBox1.Text
    Case "Mr": col = 6
    Case "Ms": col = 6
    Case "Mrs": col = 6
    Case "Miss": col = 6
    End Select 'Why use a Select Case if you are doing the same in ALL cases?
    Select Case ComboBox2.Text
    Case "Mon": col = 7
    Case "Tue": col = 7
    Case "Wed": col = 7
    Case "Thu": col = 7
    Case "Fri": col = 7
    End Select
    Select Case ComboBox3.Text
    Case "P": col = 8
    Case "R": col = 8
    Case "W": col = 8
    End Select

    lrow = nrow + 49

    Do Until IsEmpty(Worksheets("Database").Cells(nrow, col))
        nrow = nrow + 1
    Loop

    Worksheets("Database").Cells(nrow, col).Value = TextBox1.Value 'put a breakpoint in here and look for the values of your TextBoxes manually!
    Worksheets("Database").Cells(nrow, col + 1).Value = TextBox2.Value
    Worksheets("Database").Cells(nrow, col + 2).Value = TextBox3.Value
    Worksheets("Database").Cells(nrow, col + 3).Value = TextBox4.Value
    Worksheets("Database").Cells(nrow, col + 4).Value = TextBox5.Value
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
    ComboBox1.Text = ""
    ComboBox2.Text = ""
    ComboBox3.Text = ""
    TextBox1.SetFocus
End Sub

---

Quote:

---

Originally Posted by opus

I added some comments in your code

Code:

---

Private Sub Userform_Initialize()
    With ComboBox3         
        .AddItem ""
        .AddItem "Mr"
        .AddItem "Ms"
        .AddItem "Mrs"
        .AddItem "Miss"
    End With
    With ComboBox2         
        .AddItem ""
        .AddItem "Mon"
        .AddItem "Tue"
        .AddItem "Wed"
        .AddItem "Thu"
        .AddItem "Fri"
    End With
    With ComboBox1         
        .AddItem ""
        .AddItem "P"
        .AddItem "R"
        .AddItem "W"
    End With
    TextBox1.Value = Worksheets("Main").Range("A1").Value
    TextBox2.Value = Worksheets("Main").Range("B1").Value
    TextBox3.Value = Worksheets("Main").Range("C1").Value
    TextBox4.Value = Worksheets("Main").Range("D1").Value
    TextBox5.Value = Worksheets("Main").Range("E1").Value
    End Sub
Private Sub CommandButton1_Click()
    If Len(Trim(TextBox1.Text)) = o Then 'I guess you wnated the check wether the Length is 0 (spoken ZERO) not o (spoken o like ossi osborborne)
        MsgBox "**."
        TextBox1.SetFocus
        Exit Sub
    End If
    If Len(Trim(TextBox2.Text)) = o Then
        MsgBox "**."
        TextBox2.SetFocus
        Exit Sub
    End If
    If Len(Trim(TextBox3.Text)) = o Then
        MsgBox "**."
        TextBox3.SetFocus
        Exit Sub
    End If
        If Len(Trim(TextBox4.Text)) = o Then
        MsgBox "**."
        TextBox4.SetFocus
        Exit Sub
    End If
        If Len(Trim(TextBox5.Text)) = o Then
        MsgBox "**."
        TextBox5.SetFocus
        Exit Sub
    End If
    If Len(Trim(ComboBox1.Text)) = o Then
        MsgBox "**."
        ComboBox1.SetFocus
        Exit Sub
    End If
        If Len(Trim(ComboBox2.Text)) = o Then
        MsgBox "**."
        ComboBox2.SetFocus
        Exit Sub
    End If
        If Len(Trim(ComboBox3.Text)) = o Then
        MsgBox "**."
        ComboBox3.SetFocus
        Exit Sub
    End If
    Set fnd = Sheets("Database").UsedRange.Find(TextBox3.Text)
      If Not fnd Is Nothing Then
        MsgBox TextBox3.Text & " **"
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
        ComboBox1.Text = ""
        ComboBox2.Text = ""
        ComboBox3.Text = ""
        Exit Sub
    End If
    Select Case ComboBox1.Text
    Case "Mr": col = 6
    Case "Ms": col = 6
    Case "Mrs": col = 6
    Case "Miss": col = 6
    End Select 'Why use a Select Case if you are doing the same in ALL cases?
    Select Case ComboBox2.Text
    Case "Mon": col = 7
    Case "Tue": col = 7
    Case "Wed": col = 7
    Case "Thu": col = 7
    Case "Fri": col = 7
    End Select
    Select Case ComboBox3.Text
    Case "P": col = 8
    Case "R": col = 8
    Case "W": col = 8
    End Select

    lrow = nrow + 49

    Do Until IsEmpty(Worksheets("Database").Cells(nrow, col))
        nrow = nrow + 1
    Loop

  Worksheets("Database").Cells(nrow, col).Value = TextBox1.Value 'put a breakpoint in here and look for the values of your TextBoxes manually!
    Worksheets("Database").Cells(nrow, col + 1).Value = TextBox2.Value
    Worksheets("Database").Cells(nrow, col + 2).Value = TextBox3.Value
    Worksheets("Database").Cells(nrow, col + 3).Value = TextBox4.Value
    Worksheets("Database").Cells(nrow, col + 4).Value = TextBox5.Value
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
    ComboBox1.Text = ""
    ComboBox2.Text = ""
    ComboBox3.Text = ""
    TextBox1.SetFocus
End Sub

---

---

the line higlighted red is the line that comes back faulty the len trim arguments work there is no problem with them, when i try to enter data its the line in red that has an issue.

I guess its that a TextBox should have a Text not a Value property!
Wghat do you get for TextBox1.Value in cae of this error?

1.) You ignored what Pete said....

Replace o by 0

2) What is the value of nrow and col at the time of error

Insert this one line before the red line and tell me what do you get?

vb Code:

Msgbox "nrow: " & nrow & ", " & "col: " & col

Quote:

---

I guess its that a TextBox should have a Text not a Value property!

---

same thing in forms2 textboxes

what value do nrow and col have at that point?

also it would be really helpful if you told what error you are getting

application defined or object defined error, thats the error code then when i debug i go to that line in red.
there is no problem with the len trim code, koolsid you helped me write that code and thats what u put, they work i have tested it.

its just when i press command button1 the error come up.

When i press comman button 1 i want the data to be placed on the next row down in the spreadsheet
no end in the sheet.....just one after the other never replaced or overwritten.

textbox1 = col 1
textbox2 = col 2
textbox3 = col 3
textbox4 = col 4
textbox5 = col 5
combobox1 = col 6
combobox2 = col 7
combobox3 = col 8

Quote:

---

koolsid you helped me write that code

---

Which Thread are you refering to? Because I am sure I have used 0 (Zero) and not o (the alphabet)

you wrote it private mate on msn, so no reference here but anyway that no problem ill try it too please

ok... the idea behind

Len(Trim(TextBox4.Text)) is to check if the length of the text in the textbox is Zero or not...

ok skip the len trim idea it works fine no problems i changed the values to zero..... and still works the same....

now the problem is when i fill the form fully, and then try to enter the data, is comes back with this line

Code:

---

Do Until IsEmpty(Worksheets("Database").Cells(nrow, col))
        nrow = nrow + 1

---

now i have set all the values to correct.... which i believe and this is always happening....
if i delete it doesnt work properly no more....

when i press command button 1 i want textbox1 - textbox 5 and combobox1 - combobox3 to have there data stored in the workbook.

What is the value of nrow and col at this moment?

im not sure what u mean by nrow...

the first rwo will start at row 1 if thats what you mean

ok now i get you so somewhere in here i need to set the nrow argument so the sheet knows where to start, well the first row is 1....
but where do i apply this code?

Code:

---

Private Sub Userform_Initialize()
    'Populate textboxes with initial values
    TextBox1.SetFocus
    TextBox1.Value = Worksheets("Main").Range("A1").Value
    TextBox2.Value = Worksheets("Main").Range("B1").Value
    TextBox3.Value = Worksheets("Main").Range("C1").Value
    TextBox4.Value = Worksheets("Main").Range("D1").Value
    TextBox5.Value = Worksheets("Main").Range("E1").Value
    TextBox6.Value = Worksheets("Main").Range("F1").Value
    TextBox7.Value = Worksheets("Main").Range("G1").Value
    TextBox8.Value = Worksheets("Main").Range("H1").Value
    End Sub
Private Sub CommandButton1_Click()
    If Len(Trim(TextBox1.Text)) = 0 Then
        MsgBox "The Customers Surname Is Missing, Please Amend."
        TextBox1.SetFocus
        Exit Sub
    End If
    If Len(Trim(TextBox2.Text)) = 0 Then
        MsgBox "The Customers NINO Is Missing, Please Amend."
        TextBox2.SetFocus
        Exit Sub
    End If
    If Len(Trim(TextBox3.Text)) = 0 Then
        MsgBox "The Customers Actual Signing Time Is Mising, Please Amend."
        TextBox3.SetFocus
        Exit Sub
    End If
        If Len(Trim(TextBox4.Text)) = 0 Then
        MsgBox "The Customers Arrival Time Is Missing, Please Amend."
        TextBox4.SetFocus
        Exit Sub
    End If
        If Len(Trim(TextBox5.Text)) = 0 Then
        MsgBox "The Customers Supposed Signing Date (Most Recent) Is Missing, Please Amend."
        TextBox5.SetFocus
        Exit Sub
    End If
    If Len(Trim(TextBox6.Text)) = 0 Then
        MsgBox "The Customers Title Is Missing, Please Amend."
        TextBox6.SetFocus
        Exit Sub
    End If
        If Len(Trim(TextBox7.Text)) = 0 Then
        MsgBox "The Customers Signing Day Is Missing, Please Amend."
        TextBox7.SetFocus
        Exit Sub
    End If
        If Len(Trim(TextBox8.Text)) = 0 Then
        MsgBox "The Customers Cycle Is Missing, Please Amend."
        TextBox8.SetFocus
        Exit Sub
    End If
    Set fnd = Sheets("Database").UsedRange.Find(TextBox3.Text)
      If Not fnd Is Nothing Then
        MsgBox TextBox3.Text & " : Is Already Stored Within The Database, Please Do A Search From The Main Page To Check Whether The Current Data Is Correct!"
        TextBox1.Text = ""
        TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        TextBox5.Text = ""
        TextBox6.Text = ""
        TextBox7.Text = ""
        TextBox8.Text = ""
        Exit Sub
    End If
   
   
    'etc
    lrow = nrow + 10000
    'Save textbox values in Sheet2 and clear
    'get next empty row
    Do Until IsEmpty(Worksheets("Database").Cells(nrow, col))
        nrow = nrow + 1
    Loop
    ' maybe check here if nrow is still in correct timeslot
    'Save textbox values in Sheet2 and clear
    'get next empty row

    ' maybe check here if nrow is still in correct timeslot
   
    Worksheets("Database").Cells(nrow, col).Value = TextBox1.Value
    Worksheets("Database").Cells(nrow, col + 1).Value = TextBox2.Value
    Worksheets("Database").Cells(nrow, col + 2).Value = TextBox3.Value
    Worksheets("Database").Cells(nrow, col + 3).Value = TextBox4.Value
    Worksheets("Database").Cells(nrow, col + 4).Value = TextBox5.Value
    Worksheets("Database").Cells(nrow, col + 5).Value = TextBox6.Value
    Worksheets("Database").Cells(nrow, col + 6).Value = TextBox7.Value
    Worksheets("Database").Cells(nrow, col + 7).Value = TextBox8.Value
    'clear all
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
    TextBox6.Value = ""
    TextBox7.Value = ""
    TextBox8.Value = ""
    TextBox1.SetFocus
End Sub

---

anywhere before the error
you will get error if nrow or col are empty or zero
or greater than the number of rows or columns available, 65536 rows or 255 cols
higher in 2007

you can also get the next empty row without looping over every used row
nrow = range("a65536").End(xlUp).Row +1
which will also avoid the error at that point

Quote:

---

IsEmpty(Worksheets("Database").Cells(nrow, col))

---

Your code will fail here as the value of nrow will be 0 as I don't see you setting it's value any where in the code above.

Try this

vb Code:

Private Sub CommandButton1_Click()
    nrow=1
    If Len(Trim(TextBox1.Text)) = 0 Then

right no problem so can i use select case for textbox arguments

select case textbox1.text
case "": nrow = 1 yes?

ok i have tried that.....koolsid and still firing back to that line in question...

not a good choice of place to put it as if there is anything in text1 it will not be called
what about col?

Ok upload your workbook... Let me debug it for you by testing it...

Attachment 73477

here ay go mate

Ok I see...

Try this.

Code:

---

Private Sub Userform_Initialize()
    'Populate textboxes with initial values
    TextBox1.SetFocus
    TextBox1.Value = Worksheets("Main").Range("A1").Value
    TextBox2.Value = Worksheets("Main").Range("B1").Value
    TextBox3.Value = Worksheets("Main").Range("C1").Value
    TextBox4.Value = Worksheets("Main").Range("D1").Value
    TextBox5.Value = Worksheets("Main").Range("E1").Value
    TextBox6.Value = Worksheets("Main").Range("F1").Value
    TextBox7.Value = Worksheets("Main").Range("G1").Value
    TextBox8.Value = Worksheets("Main").Range("H1").Value
    End Sub
Private Sub CommandButton1_Click()
    If Len(Trim(TextBox1.Text)) = 0 Then
        MsgBox "The Customers Surname Is Missing, Please Amend."
        TextBox1.SetFocus
        Exit Sub
    End If
    If Len(Trim(TextBox2.Text)) = 0 Then
        MsgBox "The Customers NINO Is Missing, Please Amend."
        TextBox2.SetFocus
        Exit Sub
    End If
    If Len(Trim(TextBox3.Text)) = 0 Then
        MsgBox "The Customers Actual Signing Time Is Mising, Please Amend."
        TextBox3.SetFocus
        Exit Sub
    End If
        If Len(Trim(TextBox4.Text)) = 0 Then
        MsgBox "The Customers Arrival Time Is Missing, Please Amend."
        TextBox4.SetFocus
        Exit Sub
    End If
        If Len(Trim(TextBox5.Text)) = 0 Then
        MsgBox "The Customers Supposed Signing Date (Most Recent) Is Missing, Please Amend."
        TextBox5.SetFocus
        Exit Sub
    End If
    If Len(Trim(TextBox6.Text)) = 0 Then
        MsgBox "The Customers Title Is Missing, Please Amend."
        TextBox6.SetFocus
        Exit Sub
    End If
        If Len(Trim(TextBox7.Text)) = 0 Then
        MsgBox "The Customers Signing Day Is Missing, Please Amend."
        TextBox7.SetFocus
        Exit Sub
    End If
        If Len(Trim(TextBox8.Text)) = 0 Then
        MsgBox "The Customers Cycle Is Missing, Please Amend."
        TextBox8.SetFocus
        Exit Sub
    End If
    Set fnd = Sheets("Database").UsedRange.Find(TextBox3.Text)
      If Not fnd Is Nothing Then
        MsgBox TextBox3.Text & " : Is Already Stored Within The Database, Please Do A Search From The Main Page To Check Whether The Current Data Is Correct!"
        Exit Sub
    End If
   
    '~~> As Pete Suggested
    nrow = Range("a65536").End(xlUp).Row + 1
    col = 1
   
    Worksheets("Database").Cells(nrow, col).Value = TextBox1.Value
    Worksheets("Database").Cells(nrow, col + 1).Value = TextBox2.Value
    Worksheets("Database").Cells(nrow, col + 2).Value = TextBox3.Value
    Worksheets("Database").Cells(nrow, col + 3).Value = TextBox4.Value
    Worksheets("Database").Cells(nrow, col + 4).Value = TextBox5.Value
    Worksheets("Database").Cells(nrow, col + 5).Value = TextBox6.Value
    Worksheets("Database").Cells(nrow, col + 6).Value = TextBox7.Value
    Worksheets("Database").Cells(nrow, col + 7).Value = TextBox8.Value
    'clear all
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
    TextBox6.Value = ""
    TextBox7.Value = ""
    TextBox8.Value = ""
    TextBox1.SetFocus
End Sub

---

I would also recommend to use a cancel button so you can exit the userform?

cancel button still necessary ?
as i when the form is closed which closes the workbook i have an autosave in place?

Hmmm... I was unable to close the form.... probably it was because I was doing it from the design mode...

I have few other suggestion to make your form more professional. Let me know if you are interested...

yes ok always keen to find new stuff

Something like this... Of course you can make it more presentable... This is something which I did in 10 mins...

You can also loop thru the textboxes using 'Typeof' and check if they are not empty instead of writing 8 different sects of code...

Similarly you can use the same code to clear the text boxes...

Search the forum on Typeof. Plenty of code which will tell you on how to loop thru specific controls in a form

Hope this helps...

that is a lot better to be honest....

i created the close button and that work but now i need to cancel out the cross on the form if i can....

aswell i have created a search function that will search the entries and bring up the latest one.

so the further down the database you go the new the entries will be in there,
so i want the search to descend when finding a value.
when it finds on it returns the data to the listbox below.
or better still it returns the data back to all the textboxes.

possible i know but a bit stuck :S

ok a little stuck i have this error when searching and trying to re populate the textboxes.

method or data member not found

Code:

---

Private Sub Search()
Set r = Sheets("Database").UsedRange.Find(TextBox3.Value)
        If Not fnd Is Nothing Then
       
    TextBox1.AddItem r.Offset(0, 0)
    TextBox2.AddItem r.Offset(0, 1)
    TextBox3.AddItem r.Offset(0, 2)
    TextBox4.AddItem r.Offset(0, 3)
    TextBox5.AddItem r.Offset(0, 4)
    TextBox6.AddItem r.Offset(0, 5)
    ComboBox1.AutoWordSelect = True
    ComboBox2.AutoWordSelect = True
    ComboBox3.AutoWordSelect = True
   
        TextBox1.Value = ""
        TextBox1.SetFocus
Else
        MsgBox "Try Again.", vbCritical
       
        TextBox1.Value = ""
        ListBox1.Value = ""
        TextBox1.SetFocus
        End If
End Sub
Private Sub CommandButton2_Click()
Search
End Sub

---

Quote:

---

i created the close button and that work but now i need to cancel out the cross on the form if i can....

---

Rob has a tutorial on that... Search the Codebank...

Quote:

---

aswell i have created a search function that will search the entries and bring up the latest one.

so the further down the database you go the new the entries will be in there,
so i want the search to descend when finding a value.
when it finds on it returns the data to the listbox below.
or better still it returns the data back to all the textboxes.

possible i know but a bit stuck :S

---

Search for ".Find" in Office development. I have given few examples on how to go about it...

point me in the right direction please dude, perhaps a link of a post you know of, i have been trawling through for sometime now and dont seem to have come accross anything in the line of what i want yet.

Are you by any chance referring to Rob's link that I mentioned?

for post #29
textboxes have no additem method, only combo and list boxes

no looking for the find post, where search on the value of a certain textbox woul search the required sheet descending order and display all connected cells in all the other textboxes

the find code you used worked
the error (method or data member not found) was trying to additem to textbox

i definitely have something missing

Code:

---

Private Sub Search()
Set r = Sheets("Database").UsedRange.Find(TextBox3.Value)
        If Not fnd Is Nothing Then
       
    TextBox1 = r.Offset(0, -2)
    TextBox2 = r.Offset(0, -1)
    TextBox3 = r.Offset(0)
    TextBox4 = r.Offset(1)
    TextBox5 = r.Offset(2)
    TextBox6 = r.Offset(3)
    ComboBox1 = r.Offset(4)
    ComboBox2 = r.Offset(5)
    ComboBox3 = r.Offset(6)
   
        TextBox1.Value = ""
        TextBox1.SetFocus

---

the line in red is geting error "object not found now"
i do believe that im not running the textbox arguments correctly

How have you defined fnd?

it should be
if not r is nothing then

r is nothing
if nothing was returned by find

the textboxes should work correctly now, but comboboxes will require additem
though why do you clear textbox1 again?

thats old code not stripped.....my bad ok ill give it a shot with r

Code:

---

Private Sub Search()
Set r = Sheets("Database").UsedRange.Find(TextBox3.Value)
        If Not r Is Nothing Then
       
    TextBox1 = r.Offset(0, -2)
    TextBox2 = r.Offset(0, -1)
    TextBox3 = r.Offset(0)
    TextBox4 = r.Offset(0, 1)
    TextBox5 = r.Offset(0, 2)
    TextBox6 = r.Offset(0, 3)
    ComboBox1.AddItem r.Offset(0, 4)
    ComboBox2.AddItem r.Offset(0, 5)
    ComboBox3.AddItem r.Offset(0, 6)
   
        TextBox1.SetFocus
Else
        MsgBox "NO NO NO!", vbCritical
       
        TextBox1.Value = ""
        ListBox1.Value = ""
        TextBox1.SetFocus
        End If
End Sub

---

ok runs fine upto the comboboxes the additem function fails,