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.

'ThisWorkbook
Private Sub Workbook_Open()
UserForm1.Show
End Sub

'behind UserForm1
Private Sub CancelBtn_Click()
Unload Me
End Sub

Private Sub cmdImport_Click()
Dim lngCC As Long
Dim lngRC As Long
With ActiveSheet
  lngCC = 3 + Me.ComboBox1.ListIndex
  
  tbdate.Value = .Cells(2, lngCC)
  tbpono.Value = .Cells(3, lngCC)
  
  For lngRC = 5 To 24
     Controls("tb" & Format(lngRC - 4, "00")).Value = .Cells(lngRC, lngCC)
  Next lngRC
End With
Me.cmdImport.Enabled = False

End Sub

Private Sub ComboBox1_Change()
Me.cmdImport.Enabled = Me.ComboBox1.ListIndex > 0
End Sub

Private Sub Submitbtn_click()
Dim lngRC As Long
Dim lngCC As Long
  
If Me.tbdate.Value = "" Then
  MsgBox "Please Fill up Date.", vbExclamation, ""
  Me.tbdate.SetFocus
  Exit Sub
End If

If Me.tbpono.Value = "" Then
  MsgBox "Please fill up P.O. number.", vbExclamation, ""
  Me.tbpono.SetFocus
  Exit Sub
End If
If Me.ComboBox1.ListIndex <= 0 Then

  If MsgBox("Are you sure you want to add?", vbYesNo + vbQuestion, "Confirm?") = vbNo Then
    For lngRC = 5 To 24
      Controls("tb" & Format(lngRC - 4, "00")).Value = ""
    Next lngRC
    tbpono.Value = ""
    tbdate.Value = Date
    Exit Sub
  End If
End If

If tbpono.Value = "" Then Exit Sub

With ActiveSheet
  If Me.ComboBox1.ListIndex > 0 Then
    lngCC = 3 + Me.ComboBox1.ListIndex
  Else
    lngCC = .Cells(2, .Columns.Count).End(xlToLeft).Column + 1
  End If
  
  .Cells(2, lngCC) = tbdate.Value
  .Cells(3, lngCC) = tbpono.Value
  
  For lngRC = 5 To 24
    .Cells(lngRC, lngCC) = Controls("tb" & Format(lngRC - 4, "00")).Value
    Controls("tb" & Format(lngRC - 4, "00")).Value = ""
  Next lngRC
  tbpono.Value = ""
End With

Me.ComboBox1.ListIndex = 0
UserForm_Activate

End Sub

Private Sub UserForm_Activate()
Dim lngCC As Long

Me.ComboBox1.Clear
Me.ComboBox1.AddItem "None"
For lngCC = 4 To ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column
  Me.ComboBox1.AddItem ActiveSheet.Cells(3, lngCC).Value
Next lngCC

Me.cmdImport.Enabled = False
End Sub

Private Sub UserForm_Initialize()
Me.tbdate = Date
End Sub

Private Sub tbpono_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'PO number format
If TypeName(Me.ActiveControl) = "TextBox" Then
  With Me.ActiveControl
    If Not IsNumeric(.Value) And .Value <> vbNullString Then
      MsgBox "Sorry, ""The P.O. number"" only numbers allowed!", vbCritical, "oops"
      .Value = vbNullString
      .SetFocus
    End If
  End With
End If
  
End Sub

Private Sub tbdate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Date no format
If TypeName(Me.ActiveControl) = "TextBox" Then
  With Me.ActiveControl
    If Not IsDate(.Value) And .Value <> vbNullString Then
      MsgBox "Sorry, it is not on date format!", vbCritical, "oops"
      .Value = vbNullString
      .SetFocus
    End If
  End With
End If
  
End Sub

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:

Private Sub UserForm_Activate()
Dim lngCC As Long

Me.ComboBox1.Clear
'...

Private Sub Submitbtn_click()
'...
Me.ComboBox1.ListIndex = 0
UserForm_Activate

End Sub

Ciao,
Holger