ho tentato di adattare "http://www.maurogsc.eu/esempiforum12/gestionefogliodauserform.zip"

ho tentato di adattare "http://www.maurogsc.eu/esempiforum12/gestionefogliodauserform.zip" alle mie esigenze, lavorando su un'area che va da (a1:ai100) purtroppo la mia scarsa esperienza in VBA non mi consente di risolvere il problema gestisce tutto bene sino alla colonna 9 poi non riesco a far apparire i pati da modificare. vi allego il listato da me modificato, spero vogliate aiutarmi.  Comunque graziea chiunque voglia aiutarmi
p.s. le righe con il simbolo nota sono quelle che non funzionano

Option Explicit

Public sh As Worksheet
Dim lRiga As Long

Private Sub CommandButton5_Click()
    With sh
        lRiga = lRiga + 1
        .Range("A" & lRiga).Value = Me.TextBox1.Text
        .Range("B" & lRiga).Value = Me.TextBox2.Text
        .Range("C" & lRiga).Value = Me.TextBox3.Text
        .Range("D" & lRiga).Value = Me.TextBox4.Text
        .Range("E" & lRiga).Value = Me.TextBox5.Text
        .Range("F" & lRiga).Value = Me.TextBox6.Text
        .Range("G" & lRiga).Value = Me.TextBox7.Text
        .Range("H" & lRiga).Value = Me.TextBox8.Text
        .Range("I" & lRiga).Value = Me.TextBox9.Text
                              
                              
        .Range("J" & lRiga).Value = Me.TextBox10.Text
'        .Range("K" & lRiga).Value = Me.TextBox11.Text
'        .Range("L" & lRiga).Value = Me.TextBox12.Text
'        .Range("M" & lRiga).Value = Me.TextBox13.Text
'        .Range("N" & lRiga).Value = Me.TextBox14.Text
'        .Range("O" & lRiga).Value = Me.TextBox15.Text
'        .Range("P" & lRiga).Value = Me.TextBox16.Text
'        .Range("Q" & lRiga).Value = Me.TextBox17.Text
'        .Range("R" & lRiga).Value = Me.TextBox18.Text
'        .Range("S" & lRiga).Value = Me.TextBox19.Text
'        .Range("T" & lRiga).Value = Me.TextBox20.Text
'        .Range("U" & lRiga).Value = Me.TextBox21.Text
'        .Range("V" & lRiga).Value = Me.TextBox22.Text
'        .Range("W" & lRiga).Value = Me.TextBox23.Text
'        .Range("X" & lRiga).Value = Me.TextBox24.Text
'        .Range("Y" & lRiga).Value = Me.TextBox25.Text
'        .Range("Z" & lRiga).Value = Me.TextBox26.Text
'        .Range("AA" & lRiga).Value = Me.TextBox27.Text
'        .Range("AB" & lRiga).Value = Me.TextBox28.Text
'        .Range("AC" & lRiga).Value = Me.TextBox29.Text
'        .Range("AD" & lRiga).Value = Me.TextBox30.Text
'        .Range("AE" & lRiga).Value = Me.TextBox31.Text
'        .Range("AF" & lRiga).Value = Me.TextBox32.Text
'        .Range("AG" & lRiga).Value = Me.TextBox33.Text
'        .Range("AH" & lRiga).Value = Me.TextBox34.Text
'        .Range("AI" & lRiga).Value = Me.TextBox35.Text
                              
               
    End With
    Call mCaricaListBox
End Sub

Private Sub CommandButton6_Click()
On Error GoTo RigaErrore
    Dim lng As Long
    With sh
        For lng = 2 To lRiga
            If .Range("A" & lng).Value = _
                Me.ListBox1.List(Me.ListBox1.ListIndex, 0) Then
                .Range("B" & lng).Value = Me.TextBox2.Text
                .Range("C" & lng).Value = Me.TextBox3.Text
                .Range("D" & lng).Value = Me.TextBox4.Text
                .Range("E" & lng).Value = Me.TextBox5.Text
                .Range("F" & lng).Value = Me.TextBox6.Text
                .Range("G" & lng).Value = Me.TextBox7.Text
                .Range("H" & lng).Value = Me.TextBox8.Text
                .Range("I" & lng).Value = Me.TextBox9.Text
               
               
                .Range("J" & lng).Value = Me.TextBox10.Text
