question

[cmccabe]

Is there a way in excel 2003 that a user can double click a cell and a user prompt asking to input data results? If I use code 1 I can use a macro it seems, but can it be modified so that a double click is required. Code 2 is what I thought of but it does not seem to work. Thanks.

Code 1

Sub Data ()
Cells(3, "A").Value = InputBox("Enter Case Number Between 1 and 50", "You must Enter a Value")
    Do While Cells(3, "A").Value < 1 Or Cells(3, "A").Value > 50
    Cells(3, "A").Value = InputBox("Enter a number Between 1 and 50", "Input Out of Range!")
    Loop
End Sub

Code 2 (HSCBM is the workshhet name)

Private Sub HSCBM_RD_BeforeDoubleClick_()
    Cells(3, "A").Value = InputBox("Enter Case Number Between 1 and 50", "You must Enter a Value")
    Do While Cells(3, "A").Value < 1 Or Cells(3, "A").Value > 50
    Cells(3, "A").Value = InputBox("Enter a number Between 1 and 50", "Input Out of Range!")
    Loop
End Sub

 

[VoG]

Try

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("A3:A53,E3:E53,M3:M53,O3:O53")) Is Nothing Then Exit Sub
Cancel = True
Select Case Target.Column
    Case 1
        Target.Value = InputBox("Enter Case Number Between 1 and 50", "You must Enter a Value")
        Do While Target.Value < 1 Or Target.Value > 50
            Target.Value = InputBox("Enter a number Between 1 and 50", "Input Out of Range!")
        Loop
    Case 5
        Target.Value = InputBox("Enter Age", "You must Enter a Value")
        Do While Target.Value < 1 Or Target.Value > 150
            Target.Value = InputBox("Enter a number Between 1 and 150", "Input Out of Range!")
        Loop
    Case 13
        Target.Value = InputBox("Enter Donor", "You must Enter Text")
        Do While IsNumeric(Target.Value) Or Target.Value = ""
            Target.Value = InputBox("Enter Donor", "Error!")
        Loop
     Case 15
        Target.Value = InputBox("Enter Description", "You must Enter Text")
        Do While IsNumeric(Target.Value) Or Target.Value = ""
            Target.Value = InputBox("Enter Description", "Error!")
        Loop
End Select
End Sub

 

[cmccabe]

Is there a simple way to have all the user prompts linked to one cell?

So when the user clicks cell A3 he is asked for the case, after it is entered he is asked for the age, after it is ententered he is asked for the donor, and after it is entered he is asked for a description. Then the next row is ready to be entered. Thanks.

 

[VoG]

Maybe like this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("A3:A53")) Is Nothing Then Exit Sub
Cancel = True
Target.Value = InputBox("Enter Case Number Between 1 and 50", "You must Enter a Value")
Do While Target.Value < 1 Or Target.Value > 50
    Target.Value = InputBox("Enter a number Between 1 and 50", "Input Out of Range!")
Loop
Target.Offset(, 4).Value = InputBox("Enter Age", "You must Enter a Value")
Do While Target.Offset(, 4).Value < 1 Or Target.Offset(, 4).Value > 150
    Target.Offset(, 4).Value = InputBox("Enter a number Between 1 and 150", "Input Out of Range!")
Loop
Target.Offset(, 12).Value = InputBox("Enter Donor", "You must Enter Text")
Do While IsNumeric(Target.Offset(, 12).Value) Or Target.Offset(, 12).Value = ""
    Target.Offset(, 12).Value = InputBox("Enter Donor", "Error!")
Loop
Target.Offset(, 14).Value = InputBox("Enter Description", "You must Enter Text")
Do While IsNumeric(Target.Offset(, 14).Value) Or Target.Offset(, 14).Value = ""
    Target.Offset(, 14).Value = InputBox("Enter Description", "Error!")
Loop
End Sub

 

[cmccabe]

Thanks. When the input prompt is displayed there is a cancel button on it that doesnt do anything, is this how it should be?

 

[VoG]

You cannot get rid of the Cancel button if that's what you mean.

 

