Use of check boxes
 

Hi,

I am devising a spreadsheet to log the progress on a list of clients,
through a set series of procedures. The idea is that each time a certain
stage has been passed, the corresponding date is entered in the relevant
column, at that client's row.

I have thought about using check boxes by each date column, so that when the
box is checked, the corresponding date is automatically filled in.

I know that one can 'tie' a cell to a check box, and it will flip from
'True' to 'False' accordingly. I should like that box to show today's date
when ticked, and revert to blank when unticked.

If it works, there will be several hundred of these little critters, and
copying them can be a problem - they all want to 'tie' themselves to the same
cell, when I use the normal copy and paste function.

If anyone can advise on code to get the tied cell to show correct info.
rather than just True/False, I'd be much obliged.

If anyone can advise on how to get copied check boxes automatically to refer
to the next cell down, or in line, or some kind of automation/macro that I
could set up, then I'd be even more grateful.

If anyone thinks that the check-box idea is fatally flawed and/or can
propose a more elegant solution, then please let me have your thoughts.

Thank you,

Lambs

Use of check boxes
 

Instead of directly modifying the cell data in the spreadsheet ...

Create a userform with the various stages identified (with check
boxes)

The client can be selected from a dropdown, and the checkboxes can
display the current status of that client.

Then, when the "next" checkbox is clicked, the (current?) date is
entered into the appropriate column.


--
weavtennis
------------------------------------------------------------------------
weavtennis's Profile: http://www.excelforum.com/member.php...fo&userid=3634
View this thread: http://www.excelforum.com/showthread...hreadid=514351

Use of check boxes
 

Here is a way without checkboxes. All you do is select a flagging column (I
have defined, B,D,F,H), and the date pops up next door.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "B:B,D:D,F:F,H:H"

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Font.Name = "Marlett"
.Value = "a"
.Offset(0, 1).Value = Date
.Offset(0, 1).NumberFormat = "dd mmm"
End With
End If

End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Lambs" wrote in message
...
Hi,

I am devising a spreadsheet to log the progress on a list of clients,
through a set series of procedures. The idea is that each time a certain
stage has been passed, the corresponding date is entered in the relevant
column, at that client's row.

I have thought about using check boxes by each date column, so that when
the
box is checked, the corresponding date is automatically filled in.

I know that one can 'tie' a cell to a check box, and it will flip from
'True' to 'False' accordingly. I should like that box to show today's date
when ticked, and revert to blank when unticked.

If it works, there will be several hundred of these little critters, and
copying them can be a problem - they all want to 'tie' themselves to the
same
cell, when I use the normal copy and paste function.

If anyone can advise on code to get the tied cell to show correct info.
rather than just True/False, I'd be much obliged.

If anyone can advise on how to get copied check boxes automatically to
refer
to the next cell down, or in line, or some kind of automation/macro that I
could set up, then I'd be even more grateful.

If anyone thinks that the check-box idea is fatally flawed and/or can
propose a more elegant solution, then please let me have your thoughts.

Thank you,

Lambs

Use of check boxes
 

Weavtennis,

Thanks for your reply. I see some merit in User Forms, but the idea is for
an operator to deal with a number of clients in quick succession, which means
ticking a few boxes on-screen, and taking a couple of seconds to deal with,
say, half a dozen clients.

It follows that firing up a User Form for each client would slow things down
- certainly the intended users of this spreadsheet would find it overly
complex.

Many thanks, all the same.

Regards,

Lambs
"weavtennis" wrote:


Instead of directly modifying the cell data in the spreadsheet ...

Create a userform with the various stages identified (with check
boxes)

The client can be selected from a dropdown, and the checkboxes can
display the current status of that client.

Then, when the "next" checkbox is clicked, the (current?) date is
entered into the appropriate column.


--
weavtennis
------------------------------------------------------------------------
weavtennis's Profile: http://www.excelforum.com/member.php...fo&userid=3634
View this thread: http://www.excelforum.com/showthread...hreadid=514351

Use of check boxes
 

Dear Bob,

That's classy. You've saved me the next dozen weekends or so, of
meticulously editing hundreds of rows...

