Input Data box open based on cells value condition unload in different cells

mamun.ges · Feb 24th 2022

Hi All,

Hope Everyone is all right.

I have searched many forums the fact below that make me stuck. Hope someone here will help me.

I have a Large datasheet. In this datasheet, I want to use input box code to simplify my data entry. But I am unable to do it on my own as I am not an expert. So I need Expert help.

This is the table where the ROW heading is in ROW 8.

B	C	D	E	F	G	AA	AQ
Types			Open Date			Saving	Loan
Debit			12/12/2021				18/03/2022
Credit			11/11/2021			13/01/2022
			13/01/2020			20/11/2021

In the above table when E column data is entered then an input box opens based on B column value.

if B column value is Debit then the input box opens to ask for "Date of Loan Creation" after the date enter it unload in AQ Cell and will show a message as "Loan Creation date is 18/02/2022.

if B column has Credit or nothing then the input box opens to ask for "Date of Savings Entry Creation" after the date enter it unload in AA Cell and will show a message as "Savings Entry date is 13/01/2022. All Date will be "dd/mm/yyyy"

Hope I am able to describe the fact and someone will help me to ease my data entry.

Thanks in advance.

**royUK** · Feb 24th 2022

I would suggest a UserForm, see my DatabaseForm example on my website, download link in my signature below.

mamun.ges · Feb 24th 2022

Thanks royUK again.

I am not very good at UserForm data entry, but is it possible to do it by VBA using input box data entry then it will be very easy for me to understand.

Hope you understand my limitation.

Thanks

**KjBox** · Feb 24th 2022

Try this, paste the code to the Worksheet Object Module for the sheet concerned

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sMsg1 As String, sMsg2 As String, sMsg3 As String, s As String
    sMsg1 = "Loan Creation date is "
    sMsg2 = "Savings Entry date is "
    sMsg3 = ". All Date will be ""dd/mm/yyyy"""
    
    If Target.Count > 1 Then Exit Sub
    If Target.Row > 8 And Target.Column = 5 Then
        If Cells(Target.Row, 2) = "Debit" Then
            s = InputBox("Date of Loan Creation", "Loan Creation")
            If Len(s) Then
                If IsDate(s) Then
                    Cells(Target.Row, 43) = Format(CDate(s), "dd/mm/yyyy")
                    MsgBox sMsg1 & Format(CDate(s), "dd/mm/yyyy")
                End If
            End If
        Else
            s = InputBox("Date of Savings Entry Creation", "Saving Entry Creation")
            If Len(s) Then
                If IsDate(s) Then
                    Cells(Target.Row, 27) = Format(CDate(s), "dd/mm/yyyy")
                    MsgBox sMsg2 & Format(CDate(s), "dd/mm/yyyy") & sMsg3
                End If
            End If
        End If
    End If
    
End Sub

Display More

mamun.ges · Feb 25th 2022

Thank you KjBox,

It works as expected as I want and appreciate you for the code.

By the way, Can a triple condition be applied in this code

Like

In the above table when E column data is entered then an input box opens based on B column value.

Condition 1:if B column value is Debit then the input box opens to ask for "Date of Loan Creation" after the date enter it unload in AQ Cell and will show a message as "Loan Creation date is 18/02/2022.

Condition 2: if B column has Credit or Other then the input box opens to ask for "Date of Savings Entry Creation" after the date enter it unload in AA Cell and will show a message as "Savings Entry date is 13/01/2022.

Condition 3: if B column has Nothing then no input box opens or skip.

Thanks and Best regards

**KjBox** · Feb 25th 2022

