Only select one cell at a time in excel

[bencnu]

Is there a way to have excel only let the user select one cell at a time? The code I have displayed below, gives me an error everytime a user selects more than one cell at a time.....


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Selection = Range("b10&quot Then
Range("B10&quot.Value = (InputBox("Scan or Type Assembly Part Number", "NORTECH SYSTEMS INC.&quot)
Range("B100&quot.Value = (InputBox("Scan or Type Sequence Number", "NORTECH SYSTEMS INC.&quot)
Range("d10&quot.Formula = "=IF(ISBLANK(B100)=TRUE,TRIM(CHAR(32)),MID(b100,3,6))"
Range("f10&quot.Formula = "=IF(ISBLANK(B100)=TRUE,TRIM(CHAR(32)),MID(b100,16,2))"
End If

End Sub

 

[Zathras]

You could try this variation:
[blue]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("b10")) Is Nothing Then
  If Target.Cells.Count = 1 Then
    Range("B10").Value = (InputBox("Scan or Type Assembly Part Number", "NORTECH SYSTEMS INC."))
    Range("B100").Value = (InputBox("Scan or Type Sequence Number", "NORTECH SYSTEMS INC."))
    Range("d10").Formula = "=IF(ISBLANK(B100)=TRUE,TRIM(CHAR(32)),MID(b100,3,6))"
    Range("f10").Formula = "=IF(ISBLANK(B100)=TRUE,TRIM(CHAR(32)),MID(b100,16,2))"
  End If
End If
End Sub

[/color]

 

[TonyJollans]

Hi bencnu,

I would check the other way round from Zathras ..

If Selection.Count = 1
If Selection = Range("B10&quot Then
.. etc ..
End If
End If

.. saves bothering with the Intersection.

Or you could use ActiveCell or Selection(1) instead of just Selection. Depends on what you want to happen.

Enjoy,
Tony

 

[Zathras]

Tony must be away from his machine. Here is what he meant to post:
[blue]

If Target.Cells.Count = 1 Then
  If Target = Range("B10") Then
    etc...

[/color]

 

[TonyJollans]

Hi Zathras,

You're probably right about what I should have posted (and I didn't check it out) but bencnu is using Selection and there isn't any difference I'm aware of between Selection and Target in the context. And, although perhaps sloppy, Selection.Count will give the count of cells in the Selection.

Enjoy,
Tony

 

[Zathras]

Tony, no argument there, but when I tried your code

  If Selection.Count = 1

didn't compile. But I see now that it was the lack of "Then" rather than ".Cells" that was the problem.

So, for the record, what I think you meant to say is

If Selection.Count = 1

[blue]

 Then

[/color]

  If Selection = Range("B10") Then
    .. etc ..
  End If
End If

[/color]

 

[bencnu]

Thanks you very much! The information you 2 gave me were exactly what I was looking for. Thanks again!

BENCNU

 

[TonyJollans]

Correct Zathras,

That sort of silly mistake gets picked up straight away in the VBA editor but not when typed in here.

It has got me thinking though. If there really isn't any difference between Selection and Target why does Excel pass Target as a parameter to the routine?

Enjoy,
Tony