+ Reply to Thread
Results 1 to 3 of 3

Adding a field limitation (0-1 only) to a Private sub inputbox prompt.

Hybrid View

  1. #1
    Registered User
    Join Date
    02-29-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2013
    Posts
    10

    Adding a field limitation (0-1 only) to a Private sub inputbox prompt.

    Hello Excel Gurus,

    You helped me piece this sub together in an earlier thread, now I'm back for more. Here is the sub:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 13 Then Exit Sub
    If Target.Value = "Resolved" Or Target.Value = "Resolved Pending Validation" Then
        With ActiveSheet
            .Cells(Target.Row, 5) = InputBox("Please enter the Resolved Value")
            .Cells(Target.Row, 6) = InputBox("Please enter the Resolved Issue")
            .Cells(Target.Row, 7) = InputBox("Please enter the Percentage. Syntax for percentages: .85 = 85%.")
            .Cells(Target.Row, 14) = InputBox("Please enter the date of Resolution")
        End With
    ElseIf Target.Value = "Assigned" Then
        With ActiveSheet
            .Cells(Target.Row, 15) = InputBox("Please enter the Assigned date")
            MsgBox ("Please provide an estimate of resolution in the drop-down menu")
        End With
    End If
    
    End Sub
    My question is whether or not it is possible to add some sort of data entry limitations to the Target.Row, 7 = Inputbox entry. Instead of having the 'Syntax for percentages: .85 = 85%', I'd like it to return an error message if the user inputs a value outside of 0-1, and re-prompt them for an input.

    Thanks for your help on this!!!

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Adding a field limitation (0-1 only) to a Private sub inputbox prompt.

    Something like

    Do
    .Cells(Target.Row, 5) = InputBox("Please enter the Resolved Value for column E")
    Loop Until IsNumeric(.Cells(Target.Row, 5).Value) And .Cells(Target.Row, 5).V 1alue >=0 And .Cells(Target.Row, 5).Value <= 1

  3. #3
    Registered User
    Join Date
    02-29-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: Adding a field limitation (0-1 only) to a Private sub inputbox prompt.

    That works - thank you!

+ 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