Hello to everybody,


In the UserForm there is a combo that reads the content of D sheet
PaperReceipts and load the value of cell F and G in the same UserForm.
Then there is a TextBox4 (red) that is equal to the content of the cell
D, but I can change the value in the way that I can allocate less paper
than available and copy the D,F,G cell in another sheet called "Allocated"

I'm trying to implement the following code as follows:

1. update the number of D cell of the worksheet "PaperReceipts" when I
copy the entire row in sheet "Allocate" from the UserForm: Column G -
Textbox4 (the value of D cannot be negative)

2. From the paper "Allocate" do the reverse, if I remove the value in
column C I would like to add the value to column D of the
sheet"PaperReceipts"

Any help is really appreciated.

The Excel file can be viewed at:

https://skydrive.live.com/redir.aspx...181B 9505!115


Option Explicit
Private wk As Workbook
Private sh As Worksheet
Private sh2 As Worksheet

Private Sub CommandButton3_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim lUltRiga As Long
Dim lng As Long
'
Set wk = ThisWorkbook
With wk
Set sh = .Worksheets("PaperReceipts")
Set sh2 = .Worksheets("Allocated")
End With

With sh
lUltRiga = .Range("D" & _
.Rows.Count).End(xlUp).Row
For lng = 1 To lUltRiga
Me.ComboBox1.AddItem .Cells(lng, 4).Value
Next
End With
End Sub


Private Sub ComboBox1_Click()
Call mCerca(Me.ComboBox1.Text)
End Sub


Private Sub CommandButton2_Click()
Dim lNuovaRiga As Long
Dim lNewValue As Integer
On Error GoTo Err_Execute
With sh2
If Len(Me.TextBox3.Text) = 0 Then
MsgBox "Nessun valore inserito."
Exit Sub
End If
lNuovaRiga = .Range("A" & _
.Rows.Count).End(xlUp).Row + 1
'inser. datas
.Cells(lNuovaRiga, 1).Value = Me.ComboBox1.Text
.Cells(lNuovaRiga, 2).Value = Me.TextBox4.Text
.Cells(lNuovaRiga, 3).Value = Me.TextBox3.Text
End With
MsgBox "All matching data has been copied."
Exit Sub
Err_Execute:
MsgBox "An error occurred."
End Sub

'search routine
Private Sub mCerca(ByVal vValore As Variant)

'
Dim rng As Range
With sh
'search value column D
Set rng = _
.Range("D:D").Find( _
What:=vValore, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
End With
'chek result
If rng Is Nothing Then
'if no
MsgBox "Dato non trovato"
'clean TextBox
Call mPulisciTextBox
Else
Me.TextBox2.Text = rng.Offset(0, 3).Value
Me.TextBox3.Text = rng.Offset(0, 3).Value
Me.TextBox4.Text = rng.Offset(0, 2).Value
End If
Set rng = Nothing
End Sub

Private Sub mPulisciTextBox()
With Me
.TextBox2.Text = ""
.TextBox3.Text = ""
.TextBox4.Text = ""
End With
End Sub

Private Sub UserForm_Terminate()
Set sh2 = Nothing
Set sh = Nothing
Set wk = Nothing
End Sub