[cmccabe]

Can the cancel button be used if it is selected. Right now nothing happens. Thanks.

 

[VoG]

Try

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim x As Variant
If Intersect(Target, Range("A3:A53")) Is Nothing Then Exit Sub
Cancel = True
x = application.InputBox("Enter Case Number Between 1 and 50", "You must Enter a Value or click Cancel to exit this procedure",type:=1)
If x = False Then Exit Sub
Target.Value = x
Do While Target.Value < 1 Or Target.Value > 50
    Target.Value = InputBox("Enter a number Between 1 and 50", "Input Out of Range!")
Loop
Target.Offset(, 4).Value = InputBox("Enter Age", "You must Enter a Value")
Do While Target.Offset(, 4).Value < 1 Or Target.Offset(, 4).Value > 150
    Target.Offset(, 4).Value = InputBox("Enter a number Between 1 and 150", "Input Out of Range!")
Loop
Target.Offset(, 12).Value = InputBox("Enter Donor", "You must Enter Text")
Do While IsNumeric(Target.Offset(, 12).Value) Or Target.Offset(, 12).Value = ""
    Target.Offset(, 12).Value = InputBox("Enter Donor", "Error!")
Loop
Target.Offset(, 14).Value = InputBox("Enter Description", "You must Enter Text")
Do While IsNumeric(Target.Offset(, 14).Value) Or Target.Offset(, 14).Value = ""
    Target.Offset(, 14).Value = InputBox("Enter Description", "Error!")
Loop
End Sub

 

[Rick Rothstein]

Can the cancel button be used if it is selected. Right now nothing happens. Thanks.

Used for what? If the Cancel button is pressed, the InputBox function returns the empty string ("") which can be tested for in your code. One would think if the user pressed the Cancel button (or alternately entered nothing in the edit field), that they wanted to stop the entry process (maybe they realized they did not want to proceed for some reason). If that is how you want to proceed, then just Exit Sub in response to the InputBox returning the empty string.

 

[mikerickson]

Perhaps this.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim colRRay As Variant, headerRRay As Variant
    Dim highLimit As Variant, lowLimit As Variant
    Dim curRow As Long, curCol As Long, curLow As Variant, curHigh As Variant
    Dim curHeader As String, curPrompt As String, curType As Long
    Dim userEntry As Variant
    
    colRRay = Array(1, 5, 13, 15)
    
    headerRRay = Array("Case", "Age", "Donor", "Description")
    highLimit = Array(50, 150, Chr(200), Chr(200))
    lowLimit = Array(1, 1, Chr(5), Chr(5))
    
    If IsNumeric(Application.Match(Target.Column, colRRay, 0)) And 2 < Target.Row And Target.Row < 53 Then
        Cancel = True
        curRow = Target.Row
        curCol = Target.Column
        Do
            curHigh = WorksheetFunction.Lookup(curCol, colRRay, highLimit)
            curLow = WorksheetFunction.Lookup(curCol, colRRay, lowLimit)
            curHeader = "Enter then " & WorksheetFunction.Lookup(curCol, colRRay, headerRRay)
            curType = 2 + IsNumeric(curHigh)
            curPrompt = curHeader
            Cells(curRow, curCol).Select
            Do
               userEntry = Application.InputBox(curPrompt, Type:=curType)
               curPrompt = curHeader & vbCr & vbCr & "Value must be between " & curLow & " and " & curHigh
               If userEntry = False Then Exit Sub
            Loop Until curLow <= userEntry And userEntry <= curHigh
            Cells(curRow, curCol).Value = userEntry
            
            curCol = colRRay(Application.Match(curCol, colRRay, 0) Mod (UBound(colRRay) + 1))
            curRow = curRow - (curCol = colRRay(0))
        Loop While True
    End If
End Sub

 

[cmccabe]

I have a few cells on that spreadsheet that are lists. Using data validation I have those cells appear as a list. Is there a way using VBA that that list can be referenced? So if I have a saved list called name. Is there a way th( list can be rreferenced when the user is prompted. Instead of rntring text a pulldown list results. Thanks.