PDA

View Full Version : Solved: Problem with save texts of UserForm.ListBox



Nader
02-13-2008, 07:11 AM
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.



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



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



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


Take a look to the excel file

Tinbendr
02-13-2008, 03:34 PM
In the Queryclose event, the last line needs a -1


Sheets("sheet3").Cells(1, 1).Value = UserForm1.lstM.ListCount - 1

Nader
02-13-2008, 05:24 PM
it successd with me only for listbox on sheet but on the userform it didn't success with me.

Tinbendr
02-13-2008, 09:42 PM
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
02-14-2008, 06:32 AM
I add this code and it succeed with me

If Sheets("sheet3").Cells(1, 1).Value = "" Then Exit Sub


the code became :

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

Thank you for help