WorksheetFunction

[kuvailondra]

Hi All,

I have come across a code which am having hard time with. I need the userform information to be entered into another sheet, which is not working. I need the Macro to update the table with new/edit previous information.

Dim id As Integer, i As Integer, j As Integer, flag As Boolean

Sub GetData()

If IsNumeric(UserForm1.TextBox1.Value) Then
flag = False
i = 0
id = UserForm1.TextBox1.Value

Do While Cells(i + 1, 1).Value <> ""

If Cells(i + 1, 1).Value = id Then
flag = True
For j = 2 To 3
UserForm1.Controls("TextBox" & j).Value = Cells(i + 1, j).Value
Next j
End If

i = i + 1

Loop

If flag = False Then
For j = 2 To 3
UserForm1.Controls("TextBox" & j).Value = ""
Next j
End If

Else
ClearForm
End If

End Sub

Sub ClearForm()

For j = 1 To 3
UserForm1.Controls("TextBox" & j).Value = ""
Next j

End Sub

Sub EditAdd()

Dim emptyRow As Long

If UserForm1.TextBox1.Value <> "" Then
flag = False
i = 0
id = UserForm1.TextBox1.Value
emptyRow = WorksheetFunction.CountA(Range(Worksheets("Uniform Table").Range("A1:D82"))) + 1

Do While Cells(i + 1, 1).Value <> ""

If Cells(i + 1, 1).Value = id Then
flag = True
For j = 2 To 3
Cells(i + 1, j).Value = UserForm1.Controls("TextBox" & j).Value
Next j
End If

i = i + 1

Loop

If flag = False Then
For j = 1 To 3
Cells(emptyRow, j).Value = UserForm1.Controls("TextBox" & j).Value
Next j
End If

End If

End Sub

 

[DanteAmor]

Sorry, but I am not understanding.
You can go more calmly.
In the userform1 "Stock count" you have 3 labels:
- Tote
- Quantity
- ID
What will you capture in each field?
In which sheet are you going to save and in which column does each field go?

 

[kuvailondra]

Sorry, but I am not understanding.
You can go more calmly.
In the userform1 "Stock count" you have 3 labels:
- Tote
- Quantity
- ID
What will you capture in each field?
In which sheet are you going to save and in which column does each field go?

So under "stock count" or userform1, i need the information added there to reflect on the table available on sheet 2 "Uniform table".
i have already created a table for this information to reflect to, for example,

any information added to Tote should be added to Uniform table under the column Asset number. If the number that is added during when user uses form is already in place in the table, then Quantity and ID should change.

If the Tote / Asset number is new, then new information should be added to new row.

So;

Tote = Asset Number
Quantity = Quantity
ID = User ID

 

[DanteAmor]

ok, Another question.
Does the "Asset Number" relate to column A of the "Count" sheet?

 

[DanteAmor]

Try this, for the userform1

Dim sh1 As Worksheet, sh2 As Worksheet


Private Sub CommandButton1_Click()
  'EditAdd
  Dim f As Range
  'VALIDATIONS
  If ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then
    MsgBox "Enter valid Tote"
    ComboBox1.SetFocus
    Exit Sub
  End If
  If TextBox2.Value = "" Or Not IsNumeric(TextBox2.Value) Then
    MsgBox "Enter Quantity"
    TextBox2.SetFocus
    Exit Sub
  End If
  If TextBox3.Value = "" Then
    MsgBox "Enter User Id"
    TextBox3.SetFocus
    Exit Sub
  End If
  '
  Set f = sh1.Range("A:A").Find(ComboBox1.Value, , xlValues, xlWhole)
  If f Is Nothing Then
    lr = sh1.Range("A" & Rows.Count).End(xlUp).Row + 1
    sh1.Range("A" & lr).Value = ComboBox1.Value
    sh1.Range("C" & lr).Value = Val(TextBox2.Value)
    sh1.Range("D" & lr).Value = TextBox3.Value
  Else
    sh1.Range("C" & f.Row).Value = Val(TextBox2.Value)
    sh1.Range("D" & f.Row).Value = TextBox3.Value
  End If
  MsgBox "Record updated"
  ComboBox1.Value = ""
  TextBox2.Value = ""
  TextBox3.Value = ""
End Sub

check the file

https://www.dropbox.com/s/tg0uzbfbfy9uwgf/Uniform Tracker v3.xlsm?dl=0

 

[kuvailondra]

Try this, for the userform1

Dim sh1 As Worksheet, sh2 As Worksheet


Private Sub CommandButton1_Click()
  'EditAdd
  Dim f As Range
  'VALIDATIONS
  If ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then
    MsgBox "Enter valid Tote"
    ComboBox1.SetFocus
    Exit Sub
  End If
  If TextBox2.Value = "" Or Not IsNumeric(TextBox2.Value) Then
    MsgBox "Enter Quantity"
    TextBox2.SetFocus
    Exit Sub
  End If
  If TextBox3.Value = "" Then
    MsgBox "Enter User Id"
    TextBox3.SetFocus
    Exit Sub
  End If
  '
  Set f = sh1.Range("A:A").Find(ComboBox1.Value, , xlValues, xlWhole)
  If f Is Nothing Then
    lr = sh1.Range("A" & Rows.Count).End(xlUp).Row + 1
    sh1.Range("A" & lr).Value = ComboBox1.Value
    sh1.Range("C" & lr).Value = Val(TextBox2.Value)
    sh1.Range("D" & lr).Value = TextBox3.Value
  Else
    sh1.Range("C" & f.Row).Value = Val(TextBox2.Value)
    sh1.Range("D" & f.Row).Value = TextBox3.Value
  End If
  MsgBox "Record updated"
  ComboBox1.Value = ""
  TextBox2.Value = ""
  TextBox3.Value = ""
End Sub

check the file

https://www.dropbox.com/s/tg0uzbfbfy9uwgf/Uniform Tracker v3.xlsm?dl=0

You Sir are a genius you have even added msgbox. This is brilliant. i see what you have done with sh1 and sh2.

I believe for the Stock In (userform2) i will use Userform1 but adds on top of current Quantity and changes User. for Stock out (userform3) Same as Userform1 but deducts from current Quantity and changes User. So i will need to change the following?

Set f = sh1.Range("A:A").Find(ComboBox1.Value, , xlValues, xlWhole)
If f Is Nothing Then
lr = sh1.Range("A" & Rows.Count).End(xlUp).Row + 1
sh1.Range("A" & lr).Value = ComboBox1.Value
sh1.Range("C" & lr).Value = Val(TextBox2.Value)
sh1.Range("D" & lr).Value = TextBox3.Value



Thank you.

 

[DanteAmor]

Try to make the adjustments in the other forms, if you have any difficulty I will gladly review it.