how to write a textbox value into a excel sheet data.

[jananen]

I have a userform where once i keyin the part no(textbox1),description(textbox 2) and supplier(Textbox3) will auto populate. And when i click the update button after keyin Date(textbox4) & Quantity(textbox5) the data's will be updated in worksheet4. For example the data's will be updated in cell A2,B2,C2,D2 & E2 and the next update will be in A3,B3,C3,D3 & E3. and this goes on. This is working fine.

Im stucked where, I have some datas in Worksheet5. Column A part numbers and column B descriptions.

Now i want the value in Quantity(textbox5) to be updated in worksheet5 Column C depends on the value on part no(textbox1)
once i click the update button in my userform. And the following update on the same part no must go into the next column.

Please advice,

Below is my userform and codings.

[/IMG]

Option Explicit





Private Sub cmdClose_Click()
Unload Me
End Sub




Private Sub cmdUpdate_Click()
'--------------------------------------
' Copy values in textboxes into excel sheet
'--------------------------------------
    Dim x As Integer
    Dim nextrow As Range


           


            'to check empty textboxes
        Set nextrow = Sheet4.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0)
        For x = 1 To 5
        If Me.Controls("stock" & x).Value = "" Then
            MsgBox "Missing data"
    Exit Sub
    End If
    Next


            'add values if previous criteria matches
        For x = 1 To 5
        nextrow = Me.Controls("stock" & x).Value
        Set nextrow = nextrow.Offset(0, 1)
    Next
            'To clear textboxes
        For x = 1 To 5
        Me.Controls("stock" & x).Value = ""
    Next
    Exit Sub
    
    
End Sub


Private Sub Stock1_Change()
'--------------------------------------
' Check database for entry in Textbox1 _
  and if in DB then populate other TB _
'--------------------------------------
    
    Dim rFound As Range
    Dim wsData As Worksheet
    Dim sID As String
    Dim j As Integer
    
    Worksheets("Stock Update").Activate
    Set wsData = Sheets("Stock Update")
    With wsData
    
        ' Get the item number in sID
        sID = Me.Stock1.Value
        
        ' check column A of the datasheet for the entry
        Set rFound = Columns("A").Find(what:=sID, _
                                    after:=.Cells(1, 1))
        ' if found, process. else quit
        If Not rFound Is Nothing Then ' This checks that rFound is set to an object _
                                        and not 'nothing'
            ' Load the details in the text boxes
        For j = 2 To 3
                Me.Controls("stock" & j).Value = rFound.Offset(0, j - 1).Value
            Next j
           
         End If
        
    End With
End Sub


Private Sub UserForm_Initialize()
Stock1.SetFocus
End Sub

 

[dmt32]

Hi,
You describe your forms functions as:

“data's will be updated in worksheet4”

but as far as I can see, your cmdUpdate_Click code ADDS the displayed record to a new row only on Sheet4 in Columns F to J Yet, you describe data being placed to Columns A to E

Is the forms purpose to Add a new record to Sheet4 OR do you want to Update an existing record on Sheet4 as forms caption suggests as well as add the additional functionality of updating Sheet5? And can confirm which columns data is being placed?


Dave.