With my hazy knowledge of Basic, I can kind of see what's going on, but:

How do you trip the cell back to an empty state?

Do I need an "Else...." line, or would should I stick another "If" condition
in there?

I realise that it's something to do with the "Not Intersect.... is Nothing"
but my mother always taught me that double-negatives were a "No-no." If
you'll pardon the pun.

Presumably I can say if the "Not Intersect" is NOT Nothing...?

Could I get it to work off the back of a double-click, so that it's not eay
accidentally to over-write, or am I taking the mickey?

Kind regards and thanks once again,

Lambs

"Bob Phillips" wrote:

Here is a way without checkboxes. All you do is select a flagging column (I
have defined, B,D,F,H), and the date pops up next door.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "B:B,D:D,F:F,H:H"

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Font.Name = "Marlett"
.Value = "a"
.Offset(0, 1).Value = Date
.Offset(0, 1).NumberFormat = "dd mmm"
End With
End If

End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Lambs" wrote in message
...
Hi,

I am devising a spreadsheet to log the progress on a list of clients,
through a set series of procedures. The idea is that each time a certain
stage has been passed, the corresponding date is entered in the relevant
column, at that client's row.

I have thought about using check boxes by each date column, so that when
the
box is checked, the corresponding date is automatically filled in.

I know that one can 'tie' a cell to a check box, and it will flip from
'True' to 'False' accordingly. I should like that box to show today's date
when ticked, and revert to blank when unticked.

If it works, there will be several hundred of these little critters, and
copying them can be a problem - they all want to 'tie' themselves to the
same
cell, when I use the normal copy and paste function.

If anyone can advise on code to get the tied cell to show correct info.
rather than just True/False, I'd be much obliged.

If anyone can advise on how to get copied check boxes automatically to
refer
to the next cell down, or in line, or some kind of automation/macro that I
could set up, then I'd be even more grateful.

If anyone thinks that the check-box idea is fatally flawed and/or can
propose a more elegant solution, then please let me have your thoughts.

Thank you,

Lambs

Use of check boxes
 

I personally don't like the double-click event (I often fail to
double-click, and it gets two single clicks, and mayhem), but if you want
it, here it is. I have also added a clear-down option which happens if you
double-click a cell currently flagged.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Const WS_RANGE As String = "B:B,D:D,F:F,H:H"

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value < "a" Then
.Font.Name = "Marlett"
.Value = "a"
.Offset(0, 1).Value = Date
.Offset(0, 1).NumberFormat = "dd mmm"
Else
.Value = ""
.Offset(0, 1).Value = ""
End If
End With
End If

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Lambs" wrote in message
...
Dear Bob,

That's classy. You've saved me the next dozen weekends or so, of
meticulously editing hundreds of rows...

With my hazy knowledge of Basic, I can kind of see what's going on, but:

How do you trip the cell back to an empty state?

Do I need an "Else...." line, or would should I stick another "If"
condition
in there?

I realise that it's something to do with the "Not Intersect.... is
Nothing"
but my mother always taught me that double-negatives were a "No-no." If
you'll pardon the pun.

Presumably I can say if the "Not Intersect" is NOT Nothing...?

Could I get it to work off the back of a double-click, so that it's not
eay
accidentally to over-write, or am I taking the mickey?

Kind regards and thanks once again,

Lambs

"Bob Phillips" wrote:

Here is a way without checkboxes. All you do is select a flagging column
(I
have defined, B,D,F,H), and the date pops up next door.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "B:B,D:D,F:F,H:H"

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Font.Name = "Marlett"
.Value = "a"
.Offset(0, 1).Value = Date
.Offset(0, 1).NumberFormat = "dd mmm"
End With
End If

End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Lambs" wrote in message
...
Hi,

I am devising a spreadsheet to log the progress on a list of clients,
through a set series of procedures. The idea is that each time a
certain
stage has been passed, the corresponding date is entered in the
relevant
column, at that client's row.

I have thought about using check boxes by each date column, so that
when
the
box is checked, the corresponding date is automatically filled in.

