Thank you, the msgbox idea helped me see what the code was looking at.
So now it sets the appointment time by the WhatRowOffset, set up in the
Case Is statement, but the rest of the information is still set
according to the first row that is empty. I need the information to
stay together on one row. Is there something else I need to do to let
my program know to set focus on the offset row?

Here is my entire code, I do appreciate your suggestions as I am a
fairly new VBA programmer. I have been trying to teach myself by
books, unfortunately the books have not helped with this that much yet.
I thank you for any and all help you have and will give me.
************************************************** ************************************************** ******

Private Sub cboZipCode_Change()
If cboZipCode.Value = "" Then
txtZipCode.Enabled = False
End If
End Sub
----------------------------------------------------------------------
Private Sub chkOtherZip_Click()
If chkOtherZip = True Then
txtZipCode.Enabled = True
Else
txtZipCode.BackColor = &H80000004
End If
If chkOtherZip = False Then
txtZipCode.Enabled = False
Else
txtZipCode.BackColor = &H80000005
End If
End Sub
---------------------------------------------------------------------
Private Sub txtZipCode_Change()
If chkOtherZip = True Then
txtZipCode.Enabled = True
Else
txtZipCode.BackColor = &H80000004
End If

If chkOtherZip = False Then
txtZipCode.Enabled = False
Else
txtZipCode.BackColor = &H80000005
End If
End Sub
-------------------------------------------------------------------
Private Sub cmdClear_Click()
Call UserForm_Initialize
End Sub
------------------------------------------------------------------
Private Sub cmdCancel_Click()
Unload Me
End Sub
-------------------------------------------------------------------
Private Sub cmdOK_Click()
Range("A1").Select
ActiveWorkbook.Save
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
strClientName = txtClientName.Value
Select Case pckApptTime.Value
Case Is = "9/25/2006 7:00 AM"
WhatRowOffset = 0
Case Is = "9/25/2006 7:15 AM"
WhatRowOffset = 0
Case Is = "9/25/2006 8:00 AM"
WhatRowOffset = 9
Case Is = "9/25/2006 8:30:00 AM"
WhatRowOffset = 9
Case Is = "9/25/2006 9:00 AM"
WhatRowOffset = 19
Case Is = "9/25/2006 9:45 AM"
WhatRowOffset = 19
Case Is = "9/25/2006 10:00 AM"
WhatRowOffset = 29
Case Is = "9/25/2006 11:00 AM"
WhatRowOffset = 39
Case Is = "9/25/2006 1:00 PM"
WhatRowOffset = 0
Case Is = "9/25/2006 1:45 PM"
WhatRowOffset = 9
Case Is = "9/25/2006 3:15 PM"
WhatRowOffset = 49
Case Is = "9/25/2006 1:15 PM"
WhatRowOffset = 29
Case Is = "9/25/2006 2:30 PM"
WhatRowOffset = 39
Case Is = "9/25/2006 3:45 PM"
WhatRowOffset = 49
Case Else
MsgBox "Time: " & pckApptTime.Value & " Offset: " &
WhatRowOffset
End Select

ActiveCell.Offset(WhatRowOffset, 3) = pckApptTime.Value
ActiveCell.Value = StrConv(strClientName, vbProperCase)
ActiveCell.Offset(0, 1) = pckFDate.Value
ActiveCell.Offset(0, 2) = pckIntDate.Value
'ActiveCell.Offset(0, 3) = pckApptTime.Value

If chkYes1 = True Then
ActiveCell.Offset(0, 5).Value = "Yes"
End If
If chkNo1 = True Then
ActiveCell.Offset(0, 5).Value = "No"
End If
If ChkYes2 = True Then
ActiveCell.Offset(0, 6).Value = "Yes"
End If
If ChkNo2 = True Then
ActiveCell.Offset(0, 6).Value = "No"
End If
If chkPhone = True Then
ActiveCell.Offset(0, 7).Value = "X"
End If
If cboZipCode.Value = "" Then
ActiveCell.Offset(0, 8) = txtZipCode.Value
Else
ActiveCell.Offset(0, 8) = cboZipCode.Value
End If
If chkFS = True Then
ActiveCell.Offset(0, 9) = "X"
Else
ActiveCell.Offset(0, 9) = ""
End If
If chkMedical = True Then
ActiveCell.Offset(0, 10) = "X"
Else
ActiveCell.Offset(0, 10) = ""
End If
If ChkERDC = True Then
ActiveCell.Offset(0, 11) = "X"
Else
ActiveCell.Offset(0, 11) = ""
End If
If chkTANF = True Then
ActiveCell.Offset(0, 12) = "X"
Else
ActiveCell.Offset(0, 12) = ""
End If
If chkTADVS = True Then
ActiveCell.Offset(0, 13) = "X"
Else
ActiveCell.Offset(0, 13) = ""
End If
ActiveCell.Offset(0, 4) = cbowkrAssn.Value
ActiveCell.Offset(0, 15) = cboClerkInit.Value
ActiveCell.Offset(0, 16) = CboLanguage.Value