'                .Range("K" & lng).Value = Me.TextBox11.Text
'                .Range("L" & lng).Value = Me.TextBox12.Text
'                .Range("M" & lng).Value = Me.TextBox13.Text
'                .Range("N" & lng).Value = Me.TextBox14.Text
'                .Range("O" & lng).Value = Me.TextBox15.Text
'                .Range("P" & lng).Value = Me.TextBox16.Text
'                .Range("Q" & lng).Value = Me.TextBox17.Text
'                .Range("R" & lng).Value = Me.TextBox18.Text
'                .Range("S" & lng).Value = Me.TextBox19.Text
'                .Range("T" & lng).Value = Me.TextBox20.Text
'                .Range("U" & lng).Value = Me.TextBox21.Text
'                .Range("V" & lng).Value = Me.TextBox22.Text
'                .Range("W" & lng).Value = Me.TextBox23.Text
'                .Range("X" & lng).Value = Me.TextBox24.Text
'                .Range("Y" & lng).Value = Me.TextBox25.Text
'                .Range("Z" & lng).Value = Me.TextBox26.Text
'                .Range("AA" & lng).Value = Me.TextBox27.Text
'                .Range("AB" & lng).Value = Me.TextBox28.Text
'                .Range("AC" & lng).Value = Me.TextBox29.Text
'                .Range("AD" & lng).Value = Me.TextBox30.Text
'                .Range("AE" & lng).Value = Me.TextBox31.Text
'                .Range("AF" & lng).Value = Me.TextBox32.Text
'                .Range("AG" & lng).Value = Me.TextBox33.Text
'                .Range("AH" & lng).Value = Me.TextBox34.Text
'                .Range("AI" & lng).Value = Me.TextBox35.Text
               
               
                Exit For
            End If
        Next
    End With
   
RigaChiusura:
    Call mCaricaListBox
    Exit Sub
   
RigaErrore:
    If Err.Number = 381 Then
        MsgBox "Selezionare la riga da modificare", vbOKOnly + vbInformation, "Attenzione"
    Else
        MsgBox Err.Number & vbNewLine & Err.Description
    End If
    Resume RigaChiusura
   
End Sub

Private Sub CommandButton8_Click()
On Error GoTo RigaErrore
    Dim lng As Long
    Dim lRisposta As Long
    lRisposta = MsgBox("Eliminare la riga selezionata?", vbYesNo + vbQuestion, "Attenzione")
    If lRisposta = vbYes Then
        With sh
            For lng = lRiga To 2 Step -1
                If .Range("A" & lng).Value = _
                    Me.ListBox1.List(Me.ListBox1.ListIndex, 0) Then
                    .Rows(lng).EntireRow.Delete
                    Exit For
                End If
            Next
        End With
        Call mCaricaListBox
        Call mPulisciTextBox
    End If
   
RigaChiusura:
    Exit Sub
   
RigaErrore:
    If Err.Number = 381 Then
        MsgBox "Selezionare la riga da eliminare", vbOKOnly + vbInformation, "Attenzione"
    Else
        MsgBox Err.Number & vbNewLine & Err.Description
    End If
    Resume RigaChiusura
End Sub

Private Sub CommandButton9_Click()
    Call mPulisciTextBox
End Sub

Private Sub mPulisciTextBox()
    With Me
        .TextBox1.Text = ""
        .TextBox2.Text = ""
        .TextBox3.Text = ""
        .TextBox4.Text = ""
        .TextBox5.Text = ""
        .TextBox6.Text = ""
        .TextBox7.Text = ""
        .TextBox8.Text = ""
        .TextBox9.Text = ""
       
        .TextBox10.Text = ""
'        .TextBox11.Text = ""
'        .TextBox12.Text = ""
'        .TextBox13.Text = ""
'        .TextBox14.Text = ""
'        .TextBox15.Text = ""
'        .TextBox16.Text = ""
'        .TextBox17.Text = ""
'        .TextBox18.Text = ""
'        .TextBox19.Text = ""
'        .TextBox20.Text = ""
'        .TextBox21.Text = ""
'        .TextBox22.Text = ""
'        .TextBox23.Text = ""
'        .TextBox24.Text = ""
'        .TextBox25.Text = ""
'        .TextBox26.Text = ""
'        .TextBox27.Text = ""
'        .TextBox28.Text = ""
'        .TextBox29.Text = ""
'        .TextBox30.Text = ""
'        .TextBox31.Text = ""
'        .TextBox32.Text = ""
'        .TextBox33.Text = ""
'        .TextBox34.Text = ""
'        .TextBox35.Text = ""
       
       
    End With
End Sub

Private Sub ListBox1_Click()
    With Me
        .TextBox1.Text = .ListBox1.List(.ListBox1.ListIndex, 0)
        .TextBox2.Text = .ListBox1.List(.ListBox1.ListIndex, 1)
        .TextBox3.Text = .ListBox1.List(.ListBox1.ListIndex, 2)
        .TextBox4.Text = .ListBox1.List(.ListBox1.ListIndex, 3)
        .TextBox5.Text = .ListBox1.List(.ListBox1.ListIndex, 4)
        .TextBox6.Text = .ListBox1.List(.ListBox1.ListIndex, 5)
        .TextBox7.Text = .ListBox1.List(.ListBox1.ListIndex, 6)
        .TextBox8.Text = .ListBox1.List(.ListBox1.ListIndex, 7)
        .TextBox9.Text = .ListBox1.List(.ListBox1.ListIndex, 8)
       
        .TextBox10.Text = .ListBox1.List(.ListBox1.ListIndex, 9)
