trouble with if thenelse statements

[bencnu]

Whats wrong with my code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Selection.Count = 1 Then
If barcode = "on" Then
If Selection = Range("d8&quot Then
Range("d8&quot.Value = (InputBox("Scan or Type Assembly Part Number", "NORTECH SYSTEMS INC.", " &quot)
Range("al8&quot.Value = (InputBox("Scan or Type Sequence Number", "NORTECH SYSTEMS INC.&quot)
Range("f8&quot.Formula = "=IF(ISBLANK(al8)=TRUE,TRIM(CHAR(32)),MID(al8,3,6))"
Range("g8&quot.Formula = "=IF(ISBLANK(al8)=TRUE,TRIM(CHAR(32)),MID(al8,16,2))"
ElseIf Selection = Range("d9&quot Then
Range("d9&quot.Value = (InputBox("Scan or Type Assembly Part Number", "NORTECH SYSTEMS INC.", " &quot)
Range("al9&quot.Value = (InputBox("Scan or Type Sequence Number", "NORTECH SYSTEMS INC.&quot)
Range("f9&quot.Formula = "=IF(ISBLANK(al9)=TRUE,TRIM(CHAR(32)),MID(al9,3,6))"
Range("g9&quot.Formula = "=IF(ISBLANK(al9)=TRUE,TRIM(CHAR(32)),MID(al9,16,2))"
ElseIf Selection = Range("d10&quot Then
Range("d10&quot.Value = (InputBox("Scan or Type Assembly Part Number", "NORTECH SYSTEMS INC.", " &quot)
Range("al10&quot.Value = (InputBox("Scan or Type Sequence Number", "NORTECH SYSTEMS INC.&quot)
Range("f10&quot.Formula = "=IF(ISBLANK(al10)=TRUE,TRIM(CHAR(32)),MID(al10,3,6))"
Range("g10&quot.Formula = "=IF(ISBLANK(al10)=TRUE,TRIM(CHAR(32)),MID(al10,16,2))"

........etc......

End If
End If
End If

End Sub



No matter what i click on the input box pops up in order of the code. For ex. If i click on f10(or anything) the input box for d8 will popup, then if i click on g17(anything) next the input box for d9 will popup, and so on and so forth. If you have any suggestions that would be very cool. Thanx

 

[Loomah]

Hi
Have a go with this and see if it does what you are after...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If barcode = "on" Then

Select Case Target.Address
    Case "$D$8"
        Range("d8").Value = (InputBox("Scan or Type Assembly Part Number", "NORTECH SYSTEMS INC.", " "))
        Range("al8").Value = (InputBox("Scan or Type Sequence Number", "NORTECH SYSTEMS INC."))
        Range("f8").Formula = "=IF(ISBLANK(al8)=TRUE,TRIM(CHAR(32)),MID(al8,3,6))"
        Range("g8").Formula = "=IF(ISBLANK(al8)=TRUE,TRIM(CHAR(32)),MID(al8,16,2))"
    
    Case "$D$9"
        Range("d9").Value = (InputBox("Scan or Type Assembly Part Number", "NORTECH SYSTEMS INC.", " "))
        Range("al9").Value = (InputBox("Scan or Type Sequence Number", "NORTECH SYSTEMS INC."))
        Range("f9").Formula = "=IF(ISBLANK(al9)=TRUE,TRIM(CHAR(32)),MID(al9,3,6))"
        Range("g9").Formula = "=IF(ISBLANK(al9)=TRUE,TRIM(CHAR(32)),MID(al9,16,2))"

    Case "$D$10"
        Range("d10").Value = (InputBox("Scan or Type Assembly Part Number", "NORTECH SYSTEMS INC.", " "))
        Range("al10").Value = (InputBox("Scan or Type Sequence Number", "NORTECH SYSTEMS INC."))
        Range("f10").Formula = "=IF(ISBLANK(al10)=TRUE,TRIM(CHAR(32)),MID(al10,3,6))"
        Range("g10").Formula = "=IF(ISBLANK(al10)=TRUE,TRIM(CHAR(32)),MID(al10,16,2))"
    Case Else
        'do nothing?!?
End Select
End If
End If

End Sub

;-)


If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[TonyJollans]

Hi bencnu,

What are you trying to do? Your code checks to see if the selected cell contains the same as cell D8; Loomah's post (which I suspect does as you want) checks to see if the selected cell IS cell D8. Big difference!

If you are doing (as it appears) the same for each of the chosen cells, and the only difference is the row number then it would be much easier to use some form of relative addressing and just code once, for example ..

If Selection.Count = 1 Then
If barcode = "on" Then

If Selection.Column = 4 _
  And Selection.Row >= 8 _
  And Selection.Row <= 10 Then

    Cells(Selection.Row, 4).Value = (InputBox(&quot;Scan or Type Assembly Part Number&quot;, &quot;NORTECH SYSTEMS INC.&quot;, &quot; &quot;))
    Cells(Selection.Row, 38).Value = (InputBox(&quot;Scan or Type Sequence Number&quot;, &quot;NORTECH SYSTEMS INC.&quot;))
    Cells(Selection.Row, 6).FormulaR1C1 = &quot;=IF(ISBLANK(RC[32])=TRUE,TRIM(CHAR(32)),MID(RC[32],3,6))&quot;
    Cells(Selection.Row, 7).FormulaR1C1 = &quot;=IF(ISBLANK(RC[32])=TRUE,TRIM(CHAR(32)),MID(RC[32],16,2))&quot;

End If

EndIf
EndIf

Enjoy,
Tony

 

[bencnu]

thank you guys for the input, that really helped me out !