Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

trouble with if thenelse statements

Status
Not open for further replies.

bencnu

Technical User
Nov 3, 2003
34
US
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") Then
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))"
ElseIf Selection = Range("d9") Then
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))"
ElseIf Selection = Range("d10") Then
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))"

........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 :)
 
Hi
Have a go with this and see if it does what you are after...

Code:
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? [ponder]
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244
 
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 ..

Code:
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
 
thank you guys for the input, that really helped me out :)!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top