Solved: UserForm - Need Help!

[Minderis]

I have UserForm with TextBox1 (label: Name) field and two OpionButtons (Confirm/Cancel).
When I call userform and write in TextBox1 field name (for ex. John) and click OptionButton1 (Confirm) - I need that name John will be pasted on Sheet2, Colummn C:C first blank cell. But if in colummn C:C already name John exists, I need get MsgBox("Error, this name already exists") and egt ability to enter another name.

Colummn C:C is names colummn.

May you help me?

[Simon Lloyd]

Try this, i've not tested as i just wrote it off the cuff in this window but it should workIf Application.worksheetfunction.Sumif(Sheets("Sheet2").Range("C1:C" & Sheets("Sheet2").Range("C" & rows.count).end(xlup).row),Me.TextBox1.Value)>0 Then
Msgbox "The name " & Me.Textbox1.Value & " already exists", vbOkOnly,"Duplicate Name"
Else
Sheets("Sheet2").Range("C" & rows.count).end(xlup).Offset(1,0)=me.textbox1.value
end if

Sent using my Samsung Galaxy S2

[mdmackillop]

Option Compare Text

Private Sub CommandButton1_Click()
Dim c as range
Set c = Sheets(2).Range("C:C").Find(TextBox1, lookat:=xlWhole)
If Not c Is Nothing Then
MsgBox TextBox1 & " exists"
TextBox1 = ""
TextBox1.SetFocus
else
Sheets(2).Range("C" & Rows.Count).End(xlUp)(2)=Textbox1.value
End If

End Sub

[Bob Phillips]

Sent using my Samsung Galaxy S2

Jeez, writing VBA coode on a smartphone. Are you mad :)?

[Simon Lloyd]

You have to work with the tools you're presented with :)

It seemed like a quick fix so just dashed it off, it's not too bad as it's quite a large smartphone!

[Minderis]

Option Compare Text

Private Sub CommandButton1_Click()
Dim c as range
Set c = Sheets(2).Range("C:C").Find(TextBox1, lookat:=xlWhole)
If Not c Is Nothing Then
MsgBox TextBox1 & " exists"
TextBox1 = ""
TextBox1.SetFocus
else
Sheets(2).Range("C" & Rows.Count).End(xlUp)(2)=Textbox1.value
End If

End Sub


It does not work (run time error 9), something wrong with Set c = ....

[Minderis]

Try this, i've not tested as i just wrote it off the cuff in this window but it should workIf Application.worksheetfunction.Sumif(Sheets("Sheet2").Range("C1:C" & Sheets("Sheet2").Range("C" & rows.count).end(xlup).row),Me.TextBox1.Value)>0 Then
Msgbox "The name " & Me.Textbox1.Value & " already exists", vbOkOnly,"Duplicate Name"
Else
Sheets("Sheet2").Range("C" & rows.count).end(xlup).Offset(1,0)=me.textbox1.value
end if
Sent using my Samsung Galaxy S2

This works fine. How can I extend the code, if I want use 2 criterias?
I will use 2 (TextBox1 as name, TextBox2 as ID number) fields in userform.
TextBox1 range C:C, TextBox2 range D:D. Both on Sheet2.
If where are name or ID written on spreedsheet, it must not let to writte new information.
I was try "If Aplicationxxxx And Aplicationxxx", but it dont work.
How modify the code?

[GTO]

It does not work (run time error 9), something wrong with Set c = ....

Presuming you have not altered codenames, change:

