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)