| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Trying to select a specific range based on the time value of u
|
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
| Print scrollable user form. | Excel Discussion (Misc queries) | |||
| Finding a record based on user input | Excel Discussion (Misc queries) | |||
| Date/time range based calculations | Excel Discussion (Misc queries) | |||
| Input Form vba help | Excel Discussion (Misc queries) | |||
Hybrid Mode
