kuvailondra

New Member
Joined
Oct 13, 2019
Messages
7
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
 
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?
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
ok, Another question.
Does the "Asset Number" relate to column A of the "Count" sheet?
 
Upvote 0
Try this, for the userform1


Code:
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
 
Upvote 0
Try this, for the userform1


Code:
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.
 
Upvote 0
Try to make the adjustments in the other forms, if you have any difficulty I will gladly review it.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top