'        .TextBox11.Text = .ListBox1.List(.ListBox1.ListIndex, 10)
'        .TextBox12.Text = .ListBox1.List(.ListBox1.ListIndex, 11)
'        .TextBox13.Text = .ListBox1.List(.ListBox1.ListIndex, 12)
'        .TextBox14.Text = .ListBox1.List(.ListBox1.ListIndex, 13)
'        .TextBox15.Text = .ListBox1.List(.ListBox1.ListIndex, 14)
'        .TextBox16.Text = .ListBox1.List(.ListBox1.ListIndex, 15)
'        .TextBox17.Text = .ListBox1.List(.ListBox1.ListIndex, 16)
'        .TextBox18.Text = .ListBox1.List(.ListBox1.ListIndex, 17)
'        .TextBox19.Text = .ListBox1.List(.ListBox1.ListIndex, 18)
'        .TextBox20.Text = .ListBox1.List(.ListBox1.ListIndex, 19)
'        .TextBox21.Text = .ListBox1.List(.ListBox1.ListIndex, 20)
'        .TextBox22.Text = .ListBox1.List(.ListBox1.ListIndex, 21)
'        .TextBox23.Text = .ListBox1.List(.ListBox1.ListIndex, 22)
'        .TextBox24.Text = .ListBox1.List(.ListBox1.ListIndex, 23)
'        .TextBox25.Text = .ListBox1.List(.ListBox1.ListIndex, 24)
'        .TextBox26.Text = .ListBox1.List(.ListBox1.ListIndex, 25)
'        .TextBox27.Text = .ListBox1.List(.ListBox1.ListIndex, 26)
'        .TextBox28.Text = .ListBox1.List(.ListBox1.ListIndex, 27)
'        .TextBox29.Text = .ListBox1.List(.ListBox1.ListIndex, 28)
'        .TextBox30.Text = .ListBox1.List(.ListBox1.ListIndex, 29)
'        .TextBox31.Text = .ListBox1.List(.ListBox1.ListIndex, 30)
'        .TextBox32.Text = .ListBox1.List(.ListBox1.ListIndex, 31)
'        .TextBox33.Text = .ListBox1.List(.ListBox1.ListIndex, 32)
'        .TextBox34.Text = .ListBox1.List(.ListBox1.ListIndex, 33)
'        .TextBox35.Text = .ListBox1.List(.ListBox1.ListIndex, 34)
       
        
       
    End With
End Sub
Private Sub TextBox1_Change()
End Sub
Private Sub TextBox2_Change()
End Sub
Private Sub TextBox3_Change()
End Sub
Private Sub TextBox4_Change()
End Sub
Private Sub TextBox5_Change()
End Sub
Private Sub TextBox6_Change()
End Sub
Private Sub TextBox7_Change()
End Sub
Private Sub TextBox8_Change()
End Sub
Private Sub TextBox9_Change()
End Sub
Private Sub TextBox10_Change()
End Sub
Private Sub TextBox11_Change()
End Sub
Private Sub TextBox12_Change()
End Sub
Private Sub TextBox13_Change()
End Sub
Private Sub TextBox14_Change()
End Sub
Private Sub TextBox15_Change()
End Sub
Private Sub TextBox16_Change()
End Sub
Private Sub TextBox17_Change()
End Sub
Private Sub TextBox18_Change()
End Sub
Private Sub TextBox19_Change()
End Sub
Private Sub TextBox20_Change()
End Sub
Private Sub TextBox21_Change()
End Sub
Private Sub TextBox22_Change()
End Sub
Private Sub TextBox23_Change()
End Sub
Private Sub TextBox24_Change()
End Sub
Private Sub TextBox25_Change()
End Sub
Private Sub TextBox26_Change()
End Sub
Private Sub TextBox27_Change()
End Sub
Private Sub TextBox28_Change()
End Sub
Private Sub TextBox29_Change()
End Sub
Private Sub TextBox30_Change()
End Sub
Private Sub TextBox31_Change()
End Sub
Private Sub TextBox32_Change()
End Sub
Private Sub TextBox33_Change()
End Sub
Private Sub TextBox34_Change()
End Sub
Private Sub TextBox35_Change()
End Sub


