Edit line in code

[Pol04]

Hi,

I have Windows 7 and using Excel 2010.

I am using this code to prompt for additional info when an entry is made in the file. Currently it is asking for an entry for the next box. I need it to ask for an entry for the next 3 boxes too but cannot figure out what to add.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myComment As String
'
If Target.Column = 1 And Target.Count = 1 Then
Application.EnableEvents = False
myComment = InputBox("Please enter Room Number or n/a, otherwise you entry will be deleted", "ENTER COMMENT")
If Len(myComment) > 0 Then
Target.Offset(0, 1) = myComment
Else
Target.ClearContents
End If
Application.EnableEvents = True
End If


End Sub

 

[Teeroy]

Something like;

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myComment As String
Dim myQuestions(1 To 4) As String
Dim myPrompts(1 To 4) As String
Dim i As Integer
'
If Target.Column = 1 And Target.Count = 1 Then
    Application.EnableEvents = False
    myQuestions(1) = "Please enter Room Number or n/a, otherwise you entry will be deleted"
    myQuestions(2) = "Another question"
    myQuestions(3) = "Yet anther question"
    myQuestions(4) = "Last question (I promise)"
    
    myPrompts(1) = "ENTER COMMENT1"
    myPrompts(2) = "ENTER COMMENT2"
    myPrompts(3) = "ENTER COMMENT3"
    myPrompts(4) = "ENTER COMMENT4"
    
    For i = 1 To 4
        myComment = InputBox(myQuestions(i), myPrompts(i))
        If Len(myComment) > 0 Then
            Target.Offset(0, i) = myComment
        Else
            Target.ClearContents
        End If
    Next i
    Application.EnableEvents = True
End If

Since this code overwrites cells test on a COPY of your workbook first.

 

[dmt32]

Hi,
A way to handle multiple input box prompts is to use an Array to gather inputs.

See if this code helps you:

 Option Explicit
 Option Base 1
Private Sub Worksheet_Change(ByVal Target As Range)


    Dim Prompt As Variant, Title As Variant
    Dim MyComment() As Variant
    Dim i As Integer, msg As Integer


    'Intialize Prompt & Title Arrays
    Prompt = Array("Room Number", "Box 1", "Box 2", "Box 3")


    Title = Array("ENTER COMMENT", "TITLE 1", "TITLE 2", "TITLE 3")


    
    On Error GoTo exitsub
    If Target.Column = 1 And Target.Count = 1 Then
        Application.EnableEvents = False
        
        i = LBound(Prompt)
        
        'size array
        ReDim MyComment(UBound(Prompt))
        
        Do
            MyComment(i) = InputBox("Please enter " & Prompt(i), Title(i))
            If StrPtr(MyComment(i)) = 0 Then Target.ClearContents: GoTo exitsub


            If Len(MyComment(i)) > 0 Then
                i = i + 1
            Else
                msg = MsgBox("Please enter " & Prompt(i) & " or n/a" & Chr(10) & _
                             "otherwise you entry will be deleted." & Chr(10) & Chr(10) & _
                             "Do You Want To Continue?", 36, "Entry Required")
                If msg = 7 Then Target.ClearContents: GoTo exitsub
            End If
        Loop Until i > UBound(Prompt)


        'ouput array to range
        Target.Offset(0, 1).Resize(1, UBound(Prompt)).Value = MyComment
    End If


exitsub:
    Application.EnableEvents = True
End Sub

I have assumed that the “Box” values as you call them go in the next cells to Target Cell (B, C,D,E) but you should adjust as required.
You will need to change Array values for Prompt & Title shown in RED to meet your requirements.

Hope Heplful

Dave

 

[Pol04]

Thanks Teeroy and dmt32. Both codes work as i want them to. Will have a play with both to see which one to use.

Is there a way for me to up your reputation here in mr excel?

 

[Teeroy]

Glad you got some solutions that worked for you.

There are a lot of ways to do things in VBA and it often comes down to your personal preference. There's not actually much difference in the methods dmt32 & I used (at least in the input arrays). I created the input arrays long hand to help explain the method while dmt32 used the Array function to build the arrays. dmt32's output array is more efficient for writing to the sheet but I don't bother if there is user input involved as their input time contributes more to the run time than multiple writes to the sheet.

FYI this forum doesn't have reputation points.