Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Trying to select a specific range based on the time value of user form input

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)

  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,365
Default Trying to select a specific range based on the time value of user

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)


  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Trying to select a specific range based on the time value of user

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)



  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Trying to select a specific range based on the time value of user

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)



  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,365
Default Trying to select a specific range based on the time value of u

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)






  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Trying to select a specific range based on the time value of u

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)





  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 7
Default Trying to select a specific range based on the time value of u

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)





  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,365
Default Trying to select a specific range based on the time value of u

Well, might be easier - I won't have to recreate your entire workbook with
buttons and all, if you could send a copy of the .xls file to

I could then jump right in on it. But I'll try tossing the code into a
workbook this evening (it's early morning now) if I haven't gotten email from
you and see what I can do with it. It's probably some simple, almost obvious
thing that I'm not thinking of right at this moment. But having the workbook
would help make sure I don't make another subtle mistake such as naming the
controls improperly.

JLatham

"Jitranijam" wrote:

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

  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 3,365
Default Trying to select a specific range based on the time value of u

The workbook really would help - it's a pretty busy form you have there,
taking some time just to build it to even get ready to test.

But one thing I noticed during that process is your Case statements - they
are specific in looking for a match to a specific date. In order to work on
any other day then you're going to have to modify the way the test values are
obtained. Probably going to involve setting up a value to get the current
date and adding the times to it, or stripping off the date and examining the
hour:minute portion only.

JLatham

"Jitranijam" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
checking that cells have a value before the workbook will close kcdonaldson Excel Worksheet Functions 8 December 5th 05 05:57 PM
Print scrollable user form. cparsons Excel Discussion (Misc queries) 2 August 4th 05 04:45 PM
Finding a record based on user input Soundman Excel Discussion (Misc queries) 5 June 21st 05 03:06 AM
Date/time range based calculations jim314 Excel Discussion (Misc queries) 1 April 28th 05 07:21 PM
Input Form vba help mdalzell Excel Discussion (Misc queries) 0 April 8th 05 03:57 PM


All times are GMT +1. The time now is 10:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"