Private Sub UserForm_Initialize()
    Set sh = ThisWorkbook.Worksheets("Registro")
    With Me.ListBox1
        .ColumnCount = 35
        .ColumnWidths = "80;30;30;30;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20;20"
    End With
    Call mCaricaListBox
End Sub

Private Sub mCaricaListBox()
    Dim lng As Long
    Dim lCont As Long
    lCont = 0
    With sh
        Me.ListBox1.Clear
        lRiga = .Range("A" & .Rows.Count).End(xlUp).Row
        For lng = 2 To lRiga
            Me.ListBox1.AddItem
            Me.ListBox1.List(lCont, 0) = .Range("A" & lng).Value
            Me.ListBox1.List(lCont, 1) = .Range("B" & lng).Value
            Me.ListBox1.List(lCont, 2) = .Range("C" & lng).Value
            Me.ListBox1.List(lCont, 3) = .Range("D" & lng).Value
            Me.ListBox1.List(lCont, 4) = .Range("E" & lng).Value
            Me.ListBox1.List(lCont, 5) = .Range("F" & lng).Value
            Me.ListBox1.List(lCont, 6) = .Range("G" & lng).Value
            Me.ListBox1.List(lCont, 7) = .Range("H" & lng).Value
            Me.ListBox1.List(lCont, 8) = .Range("I" & lng).Value
           
            Me.ListBox1.List(lCont, 9) = .Range("J" & lng).Value
'            Me.ListBox1.List(lCont, 10) = .Range("K" & lng).Value
'            Me.ListBox1.List(lCont, 11) = .Range("L" & lng).Value
'            Me.ListBox1.List(lCont, 12) = .Range("M" & lng).Value
'            Me.ListBox1.List(lCont, 13) = .Range("N" & lng).Value
'            Me.ListBox1.List(lCont, 14) = .Range("O" & lng).Value
'            Me.ListBox1.List(lCont, 15) = .Range("P" & lng).Value
'            Me.ListBox1.List(lCont, 16) = .Range("Q" & lng).Value
'            Me.ListBox1.List(lCont, 17) = .Range("R" & lng).Value
'            Me.ListBox1.List(lCont, 18) = .Range("S" & lng).Value
'            Me.ListBox1.List(lCont, 19) = .Range("T" & lng).Value
'            Me.ListBox1.List(lCont, 20) = .Range("U" & lng).Value
'            Me.ListBox1.List(lCont, 21) = .Range("V" & lng).Value
'            Me.ListBox1.List(lCont, 22) = .Range("W" & lng).Value
'            Me.ListBox1.List(lCont, 23) = .Range("X" & lng).Value
'            Me.ListBox1.List(lCont, 24) = .Range("Y" & lng).Value
'            Me.ListBox1.List(lCont, 25) = .Range("Z" & lng).Value
'            Me.ListBox1.List(lCont, 26) = .Range("AA" & lng).Value
'            Me.ListBox1.List(lCont, 27) = .Range("AB" & lng).Value
'            Me.ListBox1.List(lCont, 28) = .Range("AC" & lng).Value
'            Me.ListBox1.List(lCont, 29) = .Range("AD" & lng).Value
'            Me.ListBox1.List(lCont, 30) = .Range("AE" & lng).Value
'            Me.ListBox1.List(lCont, 31) = .Range("AF" & lng).Value
'            Me.ListBox1.List(lCont, 32) = .Range("AG" & lng).Value
'            Me.ListBox1.List(lCont, 33) = .Range("AH" & lng).Value
'            Me.ListBox1.List(lCont, 34) = .Range("AI" & lng).Value

           
           
            lCont = lCont + 1
        Next
    End With
End Sub


Private Sub UserForm_Terminate()
    Set sh = Nothing
End Sub

 

Informazioni domanda


Ultimo aggiornamento febbraio 27, 2018 Visualizzazioni 168 Si applica a:
Risposta
grazie, credevo di non riuscire ad interpretare io i vari forum che ho spulciato, spero comunque che qualcuno riesca ha risolvermi il problema.  "LA SPERANZA E' L'ULTIMA A MORIRE". Grazie comunque per l'aiuto che date.
bye, bye


Per risolvere *non devi* caricare la Listbox con AddItem.

Devi utilizzare la proprietà RowSource.

 

Vedi un esempio qui:

http://www.maurogsc.eu/esempiforum13/listbox35colonne

--
Mauro Gamberini
Microsoft© MVP (Excel)
http://www.maurogsc.eu

Il problema è stato risolto?

Siamo spiacenti che questo non sia stato d'aiuto.

Fantastico! Grazie per aver scelto questa risposta.

Sei soddisfatto di questa risposta?

Grazie per il tuo commento, ci aiuta a migliorare il sito.

Sei soddisfatto di questa risposta?

Grazie per il tuo commento.