Using List boxes and text boxes with complex data sets

[Mlanger575]

Hey there

I'm wondering if you can help. I am using a Macro to gather information to write to a spreadsheet. The spreadsheet uses multiple macro entry points and compiles information.

The macro I am using is below, however the macro errors at the following point

Please Login or Register  to view this content.

I'm really not sure why this would be the case, the macor should be taking all the entries from the user form and writing to relevant cell in another sheet. ANy help would be greatly appreciated. the full code is below

Please Login or Register  to view this content.

[jindon]

What is the length of "ListStr"?

It might give error when the length is less than 2...

[Mlanger575]

Hi there

The length is multiple lines of data, despite changing this, the macro doesn't run as I would have expected which begs the question if the macro is properly formed

Any ideas?

M

[jindon]

You are not answering to my question.

What is the length of "ListStr"?

It could have no value when no item selected in Listbox.

[Mlanger575]

Morning Jindon

The length in characters could be up to 1058 characters made up of 21 lines. You are correct, no value could exist should nothing be in the list box

Thanks

[Mlanger575]

Hi Jindon

I've been looking into this more, in terms of length

Please Login or Register  to view this content.

, is the code not wrapping this with the Len section?

Thanks

[jindon]

You can check it with adding one line

Please Login or Register  to view this content.

[Mlanger575]

Thanks - I really do not understand

[jindon]

Then need to see your workbook.

[Mlanger575]

Please see the attached

[jindon]

It looks into ListBox2 (one on the right side), so if you select the item(s) from ListBox2, it works.

Did you write the code for yourself?

[Mlanger575]

Hey Jindon

I wrote the code for the Listbox one and two myself, however I tried to get help with the writing of the text, combo and list boxes to the master sheet.

The macro works well for everything when selecting the ok button but returning the ListBox as a string is really the issue I'm having, it's much more complicated than I initially thought!

Any ideas?

[Mlanger575]

Hey Jindon

I wrote the code for the ListBox macro myself and it works.

I saught aid for the writing of the user form to an existing data set, when selecting ok on the user form, the macro writes well for TextBox and ComboBox but not a ListBox as a string, that's the area I'm really struggling getting to work.

Any ideas?
Michael

[jindon]

1) I would check if the user select any item(s) from list box2,
e.g

Please Login or Register  to view this content.

And change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Then

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Mlanger575]

Thanks Jindon

The check is really useful as I should have picked this one up to maximise the use of the user form.

I still seem to have an issue whereby the row is not updating using the following code

Please Login or Register  to view this content.

I have not used the vbLf item before, would this need something within the DIM to identify?

Many thanks
Michael

[Mlanger575]

What I was expecting is the result from ListBox2 to enter the master sheet within Row27 in the relevant row in one cell. Currently nothing is there.

I'm sure its something silly, I would really appreciate if you could help
Michael

[Moha9030amir]

Need Spell Currency To Convert To Rial Omani

[jindon]

vbLf is the constant for Line Feed character, so it should add multiple lines if multiple items are selected in listbox2.
If you unprotect Master sheet and format col.AA "Wrap text", you will see the contents in the cell.

[jindon]

OOps..
Should be

Please Login or Register  to view this content.

[Mlanger575]

Hi Jindon

The master sheet is still not updating with the string of items from ListBox2, any ideas of what else is missing?

I've just tested on your sheet attached and its the same result

Many thanks
Michael

[jindon]

It is updating.

Do you really understand what is does?

[Mlanger575]

Hi Jindon

When selecting ok on the user form, the items inserted from ListBox1 to ListBox2 should write the items in ListBox2 into the 27th column of the master worksheet, currently this is not working on either spreadsheet.

The following writes beautifully

Please Login or Register  to view this content.

However the following is not writing the content of itself as a string to the 27th column in the "master' worksheet

Please Login or Register  to view this content.

What am I missing?

Many thanks

[jindon]

1) Select PAP123 fom Combobox, studyID should be EDBBV10065 and it is in A65 in Master Sheet.
2) Copy some items to ListBox2 from ListBox1 and select item(s) in ListBox2.

It should update AA65 according to the items selected in ListBox2.

[Mlanger575]

Hi Jindon

Thats the thing, its not updating on myside for some reason? AA65 in this example is blank

[Mlanger575]

Hey,

I've just played with the check / uncheck all. This seems to work when selected. Is there anyway to by-pass this at the writing to 'Master'sheet state? The reason being the CheckBox was to move large date from ListBox1 and ListBox 2

Thanks
Michael

[jindon]

Oops typo, it should be A46, not A65. so it updates AA46.