+ Reply to Thread
Results 1 to 2 of 2

Limiting results of capture input

Hybrid View

  1. #1
    Registered User
    Join Date
    06-09-2009
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    88

    Limiting results of capture input

    I have the following code:

    Public Sub CaptureInput()
    Dim vResponses(1 To 8) As Variant
    vResponses(1) = Application.InputBox("Enter Coordinates of Farm", "Farm", "", Type:=2)
    vResponses(2) = Application.InputBox("Enter Coordinates of the village listed in the above heading", "Village", "", Type:=2)
    vResponses(3) = Application.InputBox("Enter Hours Since Last Farm", "Hours", "", Type:=1)
    vResponses(4) = Application.InputBox("Enter Timber Level", "Timber", "", Type:=1)
    vResponses(5) = Application.InputBox("Enter Clay Level", "Clay", "", Type:=1)
    vResponses(6) = Application.InputBox("Enter Iron Level", "Iron", "", Type:=1)
    vResponses(7) = Application.InputBox("Enter Warehouse Level", "Warehouse", "", Type:=1)
    vResponses(8) = Application.InputBox("Enter Hiding Place Level", "Hiding", "", Type:=1)
    If IsNumeric(Application.Match(False, vResponses, 0)) Then
        MsgBox "Not All Entries Complete - Action Cancelled", vbCritical, "Incomplete"
    Else
    Dim sht As Worksheet
    Dim dest As Worksheet
    Dim targetRange As Range
    
    Set sht = ActiveWorkbook.Worksheets("Info")
    Set dest = ActiveSheet
    
    Set targetRange = dest.Cells(Rows.Count, "A").End(xlUp).Offset(1)
    
    sht.Range(sht.Cells(42, "A"), sht.Cells(42, "AH")).Copy targetRange
        With Cells(Rows.Count, "a").End(xlUp)
            'farm
            .Value = vResponses(1)
            'village
            .Offset(, 1) = vResponses(2)
            'Time
            .Offset(, 3).Value = vResponses(3)
            'Timber
            .Offset(, 5).Value = vResponses(4)
            'Clay
            .Offset(, 8).Value = vResponses(5)
            'iron
            .Offset(, 11).Value = vResponses(6)
            'warehouse
            .Offset(, 14).Value = vResponses(7)
            'hiding
            .Offset(, 16).Value = vResponses(8)
        End With
        ActiveSheet.Calculate
    End If
    End Sub
    I want to be able to limit the numbers that can be returned. for example for vresponse 4 I need the number to be between 0 and 30. And if it's not to come up with an error message before you go to the next one. Is this possible? or something along those lines

    Also, a harder one. For vresponse 1 i need to have the outcome in the format of "xxx|yyy". So, the x's and y's represent numbers. So it should return an error if there is like no "|". the thing is the thing that's entered could be anything such as: 1|1, 1|999, 999|1, 999|999 and so on. So it can't be like there has to be a "|" 3 digits in. I would be happy if you had something that gives an error if what you entered didn't contain a "|".

    Just to clarify, it wouldn't check for this error until after you click ok

    Thank you for any help you can give

  2. #2
    Registered User
    Join Date
    06-09-2009
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: Limiting results of capture input

    Any ideas on how I could at least do part of this?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1