userforms needed for moving data from columns to match selected destination column.

Hi all,
I have a file (that after some sagely words of advice from another forum member I started again from scratch), that I want to get a userform to move data.
Simplistically,
The data in Column A is a location. The data in columns B - C are outputs from a barcode. There is a minus sign separating the data, and it is split by Text to columns, but I don't know how to do that automatically.
I have started a userform, that when the data is entered, a button is to be pushed on the userform, which will then trigger a second userform asking for a location. At that point, the number in column A is entered, and the data is then moved to the corresponding row, in columns B - C.
If there is already data there, a popup saying that data is there, and asking you if you want to continue? If yes, then the data in those cells is moved to another worksheet in the file (lost & found) and the data is replaced with the new data. If no is selected, then the process stops.
I have left a basic premise of what I think should happen on a text box on the file that hopefully makes sense.
Is this something anybody can help with please?
Many thanks.

Hello, so far only the part move to production ok
i need more info for other options.

But first take a look and let me know, also
sheet name "Production " is changed to "Production"


Kind regards
Leo

Hi LeoTaxi,
that looks like a really promising start.I like it. Thank you.
When they move to the production sheet, the whole row disappears. Is it possible to leave the code in column A and move the data in column B-D?
The reason is that this will then show as an empty space, which ideally is preferable as it the allows me to count empty spaces as a separate function.
Many thanks,

but first, use copy to copy and move to move, when you ask to move something
it moves, and is no longer present on previous location, so what kind of modification i do

Move B,C and D and do nothing with A, ?
or copy B,C and D ?

Kind regards
Leo

Hi,
move B,C and D and do nothing with A is absolutely correct.
Many thanks,

So just let me know for those other options


Kind regaards
Leo

That works great thanks.
The other options:
If you put in stock code, as an example 06L0101A, and want to move the data in B,C and D to 31L0106A (B,C & D), then it moves the data. If there is already data in those cells, then ask if you want to move(the existing) data? If yes, then move it automatically to the lost and found sheet, and replace it with the data from 06L0101A B,C & D.
Hopefully that makes sense?
Many thanks.

if I have understood everything correctly, this should do

Kind regards
Leo

Hi Leo,
many thanks for this. It is looking very good. On the userform, screen"1" requires you to scroll through the list and select it from that list. When finished, that list could be over 2500 lines long. Is it possible to have it so it is just a textbox to enter the code in as per userform sreen "0"?
Many thanks for your help so far.

Kind Regards,
J.

Hi Johnny,

if you move items, is it always to the same row in other sheet ?, and is the total of sheets 6 ?

if both cases are true whe can reduce the selection in listbox to 7

Kind regards
Leo

Hi Leo,
The items moved will always be on the same column, not the same row.
The total number of sheets will be 12 including the production one, lost and found etc.
Hopefully that makes sense?
Thanks. in advance.

Johnny.

That case we go for the textbox

Kind regards
Leo

Thanks,
I tried moving something from one worksheet to another by entering the code in column A on the first worksheet, to code on the second worksheet and got this error:-
Attachment 730732
Thanks.

attachment can not be opened, so maybe say wich one to where and
i see myself

thx
Leo

Hi, apologies,
it comes up with a debug message stating runtime error 381:
Could not get the list property. Invalid property array index.
Thanks,
Johnny.

Other method



Kind regards
Leo

Hi,
Looks much cleaner thank you!
It came up with a runtime error 9 subscript out of range

When I click debug, it comes up highlighting this line of code on command button 2:

Please Login or Register  to view this content.

Many thanks for all your help so far.
Johnny.

My mistake,


this should do it

Kind regards
Leo

Thanks!
It nearly works now. When moving between the two coldstore sheets - it is fine.
If I try and move something from a coldstore sheet to production, I get the message: This item is not present in this file!
Thanks.
Johnny.

hopely third try with other method works


Kind regards
Leo

That works exactly! Thank you very much!
I just need to get the first part with importing the data initially and convert text to columns, and it is all done!!
Many thanks again for your help.
Johnny.

Yes but i see in output QR code only columns B,C and D
we do not know the sheet name, column where those qr codes are
we do not know to wich sheet and column those splitted qr codes has to go, and what to put in column A

i only see possibility to split, but if i dont know from where and to where,

maybe more info can help

Kind regards
Leo

It is very similar to what you have already done (I think).
The B,C&D is driven by the number in column A, which could be on any of the sheets.
So, I guess, on the userform you have already kindly created, if the first textbox was the QR code, the second would be the destination, and then data is then put in those columns. Hopefully that makes sense?

Many thanks,

No,

now we search by entering info from column A in textbox 1, this info is not present in qr code

