Editing Existing Data in a column using a userform.

are you able to apply a code to jpg file ?

Hi, chubbyjenz,

maybe try the workbook attached. I donīt think you should really start considering a Textbox to retrieve PO Numbers.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

You will have to play a bit with the form to understand how it works.

Ciao,
Holger

Hi, chubbyjenz,

instead of getting the new column number like now you would have to compare the PO number to the one in the last Column and then decide to add after that or run a loop back until the number fits in (you would have to insert a Column there to add the data which would be a different code as you would need to do that prior to writing the data). I would consider to add like before and maybe sort the range according to the numbers.

Ciao,
Holger

Hi, chubbyjenz,

if it's not too much of course.

At this time of the day (itīs 21:20 local time) and after nearly 15 hours Iīd say I could do it by tomorrow but at least give you some hints on how to do it on your own.

Once you open the workbook the UserForm is displayed. Use Cancel there to unload the Form. Switrch into the VBE, go to the code behind the UserForm.

Place the cursor in the first executable line of code of UserForm_Initialize and press F9. Normally a dark brown point should appear next to that line indicationg a Breakpoint. Thios point will stop the execution of the point (thatīs wanted) but will disappear if you close the workbook (tahtsīs wanted too).

Same with the breakpoint goes for
- Submitbtn_click
- cmdImport_Click
- ComboBox1_Change.

Like stated any execution will be stopped and bring you to the line in the VBE. You may continue to single-step through the code by using F8 where you may watch the values of the variables as well as the effect to the worksheet if you have a look at that part besides the VBE.

HTH,
Holger

Hi, chubbyjenz,

in my understanding, you've replaced the cells by variable lngRC am i right or not?

Absolutley right. I was using a loop and used the loop counter instead of hard-coding teh row numbers.

cells 5-9 ; 11-15 ; 17-21 ; 23-27 and so one. (one blank cell after 5 cells) how will i do that?

I really donīt like working with the loop counter. There are at least two general ways to handle this. One is to use a loop counter for the whole area of rows (from 5 to 27) and use a Select Case and just run the code in Case of 5 to 9, 11 to 15 etc. while skipping the other numbers. The second one is to use another counter running from 1 to 5 being augmented by 1 at the start of the loop. When he reaches 5 he augments the main counter by one (thatīs what I hate about it) and is set back to 0 for the next loop. But isnīt that worth a thread if the new project is in work?

Ciao,
Holger

Hi, chubbyjenz,

as this is part of the loop I donīt know a different way to address multiple Textboxes all being named accordingly.

The command checks the names of the controls on the UserForm and applies the action to those that meet the criteria. All of your Textboxes for the loop start with tb and feature a 2-digit number with leading zero.
The first Textbox is names tb01 and should match Row 5 in the worksheet. As the row counter is looping from 5 to 24 we need to substract 4 from the row counter in order to get the proper Textbox to work with. The Format-command gets in the leading 0 for the numbers between 1 and 9.

HTH,
Holger

Hi, chubbyjenz,

sorry but I canīt get the point of what you are asking in the last post - you would have to explain again (at least for me).

Ciao,
Holger

Hi, chubbyjenz,

if I had been able to imagine a way to incorporate most of your code and build up on that I would have done so. If the code supplied by me isnīt working like expected Iīd be willing to work on that but Iīm afraid I will not start over and develop another solution just because you are asking for it. Maybe some other member may come in with a different approach or according to what you wish.

Ciao,
Holger

Hi, chubbyjenz,

from what I understannd from my code the ComboBox will get cleared every time the UserForm ia activated or the command being called thus preventing multiple entries to be made:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Ciao,
Holger