Userform load and edit values from worksheet

[denExc]

Hello everyone,

i have created a Userform with a few textboxes. In my worksheet i have a the values, in sheet2, sheet1 has the button. The problem i have is, that the values in combobox1 are taking only from the sheet what is currently open. How can i make it, that excel takes always the values which are on the sheet with that given name? On the other combobox its working fine.

https://www.sendspace.com/file/mb9udv

the second thing is: I have more Textboxes. What i want to do is, to search the whole excelsheet by entering a name (Textbox4) or a number (TextBox5). I have the code for it and its working well. The only problem is with the second search criteria, the number. I just copied the code getData and editAdd and replaced the name of TextBox4 into Textbox5. I thought it would be an easy solution, but excel is crashing. Can someone help me writing the code better?

if its not already in the excel file, here it is. Take a look at the marked and underlined codeline. The "X" is just a very easy solution but not a good one. I want to search the whole excel sheet. That X means, excel will search till it finds an X somewhere.

Sub EditAdd()
Dim id As String
Dim i As Integer
Dim j As Integer
Dim flag As Boolean
Dim emptyRow As Long
If UserForm1.TextBox4.Value <> "" Then
flag = False
i = 1
id = UserForm1.TextBox4.Value
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
Do While Cells(i + 1, 1).Value <> "x"
If Cells(i + 1, 4).Value = id Then
flag = True
For j = 2 To 20
Cells(i + 1, 1).Value = UserForm1.Controls("TextBox1").Value
Cells(i + 1, 2).Value = UserForm1.Controls("TextBox2").Value


Next j
End If
i = i + 1
Loop
If flag = False Then
For j = 1 To 20
Cells(emptyRow, 1).Value = UserForm1.Controls("TextBox1").Value
Cells(emptyRow, 1).Value = UserForm1.Controls("TextBox2").Value

Next j
End If
End If
End Sub

Sub GetData()
Dim id As String
Dim i As Integer
Dim j As Integer
Dim flag As Boolean

If IsNumeric(UserForm1.TextBox4.Value) Then
flag = False
i = 1
id = UserForm1.TextBox4.Value
Do While Cells(i + 1, 1).Value <> "x"
If Cells(i + 1, 4).Value = id Then
flag = True
For j = 2 To 6
UserForm1.Controls("TextBox1").Value = Cells(i + 1, 1).Value
UserForm1.Controls("TextBox2").Value = Cells(i + 1, 2).Value

Next j
End If
i = i + 1
Loop
If flag = False Then
For j = 2 To 6
UserForm1.Controls("TextBox1").Value = ""
UserForm1.Controls("TextBox2").Value = ""
Next j
End If
If flag = False Then
End If
Else
ClearForm
End If
End Sub

 

[denExc]

ok i think its not easy. I want to ask if anyone can help me just with this question, i am trying it for weeks and found no solution. Look at this code line:

DoWhile Cells(i + 1, 1).Value <> "x"

This doesnt work when i dont write a X somewhere in the excel sheet and when i dont write the X in the Code it doesnt work when i have empty cells.

The code makes a search, the textboxes should be filled with the values from the excel sheet. Can anyone please help?

regards