• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automating input and validating

K

Kurt Smart

Member
Hi everyone,

I have little experience with VBA, could anyone come up with a solution or guide me on the path to achieve the below...

Enter number in H2...
Check if it exists in 'Completed' or on 'In Progress' tabs...
If it does input zeros in I2, J2, K2 & L2...
If it doesn't exist then only allow inputs higher than zero in same cells above.

I'm trying to stop people from inputting say in I2 a number 45 then in J2 20 and then zeros in the other two, I want data higher than zero in all 4 of those cells, only if that number in H2 doesn't already exist (duplicate).

Thank you for any help or/and solutions
 
Will the user enter data in the order you've posted ?
H2 then I2, then J2, then K2, Then L2 ?

Will there ever be a circumstance where they will not adhere to that order ?
 
Hi, generally it is random, the information is inputted based on how they get it on paper to transfer, eventually I'd like a pop-up box that forces order.
 
.
Paste in the InProgress sheet level module, below all existing macros :

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Msg As String, Title As String
Dim Config As Integer, Ans As Integer

    If InRange(ActiveCell, Range("H2:H50")) Then            'adjust range here
        Msg = "Do you wish to enter all ZEROS Cols H:J ?"
        Msg = Msg & vbNewLine & vbNewLine
        Msg = Msg & "If so, click YES. "
       
        Title = "Zeros or PO # ? "
        Config = vbYesNo + vbExclamation
        Ans = MsgBox(Msg, Config, Title)
        If Ans = vbYes Then GoTo EnterZeros:
        If Ans = vbNo Then SrchTerm: Exit Sub
EnterZeros:
                    ActiveCell.Value = 0
                    ActiveCell.Offset(rowOffset:=0, columnOffset:=1) = 0
                    ActiveCell.Offset(rowOffset:=0, columnOffset:=2) = 0
                    ActiveCell.Offset(rowOffset:=0, columnOffset:=3) = 0
                    ActiveCell.Offset(rowOffset:=0, columnOffset:=4) = 0
                    Exit Sub 'Now that we have executed the code once we don't need to run it again!
           
    ElseIf InRange(ActiveCell, Range("I2:L50")) Then        'adjust range here
        MsgBox "Select a cell in Column H ", vbCritical, "Column H Select Only"
        Exit Sub
    End If
   
End Sub


Paste in a Routine Module :

Code: 
Option Explicit

Function InRange(Range1 As Range, Range2 As Range) As Boolean
    ' returns True if Range1 is within Range2
    InRange = Not (Application.Intersect(Range1, Range2) Is Nothing)
End Function

Sub shwFrm()
    frmEnterData.Show
End Sub

Sub SrchTerm()
Dim lRow As Integer
Dim IRange As Range
Dim cell As Range
Dim SrchTrm As String
Dim Shts As Variant, Sht As Variant

Shts = Array("In Progress", "Completed")
TryAgn:

SrchTrm = InputBox("Enter PO Number.", "Search PO #")
For Each Sht In Shts
   lRow = Sheets(Sht).Cells(Rows.Count, 8).End(xlUp).Row
   Set IRange = Sheets(Sht).Range("H2:H" & lRow)
   
       For Each cell In IRange
           If SrchTrm = vbNullString Then
               Exit Sub
           ElseIf cell.Value = SrchTrm Then
               'cell.Offset(, -4).Value = "12345"
           MsgBox "PO # previously assigned." & vbCrLf & _
           "Please search again.", vbCritical, "Search Error PO #"
               GoTo TryAgn:
           End If
       Next cell
Next Sht
  shwFrm
  Exit Sub
End Sub


Paste in a UserForm :

Code: 
Option Explicit

Private Sub CommandButton1_Click()
    Unload Me
    Sheets("In Progress").Range("H1").Select
End Sub

Private Sub TextBox1_Change()

End Sub

Private Sub UserForm_Initialize()
   Me.TextBox1.SetFocus
End Sub

Private Sub CommandButton2_Click()
Dim N As Integer
Dim ctrl_name As String
Application.EnableEvents = False
ActiveCell.Select

Dim c As Control
Dim cnt As Integer: cnt = 1

For Each c In frmEnterData.Controls
    If TypeName(c) = "TextBox" Then
        If c.Value < 1 Then
            MsgBox "All entries must be greater than ZERO.", vbCritical, "Error Data Entry"
            c.Value = ""
            Exit Sub
        End If
        cnt = cnt + 1
    End If
Next c

For N = 1 To 5
    ctrl_name = "TextBox" + CStr(N) ' assigns textbox name to a variable
    If N > 1 Then
        ActiveCell.Offset(0, 1).Select ' move down one cell if beyond the first item
    End If
ActiveCell.Value = Controls(ctrl_name).Value ' paste the text-box value into the active cell
Next

Application.EnableEvents = True

Unload Me
End Sub


NOTE: The userform has 5 textboxes and two command buttons.

Download file : https://www.amazon.com/clouddrive/share/FnnXmQZkdQiy8O2cPK80rKLhZWm2XLwxRcYVxWVZ6Su

When the user click on Columns I:L they are advised to select a cell in Col H.
Clicking a cell in Col H displays a MsgBox asking if they want to enter all ZEROs. If they answer YES, zeros are added to the row they clicked on.

If they answer NO, a request to search for an existing PO # is created. If the PO # has been used previously, they are given another chance to search for a different PO #. Once a new PO # is located, the data entry form is displayed.

If the user enters a number less than ! in any of forms fields, a warning is given and they have an opportunity to enter another number.
 
this is excellent... a few problems...

1) when searching for a po if it already exists id like it to still allow it to be used but the numbers to be all zeros.

if it does not exist id like that po inserted but then the numbers that follow have to be greater than 0

so

PO exists = PO123456 , 0, 0, 0, 0
PO not exist = PO654321, 52, 685, 96, 4523
Not allow = PO654321, 52, 685, 0, 4523
 
Hi, i have tried all this in the work environment but getting complaints automatic input isn't fast enough and they don't like the PO check method...

Sorry to be a pain but could you change it to do it this way...
1) user inputs a PO number into the PO column..
2) in the background VBA checks for a duplicate in 'In Progress' and 'Completed' tabs...
3) If duplicate found then automatically input zeros in the four cells...
4) if not duplicate then popup an input box asking for data for the four cells, but do not allow any zeros there has to be numbers greater than 0.

This would be beneficial for speed entry and getting that validation that I need...

Again sorry and thank you :)
 
You must log in or register to reply here.
Back
Top