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!

Only select one cell at a time in excel

Status
Not open for further replies.

bencnu

Technical User
Nov 3, 2003
34
US
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") 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 Sub
 
You could try this variation:
[blue]
Code:
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]

 
Hi bencnu,

I would check the other way round from Zathras ..

If Selection.Count = 1
If Selection = Range("B10") 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
 
Tony must be away from his machine. Here is what he meant to post:
[blue]
Code:
If Target.Cells.Count = 1 Then
  If Target = Range("B10") Then
    etc...
[/color]

 
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
 
Tony, no argument there, but when I tried your code
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
Code:
If Selection.Count = 1
[blue]
Code:
 Then
[/color]
Code:
  If Selection = Range("B10") Then
    .. etc ..
  End If
End If
[/color]

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

BENCNU
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top