I know that one can 'tie' a cell to a check box, and it will flip from
'True' to 'False' accordingly. I should like that box to show today's
date
when ticked, and revert to blank when unticked.

If it works, there will be several hundred of these little critters,
and
copying them can be a problem - they all want to 'tie' themselves to
the
same
cell, when I use the normal copy and paste function.

If anyone can advise on code to get the tied cell to show correct
info.
rather than just True/False, I'd be much obliged.

If anyone can advise on how to get copied check boxes automatically to
refer
to the next cell down, or in line, or some kind of automation/macro
that I
could set up, then I'd be even more grateful.

If anyone thinks that the check-box idea is fatally flawed and/or can
propose a more elegant solution, then please let me have your
thoughts.

Thank you,

Lambs

Use of check boxes
 

Cap'n Bob,

You are a star.

Even better, you are well on your way to making me look good at Excel - in
my office, I am the 'proverbial' one-eyed man in the land of the blind, if
you catch my drift.

As to not liking double-clicks, I am guessing that you might want to cut
back on the caffeine...?

Best regards,

Lambs



"Bob Phillips" wrote:

I personally don't like the double-click event (I often fail to
double-click, and it gets two single clicks, and mayhem), but if you want
it, here it is. I have also added a clear-down option which happens if you
double-click a cell currently flagged.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Const WS_RANGE As String = "B:B,D:D,F:F,H:H"

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value < "a" Then
.Font.Name = "Marlett"
.Value = "a"
.Offset(0, 1).Value = Date
.Offset(0, 1).NumberFormat = "dd mmm"
Else
.Value = ""
.Offset(0, 1).Value = ""
End If
End With
End If

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Lambs" wrote in message
...
Dear Bob,

That's classy. You've saved me the next dozen weekends or so, of
meticulously editing hundreds of rows...

With my hazy knowledge of Basic, I can kind of see what's going on, but:

How do you trip the cell back to an empty state?

Do I need an "Else...." line, or would should I stick another "If"
condition
in there?

I realise that it's something to do with the "Not Intersect.... is
Nothing"
but my mother always taught me that double-negatives were a "No-no." If
you'll pardon the pun.

Presumably I can say if the "Not Intersect" is NOT Nothing...?

Could I get it to work off the back of a double-click, so that it's not
eay
accidentally to over-write, or am I taking the mickey?

Kind regards and thanks once again,

Lambs

"Bob Phillips" wrote:

Here is a way without checkboxes. All you do is select a flagging column
(I
have defined, B,D,F,H), and the date pops up next door.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "B:B,D:D,F:F,H:H"

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Font.Name = "Marlett"
.Value = "a"
.Offset(0, 1).Value = Date
.Offset(0, 1).NumberFormat = "dd mmm"
End With
End If

End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Lambs" wrote in message
...
Hi,

I am devising a spreadsheet to log the progress on a list of clients,
through a set series of procedures. The idea is that each time a
certain
stage has been passed, the corresponding date is entered in the
relevant
column, at that client's row.

I have thought about using check boxes by each date column, so that
when
the
box is checked, the corresponding date is automatically filled in.

I know that one can 'tie' a cell to a check box, and it will flip from
'True' to 'False' accordingly. I should like that box to show today's
date
when ticked, and revert to blank when unticked.

If it works, there will be several hundred of these little critters,
and
copying them can be a problem - they all want to 'tie' themselves to
the
same
cell, when I use the normal copy and paste function.

If anyone can advise on code to get the tied cell to show correct
info.
rather than just True/False, I'd be much obliged.

If anyone can advise on how to get copied check boxes automatically to
refer
to the next cell down, or in line, or some kind of automation/macro
that I
could set up, then I'd be even more grateful.

If anyone thinks that the check-box idea is fatally flawed and/or can
propose a more elegant solution, then please let me have your
thoughts.

Thank you,

Lambs

Use of check boxes
 

"Lambs" wrote in message
...

As to not liking double-clicks, I am guessing that you might want to cut
back on the caffeine...?

LOL! It's the only thing that keeps me going until I hit the malt.