CJM,

If you're talking about a List created in the Data menu, here' s some sample
code that changes the name of the first Data List on the sheet and then
tells you the DL's address:

Sub test()
Dim ws As Worksheet

Set ws = ActiveSheet
ws.ListObjects(1).Name = "CJMs List"
MsgBox ws.ListObjects("CJMs List").Range.Address
End Sub

On another note, it;s good to avoid "Select" and "Activate" in your code.
The macro recorder, although a great tool for learning, uses them too much
and they slow down your code. For example, in your other recent post, you
could change:

Sheets("Summary Totals").Select
Sheets("Summary Totals").Range("A9:O15").Select
Selection.Copy

to:

Sheets("Summary Totals").Range("A9:O15").Copy

Nicer, isn't it?

hth,

Doug


"CJM" wrote in message
...
Hello -- one more question. I am trying to sort a list first by tech no
and
then by name and am using the following code:

Range("A9").Select
Range("A8:O22").Sort Key1:=Range("D9"), Order1:=xlAscending,
Key2:=Range( _
"B9"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
ActiveWindow.SmallScroll Down:=6

Rather than specify the range, I'd like excel to determine the range of
the
table. What do I put instead of the A8:O22? BTW, I got the code by
recording a macro to sort and then copying the code. I'm sure everybody
does
that!
--
CJM


"Doug Glancy" wrote:

CJM,

You're very welcome. It was a learning experience for me too!

Doug

"CJM" wrote in message
...
It worked like a charm! I can't thank you enough. I'm slowly by
surely
starting to get the hang of this. I figured out the debugger thing.
Thanks
to your code, I have been able to figure out how to do certain things.
I
see
how you coded something, and I apply it to another component. You've
been
a
tremendous help.
--
CJM


"Doug Glancy" wrote:

CJM,

For your tabbing question, try something like this. I assume that
your
data
entry area spans columns A:F:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F:F")) Is Nothing Then
Range("A" & Target.Row + 1).Select
End If
End Sub

Also, take a look at setting up a data list (Data List, available in
2003,
not sure about XP and earlier) and see if that meets your purposes.
It's
a
basic data entry environment that allows users to add new rows among
other
things and tabs as you described.

hth,

Doug

"CJM" wrote in message
...
Thanks, Doug. Once again you've been a big help. I couldn't get
the
code
to
run properly, but I'm sure it wasn't your code. Since I'm not that
up
to
speed (yet!), I have problems navigating the debugger. Sometimes I
can't
seem to reset the environment to start fresh, and I wind up exitng
out
of
Excel and back in. This happened even when I got the code to work.

Since I'm under a tight deadline, I've resdesigned the way I'm doing
this
thing. I'm setting up a form (not an actual user form, but a group
of
cells)
that the user can enter their numbers into. I'm then converting
those
numbers to the format I need in another cell. The user interface
looks
something like this:

Day Date Start AM/PM End AM/PM Code
SAT 9/12 830 AM 530 PM 8
SUN 9/13 830 AM 530 PM 8
MON 9/14 830 AM 530 PM 8
TUE 9/15 830 AM 530 PM 8

This will work and everyone seems to like this idea. The problem
I'm
having
now is I'd like to be able to navigate the user input -- i.e., when
the
user
gets to the end of the first row, I'd like the cell selection to be
the
first
entry in the next row so that the user doesn't have to cursor over
or
click
in the cell. This seems easy to me, and I assume it will require a
macro.
Have you ever done this?

I thought of setting up a user form, but I think this might be too
complicated. What do you think? People like you keep me
going...I'm
really
tired and I didn't get to have lunch!




--
CJM


"Doug Glancy" wrote:

CJM,

The issue that I see with only triggering if the cell is blank is
that
if
the user realizes they meant 730 instead of 830 the code won't run
when
they
make the corrcection. Here's what I've come up with. It's
convoluted
and
my lunch is almost over, so see if it works for you and feel free
to
ask
me
what I meant either way.

