Solved: Problem with save texts of UserForm.ListBox

[Nader]

I tried this code to save the text of the listbox called "lstM" ( I placed on sheet1) in the sheet3 and after open the book it shows the text in the the listbox. it successd with me.

[vba]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets(3).Range("a:a").Value = ""
For i = 0 To Sheets("sheet1").lstM.ListCount - 1
Sheets("sheet3").Cells(i + 2, 1).Value = Sheets("sheet1").lstM.List(i)
Next
Sheets("sheet3").Cells(1, 1).Value = Sheets("sheet1").lstM.ListCount
End Sub

Private Sub Workbook_Open()
For i = 0 To Sheets("sheet3").Cells(1, 1).Value
Sheets("sheet1").lstM.AddItem Sheets("sheet3").Cells(i + 2, 1).Value

Next
End Sub

[/vba]

I placed a listbox called "lstM" on the UserForm and tried this code to save the text in sheet 3 to show the text of the the listbox . It saved the text but with balnk rows
How can I fix this problem

[vba]

Private Sub UserForm_Activate()
For i = 0 To Sheets("sheet3").Cells(1, 1).Value
UserForm1.lstM.AddItem Sheets("sheet3").Cells(i + 2, 1).Value
Next i
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Sheets(3).Range("a:a").Value = ""

For i = 0 To lstM.ListCount - 1
Sheets("sheet3").Cells(i + 2, 1).Value = lstM.List(i)
Next i
Sheets("sheet3").Cells(1, 1).Value =UserForm1. lstM.ListCount
End Sub

[/vba]

Take a look to the excel file

[Tinbendr]

In the Queryclose event, the last line needs a -1

[VBA]
Sheets("sheet3").Cells(1, 1).Value = UserForm1.lstM.ListCount - 1
[/VBA]

[Nader]

it successd with me only for listbox on sheet but on the userform it didn't success with me.

[Tinbendr]

You have to start out with the correct number of names and reference number in sheet3:A1. Since your code doesn't check for empty cells when it runs, the names have to be correct to start with.

So starting at A3, the example I uploaded has eight names. They show up correctly in the userform.

[Nader]

I add this code and it succeed with me
[VBA]
If Sheets("sheet3").Cells(1, 1).Value = "" Then Exit Sub
[/VBA]

the code became :
[VBA]
Private Sub UserForm_Activate()
For i = 0 To Sheets("sheet3").Cells(1, 1).Value
If Sheets("sheet3").Cells(1, 1).Value = "" Then Exit Sub
lstM.AddItem Sheets("sheet3").Cells(i + 2, 1).Value
Next i
End Sub
[/VBA]
Thank you for help