This will now do nothing if Column B is empty for the target row.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sMsg1 As String, sMsg2 As String, sMsg3 As String, s As String
    sMsg1 = "Loan Creation date is "
    sMsg2 = "Savings Entry date is "
    sMsg3 = ". All Date will be ""dd/mm/yyyy"""
    
    If Target.Count > 1 Then Exit Sub
    If Target.Row > 8 And Target.Column = 5 Then
        If Cells(Target.Row, 2) = "Debit" Then
            s = InputBox("Date of Loan Creation", "Loan Creation")
            If Len(s) Then
                If IsDate(s) Then
                    Cells(Target.Row, 43) = Format(CDate(s), "dd/mm/yyyy")
                    MsgBox sMsg1 & Format(CDate(s), "dd/mm/yyyy")
                End If
            End If
        ElseIf Len(Cells(Target.Row, 2)) Then
            s = InputBox("Date of Savings Entry Creation", "Saving Entry Creation")
            If Len(s) Then
                If IsDate(s) Then
                    Cells(Target.Row, 27) = Format(CDate(s), "dd/mm/yyyy")
                    MsgBox sMsg2 & Format(CDate(s), "dd/mm/yyyy") & sMsg3
                End If
            End If
        End If
    End If
    
End Sub

Display More

mamun.ges · Feb 25th 2022

Hi, KjBox,

Thanks for your feedback,

If you don't mind, I am asking what is code for condition 2.

that is; Condition 2: if B column has "Credit or Other" then the input box opens to ask for "Date of Savings Entry Creation" after the date enter it unload in AA Cell and will show a message as "Savings Entry date is 13/01/2022.

Hope you help me to understand.

Thanks and best regards.

**KjBox** · Feb 25th 2022

Like this?

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sMsg1 As String, sMsg2 As String, sMsg3 As String, s As String
    sMsg1 = "Loan Creation date is "
    sMsg2 = "Savings Entry date is "
    sMsg3 = ". All Date will be ""dd/mm/yyyy"""
    
    If Target.Count > 1 Then Exit Sub
    If Target.Row > 8 And Target.Column = 5 Then
        If Cells(Target.Row, 2) = "Debit" Then
            s = InputBox("Date of Loan Creation", "Loan Creation")
            If Len(s) Then
                If IsDate(s) Then
                    Cells(Target.Row, 43) = Format(CDate(s), "dd/mm/yyyy")
                    MsgBox sMsg1 & Format(CDate(s), "dd/mm/yyyy")
                End If
            End If
        ElseIf Cells(Target.Row, 2) = "Credit or Other" Then
            s = InputBox("Date of Savings Entry Creation", "Saving Entry Creation")
            If Len(s) Then
                If IsDate(s) Then
                    Cells(Target.Row, 27) = Format(CDate(s), "dd/mm/yyyy")
                    MsgBox sMsg2 & Format(CDate(s), "dd/mm/yyyy") & sMsg3
                End If
            End If
        End If
    End If
    
End Sub

Display More

mamun.ges · Feb 25th 2022

Hi, Kjbox,

Thanks for your feedback again,

Can I write code row 17 like below

Code

ElseIf Cells(Target.Row, 2) like "*" & "Credit" "*" or "*" & "Other" & "*" Then

If not then how to write.

Thank you.

**KjBox** · Feb 25th 2022

Code

        ElseIf Cells(Target.Row, 2) Like "*Credit" Or Cells(Target.Row, 2) Like "*Other*" Then

mamun.ges · Feb 26th 2022

Thanks, KjBox your code is excellent.

**KjBox** · Feb 26th 2022

You're welcome. I do think you should consider a User Form as a means of data entry, as suggested by royUK, the conditions needed and messages can easily be incorporated in the User Form code.

**royUK** · Feb 27th 2022

If you only want to make input in one column then an inputbox will do, although a UserForm is a more professional approach.

mamun.ges · Feb 27th 2022

Thanks, RoyUK and KjBox for the expert opinion.

Can you give an example worksheet or sample file to understand how a user form entry works.

Thanks and best regards,

**KjBox** · Feb 28th 2022

Roy provided links to his web site where samples of user forms can be found.

Input Data box open based on cells value condition unload in different cells

Participate now!

Share

Tags