1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by Kurt Smart, Apr 16, 2018.

  1. Kurt Smart

    Kurt Smart Member

    Messages:
    41
    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
  2. Logit

    Logit Active Member

    Messages:
    259
    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 ?
  3. Kurt Smart

    Kurt Smart Member

    Messages:
    41
    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.
  4. Logit

    Logit Active Member

    Messages:
    259
    Please post a copy of your workbook, absent any confidential data.
  5. Kurt Smart

    Kurt Smart Member

    Messages:
    41
  6. Logit

    Logit Active Member

    Messages:
    259
    Sorry .. the link does not provide any download.

    ???
  7. Kurt Smart

    Kurt Smart Member

    Messages:
    41
  8. Logit

    Logit Active Member

    Messages:
    259
    Which sheet tab is the user entering in H2 ?
  9. Kurt Smart

    Kurt Smart Member

    Messages:
    41
    In Progress.... Sorry yes forgot the important detail there
  10. Logit

    Logit Active Member

    Messages:
    259
    .
    Paste in the InProgress sheet level module, below all existing macros :

    Code (vb):

    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 (vb):

    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 (vb):


    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.
  11. Kurt Smart

    Kurt Smart Member

    Messages:
    41
    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
  12. Logit

    Logit Active Member

    Messages:
    259
  13. Logit

    Logit Active Member

    Messages:
    259
  14. Kurt Smart

    Kurt Smart Member

    Messages:
    41
    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 :)
  15. Logit

    Logit Active Member

    Messages:
    259

Share This Page