This now assumes that start time is in column B and end time is in
column
C.
I added code to not do any processing on cells outside columns B
and C
(in
the event that they change multiple cells at once, some inside
range
B:C
and
some outside:

Private Sub Worksheet_Change(ByVal target As Range)
Dim target_cell As Range

Application.EnableEvents = False
If Not Intersect(target, Range("B:C")) Is Nothing Then
For Each target_cell In Intersect(target, Range("B:C"))
If IsNumeric(target_cell) Then
If Not (IsDate(Date & " " & Format(target_cell,
"h:mm"))
And
Int(target_cell) = 0) Then
If (Len(target_cell) = 3 Or Len(target_cell) = 4)
And
_
Int(target_cell) = target_cell And _
Left$(target_cell,
WorksheetFunction.Max(Len(target_cell) - 2, 1)) <= 12 And _
Right$(target_cell, 2) <= 59 Then
target_cell = Left$(target_cell,
Len(target_cell) -
2) &
":" & Right$(target_cell, 2) & " AM"
If Not Intersect(target_cell, Range("C:C")) Is
Nothing
Then
target_cell = Replace(target_cell.Text,
"AM",
"PM")
End If
target_cell.NumberFormat = "h:mm AM/PM"
Else
target_cell.Clear
target_cell.Select
MsgBox "that's not a time"
End If
End If
Else
target_cell.Clear
target_cell.Select
MsgBox "that's not a time"
End If
Next target_cell
End If
Application.EnableEvents = True

End Sub

hth,

Doug

"CJM" wrote in message
...
Just thinking out loud, I think another way to solve the military
time
problem would be to only execute the worksheet_change if the cell
contains
no
value. See if you follow what I'm thinking and if it makes
sense:

Cell is blank
User enters 830.
Cell returns 8:30 AM
User really wanted 8:30 PM, so user manually overwrites the AM to
PM.
Cell does not return an error message but allows the overwrite
and
stores
the correct entry.

NOW, this sounds like a lot of steps, but 99% of the time, the
"start
time"
will always be AM and the "end time" pm, so all in all it may be
less
clicks
for the user.

Is what I described possible and how would you do it? I'm going
to
play
around and see if I can figure it out. Thanks.

--
CJM


"CJM" wrote:

It works beautifully. That really helped a lot. Thanks again.
I
used
to
be a really good programmer back in the day, but things have
changed
so
much
and I've been out of the loop for a little while. Of course, as
fate
would
have it, I'm thrown back into the frey when my client asks me to
"quickly"
develop a time entry spreadsheet for them.

The only issue I have is that one of the requirements is that
the
time
entry
not be in military time -- the "start time" entered by the user
must
default
to am and the "end time" must default to pm. This is a must as
apparently
the payroll clerks get confused on military time conversions.
Now
that
the
code is working in military time (thanks to you), I'm thinking
that
perhaps
the best way to handle the military time issue is to add at the
beginning
of
the code a prompt to the user with a radio button for am or pm.
The
radio
button would default to am for the "start time" column and to pm
for
the
"end
time" column. The calculations would then proceed based on the
selection.
The user would then enter "830" and then hit enter twice or
enter
once
with a
cursor move.

What do you think and is it possible to add the radio button as
I've
described?
--
CJM


"CJM" wrote:

Doug - Thanks so much for your thorough response. I really
appreciate
it.
I'm in the process of trying it now and will let you know.
The
reason
for
avoiding the colon is that the payroll clerks are quite error
prone,
and if
they have to enter a colon, that requires two entries, the
shift
and
then the
colon, hence more chance for error. Many of the clerks are
good
at
using the
number pad, but if they have to stop to enter a shift and
colon,
they
take
their hands off the number pad and hence further chance of
error.
I've
actually seen it happen.
--
CJM


"Doug Glancy" wrote:

CJM,

I've tried to think of all the things they could enter that
won't
work, but
I've probably missed some.

Paste the following into the worksheet module. It does the
conversion for
any value entered into Column B. The long If statement
checks
for,
in
order, 3 or 4 characters, whether it's an integer, if the
hour