Use of check boxes

[Lambs]

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

[weavtennis]

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.

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Lambs]

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

[Lambs]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

[Lambs]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>

[Bob Phillips]

"Lambs" <[email protected]> wrote in message
news:[email protected]...

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