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 this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
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

 

[cmccabe]

That works prefectly. Is there a way to use that command to a range of cells? Instead of only using A3 use a range of A3 to A53? Thanks.

 

[VoG]

Try 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
End Sub

 

[cmccabe]

Works perfectly. Thanks a lot.

 

[cmccabe]

One last ?...

The last two data inputs are text:
Code 1 restricts the entry to a number, can that be modified to restrict the entry to text? Thanks.

Code 1

Cells(3, "E").Value = InputBox("Enter Age", "You must Enter a Value")
    [U]Do While Cells(3, "E").Value < 1 Or Cells(3, "E").Value > 150
[/U]    Cells(3, "E").Value = InputBox("Enter a number Between 1 and 150", "Input Out of Range!")
Loop

 

[VoG]

Maybe like this

Cells(3, "E").Value = InputBox("Enter Description", "You must Enter Text")
Do While IsNumeric(Cells(3, "E").Value) Or Cells(3, "E").Value = ""
    Cells(3, "E").Value = InputBox("Enter Description", "Error!")
Loop

 

[cmccabe]

Sorry, this is it hopefully.

When code 1 is used the user is asked for Case Number and then an Age. However when I go to the next row Case Number is entered on the next row but Age over-writes E3.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Is there a way that when A moves down a row so can E. They are both the same range A3:A53 and E3:E53. Thanks.

Code 1:

[FONT=Times New Roman][SIZE=3]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    If Intersect(Target, Range("A3:A53")) Is Nothing Then Exit Sub[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Cancel = True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Target.Value = InputBox("Enter Case Number Between 1 and 50", "You must Enter a Value")[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Do While Target.Value < 1 Or Target.Value > 50[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Target.Value = InputBox("Enter a number Between 1 and 50", "Input Out of Range!")[/FONT][/SIZE]
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on">[FONT=Times New Roman][SIZE=3]Loop[/SIZE][/FONT]</st1:place>
[SIZE=3][FONT=Times New Roman]   Cells(3, "E").Value = InputBox("Enter Age", "You must Enter a Value")[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   Do While Cells(3, "E").Value < 1 Or Cells(3, "E").Value > 150[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]   Cells(3, "E").Value = InputBox("Enter a number Between 1 and 150", "Input Out of Range!")[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] <st1:place w:st="on">Loop</st1:place>[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]

 

[VoG]

If column A is meant to be numeric and column E is meant to be text, try like this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("A3:A53,E3:E53")) 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 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]

The code works.

Code 1 is the final code:

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!")
    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

However when I enter this I get a SELECT Case error and Case 13 and 15 are highlighted. Thanks.