Sheets(2).Range(...etc...

To:

Sheet2Range(...etc...

...in both places. I am presuming the wb only has one sheet, based on the error.

Hope thta helps,

Mark

[Minderis]

Actual name of Sheet2 is "Duomenys".
I was try: Sheets("Duomenys").Range....
I was try: Sheets("Duomenys")Range...
I was try: Duomenys.Range...
I was try: DuomenysRange...

nothing, it dont work

[Simon Lloyd]

This should do what you wantIf Application.WorksheetFunction.SumIf(Sheets("Sheet2").Range("C1:C" & Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Row), Me.textbox1.Value) > 0 _
Or Application.WorksheetFunction.SumIf(Sheets("Sheet2").Range("D1:D" & Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Row), Me.textbox2.Value) > 0 Then
MsgBox "The name or ID already exists", vbOKOnly, "Duplicate Name"
Else
Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = Me.textbox1.Value
Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Offset(0, 1) = Me.textbox2.Value
End IfAs a word of advice for future posts, don't ask for one solution and then say "how can i make it so that...?", it's annoying, it's best to ask for the solution you want :)

[Minderis]

If all 3 textboxes fields are not empty and textboxes values is not written on spreadsheet, then write its values on spreadsheet.

If all 3 textboxes fields are empty or textboxes values is written on spreadsheet, then message "Fill field or this entries already is written"
p.s. to avoid dublicate information

Where is my mistakes?

Private Sub cmdAdd_Click()

Dim strLastRow As Integer
Dim numeris As Integer
Dim vardas As Integer
Dim adresas As Integer
numeris = Application.WorksheetFunction.SumIf(Sheets("Vartotojai").Range("E3:E" & Sheets("Vartotojai").Range("E" & Rows.Count).End(xlUp).Row), Me.TextBox2.Value)
vardas = Application.WorksheetFunction.SumIf(Sheets("Vartotojai").Range("D3:D" & Sheets("Vartotojai").Range("D" & Rows.Count).End(xlUp).Row), Me.TextBox1.Value)
adresas = Application.WorksheetFunction.SumIf(Sheets("Vartotojai").Range("F3:F" & Sheets("Vartotojai").Range("F" & Rows.Count).End(xlUp).Row), Me.TextBox3.Value)

strLastRow = xlLastRow("Vartotojai")

With UserForm2

If (.TextBox1.Value <> vbNullString And .TextBox2.Value <> vbNullString And _
.TextBox3.Value <> vbNullString And numeris = 0 And vardas = 0 And adresas = 0) Then
Sheets("Vartotojai").Cells(strLastRow + 1, 4).Value = UserForm2.TextBox1.Value
Sheets("Duomenys").Cells(strLastRow + 1, 4).Value = UserForm2.TextBox1.Value
Sheets("Vartotojai").Cells(strLastRow + 1, 5).Value = UserForm2.TextBox2.Value
Sheets("Vartotojai").Cells(strLastRow + 1, 6).Value = UserForm2.TextBox3.Value
strLastRow = strLastRow + 1


UserForm2.ListBox1.RowSource = "Vartotojai!D3:F" & strLastRow

.TextBox1.Value = vbNullString
.TextBox2.Value = vbNullString
.TextBox3.Value = vbNullString

Else
MsgBox "Áveskite duomenis"

End If
End With

End Sub

[Simon Lloyd]

Your mistake?, your mistake is to keep changing the goal posts!, firstly you ask for one textbox...etc, then you ask for what if two textboxes and now three!!!!!!!!!!!!!

[Minderis]

Patience please. I am new in VBA, but I dont want that somebody do job for me. I want to learn basics in VBA, so I variable diferent situations and trying to understand.
I thought, if I understand how to work with one TextBox, then I can work with 2, 5 or 10 TextBoxes (the principle is the same). This is not the essence of question. The essence is in the code structure.
I rephrased the question: how to correct the line of code - if the TextBox1 is not blank or entry has not been recorded in the database - record TextBox1.Value to cell, otherwise give msgbox "error".

The essence:
If (.TextBox1.Value <> vbNullString And '.TextBox1.Value is not recorded in datebase' Then
Sheets("Vartotojai").Cells(strLastRow + 1, 4).Value = UserForm2.TextBox1.Value

[Simon Lloyd]

Just follow the code i gave you above for the IF .... OR.....THEN you can simply add an OR in the same format.

[Minderis]

Anyway Application.WorksheetFunction.SumIf gives me 0. Does not matter if there textbox1 record in the spreadshhet or no, Sumif just gives 0. Somewhere is mistake in the code (maybe structure, variables or ....)
May I share my excel workbook here, and you look at it?

[Simon Lloyd]

sure, you can attach it here

[Minderis]

Here is my workbook. Working form is UserForm2. UserForm1 is not required, its just like example for me.So dont give attention to userform1.
Problem with code in UserForm2.

I think you will see what i want and where is the problem.

CommandButton Sukurti/Pasalinti vartotoja.
Before adding record, it must check for unique value, if record exists, gave msgbox, if no - make new record.

[Simon Lloyd]

You had your textboxes mixed up and your ranges mixed up, here's the working code (just your code fixed)Private Sub cmdAdd_Click()
Dim strLastRow As Integer
Dim numeris As Integer
Dim vardas As Integer
Dim adresas As Integer
numeris = Application.WorksheetFunction.SumIf(Sheets("Vartotojai").Range("D3:D" & Sheets("Vartotojai").Range("D" & Rows.Count).End(xlUp).Row), Me.TextBox1.Value)
vardas = Application.WorksheetFunction.SumIf(Sheets("Vartotojai").Range("E3:E" & Sheets("Vartotojai").Range("E" & Rows.Count).End(xlUp).Row), Me.TextBox2.Value)
adresas = Application.WorksheetFunction.SumIf(Sheets("Vartotojai").Range("F3:F" & Sheets("Vartotojai").Range("F" & Rows.Count).End(xlUp).Row), Me.TextBox3.Value)
strLastRow = xlLastRow("Vartotojai")
With UserForm2
If .TextBox1.Value <> vbNullString And .TextBox2.Value <> vbNullString And _
.TextBox3.Value <> vbNullString And numeris = 0 And vardas = 0 And adresas = 0 Then
Sheets("Vartotojai").Cells(strLastRow + 1, 4).Value = UserForm2.TextBox1.Value
Sheets("Duomenys").Cells(strLastRow + 1, 4).Value = UserForm2.TextBox1.Value
Sheets("Vartotojai").Cells(strLastRow + 1, 5).Value = UserForm2.TextBox2.Value
Sheets("Duomenys").Cells(strLastRow + 1, 5).Value = UserForm2.TextBox2.Value
Sheets("Vartotojai").Cells(strLastRow + 1, 6).Value = UserForm2.TextBox3.Value
Sheets("Duomenys").Cells(strLastRow + 1, 6).Value = UserForm2.TextBox3.Value
strLastRow = strLastRow + 1


UserForm2.ListBox1.RowSource = "Vartotojai!D3:F" & strLastRow

.TextBox1.Value = vbNullString
.TextBox2.Value = vbNullString
.TextBox3.Value = vbNullString

Else
MsgBox "Áveskite duomenis"
End If
End With
End Sub

[Minderis]

I replace the code, and it still dont work. I can add dublicate records. Did you try this code on my workbook?

[Simon Lloyd]

yes i tried it on your workbook, my fault, just change the SUMIf for COUNTIF and you'll be fine!

[Minderis]

Thanks, it works !

[Simon Lloyd]

Please mark your thread solved by going to thread tools and mark solved.