Private Sub UserForm_Initialize()
ActiveSheet.Unprotect
[HOME!M3] = [HOME!M3] + 1
End Sub



Private Sub UserForm_Terminate()
Sheets("HOME").Select
    Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.Visible = True
ThisWorkbook.Save
End Sub

Option Explicit
Option Compare Text
Public rig As Long
Private Sub CommandButton1_Click()
Dim i As Integer
Dim ur As Long
ur = Worksheets("Foglio1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To 7
    Worksheets("Foglio1").Cells(ur + 1, i) = Me.Controls("TextBox" & i).Value
Next i
Worksheets("Foglio1").Cells(ur + 1, 8) = Me.ComboBox1.Value
If Me.OptionButton1 = True Then
    Worksheets("Foglio1").Cells(ur + 1, 9) = "SI"
    Else
    Worksheets("Foglio1").Cells(ur + 1, 9) = "NO"
End If
Worksheets("Foglio1").Cells(ur + 1, 10) = Me.TextBox9.Value
For i = 10 To 11
    Worksheets("Foglio1").Cells(ur + 1, i + 1) = Me.Controls("TextBox" & i).Value
Next i
Worksheets("Foglio1").Cells(ur + 1, 14) = Me.TextBox13.Value
Worksheets("Foglio1").Cells(ur + 1, 15) = Me.TextBox14.Value
End Sub

Private Sub CommandButton2_Click()
Dim i As Integer
Dim cli As String
'Dim rig As Long
Call CreaElencoUnivoco
cli = UserForm2.ComboBox1.Value
rig = Trovariga(Worksheets("Foglio1").Range("a1:a10"), cli)
For i = 1 To 7
    Me.Controls("TextBox" & i).Value = Worksheets("Foglio1").Cells(rig, i)
Next i
Me.ComboBox1.Value = Worksheets("Foglio1").Cells(rig, 8)
Me.TextBox9.Value = Worksheets("Foglio1").Cells(rig, 10)
Me.TextBox10.Value = Worksheets("Foglio1").Cells(rig, 11)
Me.TextBox11.Value = Worksheets("Foglio1").Cells(rig, 12)
Me.TextBox13.Value = Worksheets("Foglio1").Cells(rig, 14)
Me.TextBox14.Value = Worksheets("Foglio1").Cells(rig, 15)
If Worksheets("Foglio1").Cells(rig, 9) = "SI" Then
    Me.OptionButton1.Value = True
    Else
    Me.OptionButton2.Value = True
End If
End Sub

Private Sub CommandButton4_Click()
UserForm1.Hide
End Sub

Private Sub CommandButton3_Click()
Dim ctrl As Control
For Each ctrl In UserForm1.Controls
    If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
        ctrl.Value = ""
    End If
Next ctrl
End Sub

Private Sub CommandButton5_Click()
Dim j As Integer
For j = 1 To 7
    Worksheets("Foglio1").Cells(rig, j) = Me.Controls("TextBox" & j).Value
Next j
Worksheets("Foglio1").Cells(rig, 8) = Me.ComboBox1.Value
For j = 10 To 12
    Worksheets("Foglio1").Cells(rig, j) = Me.Controls("TextBox" & j - 1).Value
Next j
Worksheets("Foglio1").Cells(rig, 14) = Me.TextBox13.Value
Worksheets("Foglio1").Cells(rig, 15) = Me.TextBox14.Value
If Me.OptionButton1.Value = True Then
    Worksheets("Foglio1").Cells(rig, 9) = "SI"
    Else
    Worksheets("Foglio1").Cells(rig, 9) = "NO"
End If
End Sub

Private Sub CommandButton1_Click()
If Me.ComboBox1.Value = "" Then
    MsgBox "Effettuare scelta cliente"
    Exit Sub
End If
UserForm2.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
        If CloseMode = 0 Then Cancel = True
End Sub

unction Trovariga(Tabella_Dati As Range, parola As Variant) As Variant
    If parola = "" Then
        Trovariga = ""
     Exit Function
    End If
    Trovariga = Tabella_Dati.Find(parola, LookAt:=xlWhole).Row
End Function

Sub CreaElencoUnivoco()
 Dim CL As Range, Intervallo As Range, Elenco As New Collection
 Dim Valori As Variant
 Dim cnt As Long
 cnt = Worksheets("Foglio1").Cells(Rows.Count, 1).End(xlUp).Row
 Set Intervallo = Worksheets("Foglio1").Range("a1:a" & cnt)
 Set Intervallo = Intervallo.Offset(1, 0).Resize(Intervallo.Rows.Count - 1, Intervallo.Columns.Count)
 On Error Resume Next
 For Each CL In Intervallo
Elenco.Add CL.Value, CStr(CL.Value)
 Next
 On Error GoTo 0
 With UserForm2
.ComboBox1.Clear
For Each Valori In Elenco
.ComboBox1.AddItem Valori
Next
End With
 UserForm2.Show
 End Sub