MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Listbox Problems - There might be something I'm Missing

Posted by Mareene B on November 02, 2001 12:12 PM

Hello out there,

Each transaction that is updated to the worksheet do not have to have perils - that is where the listbox problem occurs. I find that when the user did not have to select anything from the listbox, the data was updated to the worksheet leaving that column empty, the very next transaction to be updated to the worksheet that had data from the listbox would go to the next empty row in the coloumn. It was suggested that I put ' in my code so that the spaces that were empty would be filled but when the user selects p2 p4 from the listbox it is updated to the worksheet as 'p2 p4. Is there anyway I can get rid of the ' when data is selected from the listbox? Check out the code below. Any help would be appreciated.

Dim seltext as string
dim i as integer
Const Perilscol = 6 "perils in column f
seltext = " ' "
with perilslbx
for i = o to listbox1.listcount -1
if listbox1.selected(i) then
seltext = seltext & listbox1.list(i) & ""
end if
next i
end with
'put value in next available cell in perils column
cells (65536, perilscol).end(xlup).offset(1).value = seltext
End sub

Posted by Todd on November 02, 2001 12:18 PM

don't have the time to investigate further but could try something like right(text,len(text)-1)

Posted by MareeneB on November 12, 2001 5:46 AM