When using an input box, If the use 1

[bencnu]

When using an input box, If the user hits the cancel button instead of leaving what is currently in the cell, it clears it. Is there anyway around that?

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))"
End If
End If
End If

End Sub

Also with this code, if they hit cancel everytime anyother cell is clicked it will popup that input box till something is entered.

 

[Loomah]

Hi
I've just replied to your thread on if..then..else and this fits in with the solution I gave you there

Select Case Target.Address
    Case "$D$8"
        If (InputBox("Scan or Type Assembly Part Number", "NORTECH SYSTEMS INC.", " ")) = "" Then
            Exit Sub
        Else: Range("d8").Value = (InputBox("Scan or Type Assembly Part Number", "NORTECH SYSTEMS INC.", " "))
        End If
'and so on and on....

This isn't pretty and I would consider using a user form rather than input boxes as they are easier to control.
;-)

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

 

[bencnu]

thank you for the 2 posts, i will definetly give them a try. By the way what is a user form?

 

[Loomah]

Ah,
A User form (I've never tried to describe one before) allows a graphical interface between the user and the program. It can contain many items such as command buttons, list boxes, option buttons, text boxes etc.

An input box is like a user form with one text box and two command buttons. The big difference is that you have a lot more control over how a userform behaves. You could create your own cancel buttton and assign the necessary code directly to it.

If you're in the VB Editor then go INSERT>USERFORM
This will give you a blank form and should show you the Control Toolbox (the menu with the buttons etc for the form on it) Try adding some then double click them to see what events the handle.

Have fun, explore!
;-)

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

 

[bencnu]

ok cool, i'll try that
thanx!