Apologies, I was probably not clear.
The first part of the process will be that the QR code is scanned, and entered. This is the data that is in columns B-D You will then decide where to put it, and select the location. This is the data in column A.
That is how the data gets into the file in the first place.
The next step is that the data is then moved to a different location, or into production etc. This is the part that you have kindly got done for me at the moment.
That is why when the data is moved, the data in column A is left and the rest is moved - because column A represents a location, so no data in B-D means that the location is empty, and available to take something else. (QR code data) and cycle begins again.
Does that make more sense at all?
Many thabks,
Johnny.

Hi,
sometimes it does (if moving data, it will be need the data in column A to indicate B-D)
The extra zero is not conditional. The split is correct. I used a minus sign as the separator as it was easiest to do and selected text to columns to split, but could not do it automatically.
Thanks,

Hi Johnny,

could be data in column D is not 100 % correct if you start from QR code
but test this for yourself and let me know.

Other things are updated



Kind regards
Leo

Hi Leo,
thanks for this.
I have given it a test, and I get a runtime error '91' when I enter the data.
The message is Object variable or with block variable not set. It highlights this line of code:

Please Login or Register  to view this content.

I am entering the QR code data (PULSP962-16/10/2023-PULSP9621457985) in the textbox on the left, then the number from column A (06L0101C as an example).
It is putting the data in though, so it is almost there I think!

Kind Regards,
Johnny

