Data Entry VBA on next empty column

[chubbyjenz]

Guys, may I ask for your help? I've searched tutorials on this but i can't find the right answer to my problem.

1.I've watched tutorials on youtube but the data entry was on the next row. I want that my data entry to input data on the next blank column. I'm having problem with the code to use

2. And i hope that there's a way that the P.O. number will arrange in ascending order.

Example :
The last P.O. number was 005 then i've realize that I've missed P.O. number 003. And I want to input P.O. number 003 and it will be arrange.

untitled.jpg

[nathansav]

Was there not a tutorial on how to sort using vba for point 2?

[chubbyjenz]

hi, i think i've found something in here for point 2.

my main concern is my question number 1.

can you help me with this one pls?

[BS Singh]

Upload a sample file..

[chubbyjenz]

Book1.xlsm

this is the file. What i want is whenever i input the date, the P.O. number, it will input in a column.

[chubbyjenz]

hi guys.. ill just bring this UP.. hope someone can help.

[chubbyjenz]

Hello guys, ill bring up my post..

[john55]

perhaps

Private Sub CommandButton1_Click()
Dim NextCol1 As Long
Dim NextCol2 As Long
Dim NextCol3 As Long
       
    With ActiveSheet
         
        NextCol1 = .Cells(2, .Columns.Count).End(xlToLeft).Column + 1
        If TextBox1.Value <> "" Then
        .Cells(2, NextCol1) = TextBox1.Value
        Else
        End If
         NextCol2 = .Cells(3, .Columns.Count).End(xlToLeft).Column + 1
         If TextBox2.Value <> "" Then
        .Cells(3, NextCol2) = TextBox2.Value
        Else
        End If
          NextCol3 = .Cells(5, .Columns.Count).End(xlToLeft).Column + 1
         If TextBox3.Value <> "" Then
        .Cells(5, NextCol3) = TextBox3.Value
        Else
        End If
      
        'and so on
    End With
End Sub

[chubbyjenz]

thank you so much john55. i hope you can help me again next time.

[john55]

you're welcome. glad I could be of help.
if you are happy with the solution provided, please mark the thread as "Solved". thank you!

[chubbyjenz]

hey john, i'm trying to add another code.

1. Clearing the userform after pressing submit by using
textbox.value = ""

and

2. adding MSGbox to the PO number if the data is not a number and to the date.

MsgBox "The P.O. No. box must contain a number.", vbExclamation
Me.POnumber.SetFocus



and one more thing,

3. How can i add a confirmation message that when the submit button is press, a dialogue box or something will pop up saying something like "Are you sure blah blah blah?"

It will pop up a message box, however, it will still input the data in the userform. how can i prevent this?

[john55]

hope it helps you

Private Sub CommandButton1_Click()
'the question
If MsgBox("Do you want to add it?!", vbYesNo + vbQuestion, "Confirm?!") = vbYes Then
Dim NextCol1 As Long
Dim NextCol2 As Long
Dim NextCol3 As Long
 'and so on
    With ActiveSheet
         
        NextCol1 = .Cells(2, .Columns.Count).End(xlToLeft).Column + 1
        If TextBox1.Value <> "" Then
        .Cells(2, NextCol1) = TextBox1.Value
        Else
        End If
         NextCol2 = .Cells(3, .Columns.Count).End(xlToLeft).Column + 1
        
         If TextBox2.Value <> "" Then
        .Cells(3, NextCol2) = TextBox2.Value
        Else
        End If
          NextCol3 = .Cells(5, .Columns.Count).End(xlToLeft).Column + 1
         If TextBox3.Value <> "" Then
        .Cells(5, NextCol3) = TextBox3.Value
        Else
        End If
      
        'and so on
    End With
    End If
    'clear the textboxes
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    '
End Sub


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

[chubbyjenz]

Thanks again john55 . You're the best!

however, is there a shortcut for these?

1. Error message when none of those 7 textboxes (not including the date and the PO number) are not filed up if you press submit

2. Error message when inputs from any of those 7 textboxes (not including the date and the PO number) is not a number.


is there a shortcut for this? because i will be working on my next excel userform with 40 textboxes and i want to know my options.

Hope you can help.

[john55]

You're welcome!
your new requirements are not reflected by the thread title, I think you need to open a new one....

[chubbyjenz]

thank you john55.

i've already created a new thread.. i hope you can help me again this time.