View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.programming
Phillip Swanepoel Phillip Swanepoel is offline
external usenet poster
 
Posts: 21
Default select one of 6 cells down in column

On Saturday, June 30, 2018 at 8:39:44 AM UTC+2, Auric__ wrote:
Phillip Swanepoel wrote:

On Friday, June 29, 2018 at 5:35:22 PM UTC+2, Auric__ wrote:
Phillip Swanepoel wrote:

i posted the workbook here

please have a look?

https://groups.google.com/forum/#!to...os/tq2YZKPF_-c

There seems to be something wrong with that download. Post it again,
somewhere besides Google Groups.


not sure as to what options and how to upload somewhere besides google
groups...

will this work?

http://www.home-bound.co.za/saladlabel.zip


Much better, yes.

So, getting back to your original post, if you want it to happen that if
someone clicks on a cell in column E, the "X" is automatically moved from
its current location to the select cell (this is not "selecting" in the
sense that Excel uses) you could try this (in "data sheet's" object):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If the top of the table can vary,
'you'll have to deal with it some other way.
Const topOfE = 9
Dim ro As Integer
'Since you're using a colored background,
'we'll use that to determine the limits of the table.
If (Target.Column = 5) And (Target.Interior.Color < vbWhite) Then
'I don't know what good protecting the sheet does for you,
'but we'll have to unprotect it to make changes.
ActiveSheet.Unprotect
For ro = topOfE To Cells.SpecialCells(xlCellTypeLastCell).Row
'Using background color again.
If vbWhite = Cells(ro, 5).Interior.Color Then Exit For
Cells(ro, 5).Value = ""
Next ro
Target.Value = "x"
'Reprotecting.
ActiveSheet.Protect
End If
End Sub

Note that this will not prevent your users from selecting multiple products,
but your "SELECT ONLY ONE PRODUCT" banner should get the point across.

--
Thus it is written!


perfect!!! thank you !!!