MrExcel Publishing
Your One Stop for Excel Tips & Solutions

USING A LISTBOX ON A FORM TO UPDATE A WORKSHEET.


Posted by Montgomery on September 13, 2001 7:37 AM

Hello there,

I have a listbox along with other controls on a form which will be used to update a worksheet. I have written my code and it seems to be working except for the listbox. The problem is that each transaction does not need to have information from the list box and when i add a record that does not have any information in the listbox the information that is updated for the next transaction goes to the previous empty cell. Here's the code maybe someone can help

dim seltext as string
dim i as integer
const perilscol = 11 'perils in column s
seltext = ""
with perilslbx
for i = 0 to listbox1.listcount - 1
if listbox1.selected(i) then
seltext = seltext & listbox1.list(i) & ""
end if
next i
end with

I THINK HERE IS WHERE THE PROBLEM LIES

'put value in next available cell in perils column
Cells(65536, perilscol).End(xlUp).offset(1).value = seltext
end sub


Posted by Russell Hauf on September 13, 2001 9:42 AM

You are right about where your problem is. I would suggest simply changing the line:

seltext = ""

to

seltext = "'"

This will put a single quote in the cell if nothing is selected in your listbox. It will look blank unless you look in the cell, and the xlUp will recognize it has not being blank.

Hope this helps,

Russell

Posted by Montgomery on September 14, 2001 7:35 AM

Thanks Russell - It worked along with a slight change in code