Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 28th 18, 08:33 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2018
Posts: 4
Default 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

  #2   Report Post  
Old June 28th 18, 04:52 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 506
Default 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?
  #3   Report Post  
Old June 29th 18, 06:59 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2018
Posts: 4
Default 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
  #4   Report Post  
Old June 29th 18, 04:35 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 506
Default 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*.
  #5   Report Post  
Old June 29th 18, 05:56 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2018
Posts: 4
Default 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


  #6   Report Post  
Old June 30th 18, 07:39 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 506
Default 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!
  #7   Report Post  
Old June 30th 18, 08:06 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2018
Posts: 4
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 !!!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select and sum all numbered cells in Column A Lane Smith Excel Programming 2 January 30th 11 11:11 PM
How to select certain cells in a column ? Mark246 Excel Discussion (Misc queries) 1 February 25th 08 10:08 PM
Select Cells In Column that have data Sean[_15_] Excel Programming 4 May 15th 06 04:56 PM
Column Width for a select # of cells April New Users to Excel 1 July 7th 05 10:06 PM
Select cells in a column Evan Excel Programming 3 July 30th 04 07:29 AM


All times are GMT +1. The time now is 09:39 PM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017