ActiveWorkbook.Save
End Sub
------------------------------------------------------------------------------
Private Sub cmdIntakeLog_Click()
Sheets("Monday Intake Log").Select
End Sub
------------------------------------------------------------------------------
Private Sub cmdMondayLog_Click()
Sheets("Monday Intake Log").Select
End Sub
--------------------------------------------------------------------------------
Private Sub cmdMondayRotation_Click()
Sheets("Monday").Select
Unload Me
End Sub
-----------------------------------------------------------------------------
Private Sub cmdTuesdayLog_Click()
Sheets("Tuesday Intake Log").Select
End Sub
----------------------------------------------------------------------------------
Private Sub cmdTuesdayRotation_Click()
Sheets("Tuesday").Select
Unload Me
End Sub
------------------------------------------------------------------------------------
Private Sub cmdWednesdayLog_Click()
Sheets("Wednesday Intake Log").Select
End Sub
----------------------------------------------------------------------------------------
Private Sub cmdWednesdayRotation_Click()
Sheets("Wednesday").Select
Unload Me
End Sub
--------------------------------------------------------------------------------------
Private Sub cmdThursdayLog_Click()
Sheets("Thursday Intake Log").Select
End Sub
-------------------------------------------------------------------------------------------
Private Sub cmdThursdayRotation_Click()
Sheets("Thursday").Select
Unload Me
End Sub
------------------------------------------------------------------------------------------
Private Sub cmdFridayLog_Click()
Sheets("Friday Intake Log").Select
End Sub
-----------------------------------------------------------------------------------------
Private Sub cmdFridayRotation_Click()
Sheets("Friday").Select
Unload Me
End Sub
----------------------------------------------------------------------------------------------
Private Sub UserForm_Initialize()

ActiveWorkbook.Save
Range("A1").Select

txtClientName.Value = ""
chkFS = False
chkMedical = False
ChkERDC = False
chkTANF = False
chkTADVS = False

With cbowkrAssn
.AddItem "AH"
.AddItem "GB"
.AddItem "JB"
.AddItem "KC"
.AddItem "KM"
.AddItem "KT"
.AddItem "LO"
.AddItem "LR"
.AddItem "LS"
.AddItem "MO"
.AddItem "SD"
.AddItem "KW"
.AddItem "TW"
.AddItem "IJ"
.AddItem "EM"
.AddItem "NV"
.AddItem "KL"
.AddItem "AS"
End With

With CboLanguage
.AddItem "EN"
.AddItem "SP"
.AddItem "RU"
.AddItem "OTR"
End With

CboLanguage = ""
cbowkrAssn = ""
With cboClerkInit
.AddItem "DS"
.AddItem "DH"
.AddItem "RS"
.AddItem "KE"
.AddItem "JF"
.AddItem "MJ"
.AddItem "DB"
.AddItem "RV"
.AddItem "KV"
.AddItem "AT"
.AddItem "GJ"
.AddItem "MGR"
.AddItem "Lead"
.AddItem "Other"
End With

cboClerkInit = ""

With cboZipCode
.AddItem "97004"
.AddItem "97009"
.AddItem "97011"
.AddItem "97012"
.AddItem "97013"
.AddItem "97015"
.AddItem "97017"
.AddItem "97022"
.AddItem "97023"
.AddItem "97025"
.AddItem "97027"
.AddItem "97028"
.AddItem "97034"
.AddItem "97035"
.AddItem "97036"
.AddItem "97038"
.AddItem "97042"
.AddItem "97045"
.AddItem "97049"
.AddItem "97055"
.AddItem "97062"
.AddItem "97067"
.AddItem "97068"
.AddItem "97069"
.AddItem "97070"
.AddItem "97071"
.AddItem "97073"
.AddItem "97086"
.AddItem "97222"
.AddItem "97236"
.AddItem "97266"
.AddItem "97267"
.AddItem "97268"
End With
cboZipCode = ""
txtZipCode = ""

chkYes1 = False
ChkYes2 = False
chkNo1 = False
ChkNo2 = False
chkPhone = False
pckFDate = Date
pckIntDate = Date

txtClientName.SetFocus
End Sub

************************************************** ******************************************
JLatham (removethis) wrote:
You have something in there right after the loop starts that says
strClientName = txtClientName.Value
I don't see that being used anywhere else in the code you've shown here.
Just an observation - perhaps it is used somewhere else?

