Inputbox populating two cells with user input, would like it to populate only one.

[Novelec]

Hi guys,

I've been working on a code that references several named ranges, and operates a Marlett check box accordingly. Half of the named ranges also prompt an input box. (This is for a pass/fail style check sheet. The input box is for the user to enter a reason for a failure.) This is my first VB project, and I am self teaching as I go, so please forgive me for any silly mistakes.

My problem is that some of the input boxes are populating two cells with the data entered by the user. I have offset the input box data from the active cell (as the active cell is the Marlett check box), and it seems that the data is populating in all cells that are offset...? The two "if" statements following the input box code are the culprates. The offset commands in each of them seem to get combined upon action of the input box.

I've tried reordering the "if" statements, a combination of "else" statements, and have tried "goto" statements as well. I haven't had any success yet. I've listed the code below for reference.

I'm hoping I've just missed something simple in my inexperience. Any help you guru's can provide is most welcome.

Thanks in advance to those who share their knowledge and time.


<sub>Option Explicit
Dim User_Fault_Input As String

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Count > 1 Then
Exit Sub
End If

If Intersect(Target, Union(Range("Named_Range_1"), Range("Named_Range_2"), Range("Named_Range_3"), Range("Named_Range_4"))) Is Nothing Then
Exit Sub
End If

Target.Font.Name = "marlett"

If Target.Value <> "a" Then
Target.Value = "a"
Cancel = True
If Intersect(Target, Union(Range("Named_Range_1"), Range("Named_Range_3"))) Is Nothing Then
ActiveCell.Offset(0, 1) = ""
End If

If Intersect(Target, Union(Range("Named_Range_2"), Range("Named_Range_4"))) Is Nothing Then
ActiveCell.Offset(0, -1) = ""
End If

If Intersect(Target, Union(Range("Named_Range_2"), Range("Named_Range_4"))) Is Nothing Then
User_Fault_Input = InputBox("Equipment check unaccebtable - Please enter reason", "Equipment Check Unaccebtable", "Begin typing...")

If Intersect(Target, Range("Named_Range_4")) Is Nothing Then
ActiveCell.Offset(0, 3).Value = User_Fault_Input
End If

If Intersect(Target, Range("Named_Range_2")) Is Nothing Then
ActiveCell.Offset(0, 1).Value = User_Fault_Input
End If

If User_Fault_Input = "" Then
ActiveCell.ClearContents
End If
Exit Sub

End If
Exit Sub
End If

If Target.Value = "a" Then
Target.ClearContents
Cancel = True
Exit Sub
End If</sub>

 

[JLGWhiz]

These three If statements would put the value entered by the user in two cells, based on Intersect() being Nothing for two of the named ranges. What I would suggest is that you draw your logical path on paper as a block diagram so you can see how you need to write your If statements to get the result you want. I find that technique useful when I have a complex decision network to go through in a program.

Code:
If Intersect(Target, Union(Range("Named_Range_2"), Range("Named_Range_4"))) Is Nothing Then
User_Fault_Input = InputBox("Equipment check unaccebtable - Please enter reason", "Equipment Check Unaccebtable", "Begin typing...")

If Intersect(Target, Range("Named_Range_4")) Is Nothing Then
ActiveCell.Offset(0, 3).Value = User_Fault_Input
End If

If Intersect(Target, Range("Named_Range_2")) Is Nothing Then
ActiveCell.Offset(0, 1).Value = User_Fault_Input
End If
Code:

 

[Novelec]

Thanks for the reply JLGWhiz.

You identified that the current code for the input box will in fact output to two cells. Can you instruct me how to order things so "Named_Range_2" will only output to ActiceCell.Offset(0, 1), and "Named_Range_4" will only output to ActiceCell.Offset(0, 3) ?

I've got my process written down, and have tried ordering it several different ways to make it work, but have not had success yet. This is my first time wirking with code, so I guess I am missing something simple, like ordering, or placement of statements.

See original post with full code for problem area context.

Any assistance is greatly appreciated, cheers guys.