select one of 6 cells down in column
 

good day..

i have a column where they need to select just one cell at a time. if for example E9 has a x then this but if user select E11 this and then E9 must be deselected automatically...


and perhaps any way to 'clean up' or make use of this easier for the end user?

pswa
nie gm
ail

select one of 6 cells down in column
 

Phillip Swanepoel wrote:

i have a column where they need to select just one cell at a time. if
for example E9 has a x then this but if user select E11 this and then E9
must be deselected automatically...

and perhaps any way to 'clean up' or make use of this easier for the end
user?

If you only want the user to select a single cell at a time, anywhere on the
sheet, you can do this in the sheet's object:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Cells.Count 1 Then ActiveCell.Select
End Sub

Alternately, you can specifically check for cells in column E in the
selection:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Range, addr As String
If Not (Application.Intersect(Target, Range("E:E")) Is Nothing) Then
For Each cel In Target
If cel.Column = 5 Then '5-column E
'Here is where you decide if the cell remains in the selection.
If cel.Value = "x" Then addr = addr & "," & cel.Address
Else
addr = addr & "," & cel.Address
End If
Next
If Len(addr) 0 Then Range(Mid$(addr, 2)).Select
End If
End Sub

....but note that this will lead to some rather unexpected behavior (related
to selecting individual cells, instead of groups).

--
If only the good die young, what does that say about senior citizens?

select one of 6 cells down in column
 

On Thursday, June 28, 2018 at 5:52:44 PM UTC+2, Auric__ wrote:
Phillip Swanepoel wrote:

i have a column where they need to select just one cell at a time. if
for example E9 has a x then this but if user select E11 this and then E9
must be deselected automatically...

and perhaps any way to 'clean up' or make use of this easier for the end
user?

If you only want the user to select a single cell at a time, anywhere on the
sheet, you can do this in the sheet's object:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Cells.Count 1 Then ActiveCell.Select
End Sub

Alternately, you can specifically check for cells in column E in the
selection:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Range, addr As String
If Not (Application.Intersect(Target, Range("E:E")) Is Nothing) Then
For Each cel In Target
If cel.Column = 5 Then '5-column E
'Here is where you decide if the cell remains in the selection.
If cel.Value = "x" Then addr = addr & "," & cel.Address
Else
addr = addr & "," & cel.Address
End If
Next
If Len(addr) 0 Then Range(Mid$(addr, 2)).Select
End If
End Sub

...but note that this will lead to some rather unexpected behavior (related
to selecting individual cells, instead of groups).

--
If only the good die young, what does that say about senior citizens?

Goodday


i posted the workbook here

please have a look?

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

select one of 6 cells down in column
 

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.

--
- Violence doesn't solve anything.
- Violence solves *everything*.

select one of 6 cells down in column
 

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.

--
- Violence doesn't solve anything.
- Violence solves *everything*.



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

select one of 6 cells down in column
 

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!

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 !!!