Another approach, I see in thread #9 you do not want combobox lists, it is not as bad as it looks, there is a textbox embedded over the combobox (you can type the data in and hit 'Enter' searches the same) the combobox is there as a supplementary search.
To establish a item code at a location first place the QR code in 'A1' on the code sheet then select 'code' in the first dropdown, the QR is automatically split into three elements.
Then select the destination from the destination sheet dropdown followed by the location (a number of options follow dependent on whether the destination is empty or not.
I have tried to cover all combinations and options but have not rigorously tested, as long as you keep the same format you can add as many 'Coldstore_n' sheets as required, the code should recognise the additions.
torachan.

Hi Johnny,

i test it on the file i give in #28 with your inputs from #29 and had no errors,
so did you copy code to other file or you have this error on my file ?

I am really curious

Kind regards
Leo

Hi Leo,
no, it was not copied in another file. It was from your file.
I moved an item out from the first column, in the process defined, and then went to replace that data with some from a QR code. It would put the data in, but then come up with the runtime error as outlined above.
I managed to replicate it twice.

Thanks,

Johnny

Hi torachan,
many thanks for your contribution on this. It looks good.
I think Leo is pretty much there with what I am looking for, as in a real world scenario, I want something that an operator can take and scan a barcode, and then scan another barcode and have one button to push. All they will have on their excel view will be a plain white worksheet with a button on (all the tabs will be hidden). The tabs with the data on will be gone through by me and will not require any intervention from anyone else. This removes a lot of risk of keying errors, or scrolling through lots.
Currently as things stand, Leos format will allow entrance of a QR code, and a linear bar code and can move data from one to the other, so no need to keep any other tabs open.
Many thanks and kind regards,

Real challenge this one

as i ame in short of time for today, i only blokked the line that gives the error
i grave deeper later this week.

Kind regards
Leo

Hi Leo, as mentioned, it is not falling over at the moment. File now with data in as mentioned.

Many thanks.

Johnny.

Hi Johnny247, I always follow with a keen interest any posts that I have made a submission to so that I may learn how the outcome evolved.
Noting the moving goal posts (aka additional parameters) this is inevitable developing 'on the fly'.
Within the stock/location sheets there appears common storage locations in sheets '425', '4' & '3'.
If this is the case how are you differentiating, I see nothing in the code to date that deals with this (in the nature of primary and secondary keys).
Just a passing observation that needs to be addressed if the case is as seen.
Maybe one massive list (with primary & secondary keys) far faster to filter than 10 individual sheets.
torachan.

Hi torachan,
many thanks for the feedback. I note you are absolutely correct. It is a typo on my behalf whilst trying to rush through things at work this afternoon.
I have now corrected them. They are for locations (probably fairly obvious!)
I did think about one massive list at first, but given that it would potentially be over 2500 lines of data, that would be an ardous task to try and filter - especially as each of the tabs represent a different physical location as well.
Hopefully that makes some sense now.
I have attached the corrected one here.

Many thanks,

Johnny.

Hi Johnny,

think most is ok now, otherwise let me know


Kind regards
Leo

Hi Leo,
many thanks again.
I get a runtime error (object missing) when I try and move something now on this line of code:-

Please Login or Register  to view this content.

Many thanks,
Johnny

missing 't'

Thanx torachan

so Johnny in the line with error change namelisbox to namelistbox

Cheers
Leo

Had to modify for double entrys, check on sheet lost & found was not included
in this it is



kinfd regards
Leo

Hi Leo,
I have added the "t" , and that now comes through on Lost and found thank you. I can't seem to get it to come through on any of the production tabs. How would I do that please?
Many thanks.
Johnny.

You mean the check for double entry ?

Hi Leo,,
No, where on the lost and found tab it puts time and date and operator name next to each entry, I wanted to look to get that on all tabs please.
I also noted on the userform, in vba there is the listbox at the bottom of the form, with the cancel button, but that is not coming through on Excel. How do I activate that please?

Many thanks,
Johnny.

Date, time and user in every tab, for activate te hidden listbox with cancel button
enter existing batch.


Kind regards
Leo

Hi Leo,
Thanks. I have tried this, and I get a runtime error (object required) on this line of code:-

Please Login or Register  to view this content.

I tried moving from 06L0101A to Production Retail.
i have tried entering APPLE001 in box 1 and box 2 either on their own, or together and I cannot get the hidden listbox, or cancel button to activate. Please what am I doing wrong on that?
Many thanks for your help.

Kind Regards,
Johnny.

Sorry Johnny, you did nothing wrong, few mistakes my site

new try

Kind regards
Leo

Hi Leo,
I have tried this thanks, but I keep getting runtime errors (381 - could not get the list property. Invalid property array index) on these lines of code:-

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Is this what will stop the timedate stamp and name appearing on the coldstore tabs, and stopping the search listbox appearing?
I tried moving:-
325R0103A to 325R0108C with the name Marcin if you want to try and replicate it.
Sorry I can't seem to get it working. Appreciate the work you are putting in.
Thanks,
Kind Regards,
Johnny

Maybe because i dont test any more, only modify errors

so next try


kind regards
leo

Sorry!
I get a compile error every time now on this line of code:-

Please Login or Register  to view this content.

Kind Regards,
Johnny

Fixed the error(extra set of brackets on the end).
Still can't get the hidden listbox to activate or the cancel button? Is there an order to it please?

Many thanks and regards,
Johnny

it only appears by handling QR code that already exists

what moments you want more ?

Kind regards
Leo

Ahh!
Is it possible to change it at all to look for column B?
As an example, I Enter APPLE001, and it will show a list of where that item is?
I think that is then everything and finished!
Many thanks again.
Kind Regards,
Johnny.

Maybe finishd,



Kind regards
Leo

It is just about there Thanks!
Just instead of the row number, can it show the number in column A please? Then that really is it! So close now!
Many thanks and regards,
Johnny

Maybe,


for today i ame gone, back tomorrow



Kind regards
Leo

I think that is it!
May thanks for this. It is fantastic!
I will mark the thread as solved, and do some in depth testing tomorrow.
Thank you once again.
Kind regards,
Johnny

with modification for products on hold, only for sheets coldstore


Kind regards
Leo

Hi Leo,
many thanks for that.
I have been testing this the last couple of days quite extensively now (thanks). I have added a second userform, that would be used for the Hold code, with a view of putting the batch number (column D) into the single textbox, and the pushing the button and the word hold then appearing in column G on the corresponding row. I had tried to modify your original script to do this, but failed miserably. Would you be able to do that at all please?
Also, I have noticed that sometimes when moving data from the coldstore worksheets to the production worksheets, some of the data does not pull through (columns B-D), but the date and name etc does.(example on production packhouse)
Conversely, when entering data onto the coldstore worksheets, the opposite seems to happen. (examples on Coldstore 06).
When you have time, is this something you would be able to have a look at at please?
Many thanks in advance,

Johnny.

Think i found it, have no more time for today, so test this and let me know

Kind regards
Leo

1 last modification


Kind regards
Leo

Hi Leo,
I have done some more testing thanks. I think the issue is that when you try and move something from a location that is already empty, then it will show just name/date/time. I think (if it is possible) that it needs a message saying nothing to move, and clear the textbox if there is no data in B-D for the location put into box 1.
Hopefully that makes sense?
Many thanks in advance.
Johnny

Hi Johnny,

moving empty location is fixed, no longer possible
also found 1 other thing, is fixed to

Kind regards
Leo

Hi Leo,
apologies for the delay in replying. I have done some additional testing and can confirm it all looks like it is functioning as I had wanted. Many thanks again for your continued help with this.

Kind Regards,

Johnny

Hi Leo,
you helped me a lot with this last year which was most appreciated.
I am now looking to simplify it somewhat to use in a different format. Is that something you could help with please if I explain what needs changing?
I look forward to hearing back. Thanks. Johnny.

Attached is the layout of the file..