Data will not transfer from listbox

[advox1]

Hi

I have a userform which once completed transfers the info into the next empty row of a worksheet.

Problem is that four of the seleciotns are listboxes and for whatever reason the info selected by the user in the listbox will not transfer.

The code I am using is as follows

'find next empty row

nextrow = Application.WorksheetFunction.CountA(Range("a:a")) + 1

'Transfers the info

Dim DateBox As String
Dim Starttime As String
Dim Endtime As String
DateBox = Daybox.Text & "/" & Monthbox.Text & "/" & Yearbox.Text
Starttime = Starthrs.Text & ":" & Startmins.Text
Endtime = endhrs.Text & ":" & endmins.Text

Cells(nextrow, 1) = DateBox
Cells(nextrow, 2) = Casebox.Text
Cells(nextrow, 3) = Typebox.Text
Cells(nextrow, 4) = letterbox.Text
Cells(nextrow, 5) = Summarybox.Text
Cells(nextrow, 6) = Starttime
Cells(nextrow, 7) = Endtime
Cells(nextrow, 10) = Extratime.Text

Where Starttime and Endtime are the listboxes.

Can anyone tell me why this is happening and maybe a solution?

Thanks

Jo

 

[Chance1234]

use .value instead of .text


Filmmaker, gentleman and Ambasador for London to the sticks.

http://WWW.5YLAC.S5.COM

 

[TonyJollans]

Hi advox1,

Could you try again with telling us which item is which? You say you have 4 listboxes, but name 2: Starttime and Endtime - which are both defined as string variables in your code. Do you mean that Starthrs, Startmins, Endhrs, Endmins are the listboxes?

Assuming everything else works, the only thing I can think of at the moment is that if your listboxes are multi-select, the text property won't have a value.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[advox1]

Hi

.value does not work sorry

Tony,


You are correct in that the listboxes are starthrs startmins endhrs and endmins. They are not multiselect.

The values of these listboxes are defined from a rowsorce on the spreadsheet. As the name state the first list box starthrs goes from 07 - 22 for the hours and this then needs to be separated by a colon and then the second listbox, startmins 00 - 59 complets the time.

The user can only slect one time from each listbox

Jo

 

[TonyJollans]

Hi advox1,

Just to confirm .. the values you are getting in the worksheet are each a single colon (":").

Things which might cause a problem are multi-column listboxes or perhaps a lack of proper reference, but presuming your code is in the userform's code module, it should be OK.

Not really sure what else to suggest at the moment; Sorry!

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Chance1234]

put a break line in and see if the variables are holding the values form the listbox then least that way we can knock that off the list off what it could be

Filmmaker, gentleman and Ambasador for London to the sticks.

http://WWW.5YLAC.S5.COM

 

[advox1]

Hi Tony,

Yes the only value which is being entered into the cell is the ":".

Chance1234,

I put a break in and the variables did hold. Any suggestions?

Jo

 

[Chance1234]

few things to try seperatly and as a whole

take out the "AS String" see if that makes a difference

try adding .value to the cells bit

try adding in single quotes to the strings

Endtime = "'" & endhrs.Text & ":" & endmins.Text & "'" to see if that works

try changing the format of the destination cells to text

try using a comma instead of : 'you can change it with custom format

try adding in the begining of your strings

"00:" &





Filmmaker, gentleman and Ambasador for London to the sticks.

http://WWW.5YLAC.S5.COM

 

[advox1]

Thanks

Will try them and let you know the outcome.

Jo

 

[Chance1234]

had a thought at the bar last night
about using the # character to surround your times and dates


Filmmaker, gentleman and Ambasador for London to the sticks.

http://WWW.5YLAC.S5.COM