Between the Case Else and End Select statements, put this line of code
Msgbox "Time: " & pckApptTime.Value & " Offset: " & WhatRowOffset

Be sure and take the ' off of front of 'Case Else so it becomes an active
statement.

This will accomplish two things -

#1 - by showing a message at all, it tells you that none of the times
matched in any of the Case Is = statements, and while doing so, it tells you
exactly what it was using to try to compare to the string literals as "7:00",
"2:30", etc.

If you are seeing things like 0.302083333333333 instead of 7:15 then your
source list for the pckApptTime is in time format and what you see on
screen/in the combo box is not the actual value - it is only a time formatted
display of the value. To compare to the text as Case Is = "7:15" then use
this instead:

Select Case pckApptTime.Text

If that cures the immediate problem, you may still have a problem when you
set the value on the worksheet = pckApptTime.Value ... if that cell is not
formatted as time, you'll just see the odd looking number there also, and if
that's the case, the cure is to format that cell as time, or change the code
to this:
ActiveCell.Offset(WhatRowOffset, 3) = pckApptTime.Text

Hope this helps some more.




"Jitranijam" wrote:

I followed your idea, and the row offset for the Select Case statement.
For some reason, it is not offseting the number of rows indicated with
the WhatRowOffset variable. Can you see what might be missing?

Private Sub cmdOK_Click()
Range("A1").Select
ActiveWorkbook.Save
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
strClientName = txtClientName.Value
Select Case pckApptTime.Value
Case Is = "7:00"
WhatRowOffset = 0
Case Is = "7:15"
WhatRowOffset = 0
Case Is = "8:00"
WhatRowOffset = 9
Case Is = "8:30"
WhatRowOffset = 9
Case Is = "9:00"
WhatRowOffset = 19
Case Is = "9:45"
WhatRowOffset = 19
Case Is = "10:00"
WhatRowOffset = 29
Case Is = "11:00"
WhatRowOffset = 39
Case Is = "1:00"
WhatRowOffset = 0
Case Is = "1:45"
WhatRowOffset = 9
Case Is = "3:15"
WhatRowOffset = 49
Case Is = "1:15"
WhatRowOffset = 29
Case Is = "2:30"
WhatRowOffset = 39
Case Is = "3:45"
WhatRowOffset = 49
'Case Else
'handle other time errors here
End Select

ActiveCell.Offset(WhatRowOffset, 3) = pckApptTime.Value


JLatham (removethis) wrote:
If I understand this correctly, you wish to change the base range for the
offsets based on the time entered/selected on the form.
As written, you've chosen Range("A1") early on, and that makes it the active
cell.
There are a couple of ways to do this, but we will leave A1 as the active
cell.

Try this code, you'll need an added variable, lets call it WhatRowOffset
Select Case pckApptTime.Value
Case is = 7:15
WhatRowOffset = 0
Case is = 8:30
WhatRowOffset = 9
Case Is = 9:45
WhatRowOffset = 19
Case is = 1:15
WhatRowOffset = 29
Case is = 2:30
WhatRowOffset = 39
Case is = 3:45
WhatRowOffset = 49
Case Else
'handle other time errors here
End Select
ActiveCell.Offset(WhatRowOffset, 1) = pckFDate.Value
ActiveCell.Offset(WhatRowOffset, 2) = pckIntDate.Value
ActiveCell.Offset(WhatRowOffset, 3) = pckApptTime.Value

Adjust as required (not sure you want to use WhatRowOffset for the 1st 2,
also make sure type matches in the Case Is = statements, if you pick up text
from the input form, then enclose in "" as Case Is = "7:15" to make sure you
get what you expect.



"Jitranijam" wrote:

Here is the cmdOK_Click event of my user form. What I need it to do is
based on the time input in the pckApptTime on my form to have it offset
to a different range of cells (ie: if time is 7:15, then the active
cell offset is range A1, looping for a blank cell. If the time is 8:30
am then the range begins at a10, 9:45 range a20, 1:15 range a30, 2:30
range a40, and 3:45 range a50)

Where in the Sub do I put this information? and what do I put to have
the OK_Click do this automatically?
Private Sub cmdOK_Click()
Range("A1").Select
ActiveWorkbook.Save (this is to update the shared workbook)
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
strClientName = txtClientName.Value
ActiveCell.Value = StrConv(strClientName, vbProperCase)
(THIS PUTS THE INFORMATION FROM THIS FORM FIELD IN PROPER
CASE
IN THE ACTIVE WORKSHEET)
ActiveCell.Offset(0, 1) = pckFDate.Value
ActiveCell.Offset(0, 2) = pckIntDate.Value
ActiveCell.Offset(0, 3) = pckApptTime.Value...
(THIS IS THE FIELD THAT I NEED TO OFSET AS LISTED ABOVE)