PDA

View Full Version : Sleeper: schedule with colorfill~ i need help~



Alvinkiang
07-24-2006, 02:02 AM
i wanted to schedule the date entered in the form accordingly with colorfill on the worksheets. i am done with the date split and i am also done with the opening of the right worksheet for month. but i am having problems with the day. how can i get the days to be on the right cells? i dun know how to let the system detect the days from the worksheet and color the correct cells with color. can anyone pls help mi?? thanks

i have attached the worksheet of my schedule to let ur see how it looks like.

Below are my coding for date split and opening of the right month and place the EquidID on the cells. how to go about tracking the day and fill the cells with color??


Dim StoredCmd() As String
Dim myValue1 As Variant
Dim myValue2 As Variant
Dim myValue3 As Variant
Dim temp5 As String
temp5 = txtSchedule.Value
StoredCmd = Split(temp5, "/")
myValue1 = (StoredCmd(0)) ' mm
myValue2 = (StoredCmd(1)) ' dd
myValue3 = (StoredCmd(2)) ' yyyy
With Workbooks("PM_Schedule.xls")
.Activate
If (myValue1 = 1) Then
Worksheets("Jan").Activate
ElseIf (myValue1 = 2) Then
Worksheets("Feb").Activate
ElseIf (myValue1 = 3) Then
Worksheets("Mar").Activate
ElseIf (myValue1 = 4) Then
Worksheets("Apr").Activate
ElseIf (myValue1 = 5) Then
Worksheets("May").Activate
ElseIf (myValue1 = 6) Then
Worksheets("Jun").Activate
ElseIf (myValue1 = 7) Then
Worksheets("July").Activate
ElseIf (myValue1 = 8) Then
Worksheets("Aug").Activate
ElseIf (myValue1 = 9) Then
Worksheets("Sept").Activate
ElseIf (myValue1 = 10) Then
Worksheets("Oct").Activate
ElseIf (myValue1 = 11) Then
Worksheets("Nov").Activate
ElseIf (myValue1 = 12) Then
Worksheets("Dec").Activate
Else
MsgBox ("not in the 12 month")
End If
find_empty_cell ("A5")
Selection.Cells.Offset(1, 0) = txtEquidID

Bob Phillips
07-24-2006, 03:50 AM
Don't understand. What are you trying to do?

Alvinkiang
07-25-2006, 06:08 PM
basically, you notice that the coding above actually wil split the date into mm/dd/yyyy .. if the date is 1/12/2006, then the mm will be 11 therefore it will open the Nov sheet . i am done with it and then now, i go on to the the dd(days).. but i am havin problem to find the days in the nov sheet. i wan it to find day 12 on the sheet and place the fill of the nov sheet day 12 cells with a color. but i don't know how to..

hope u understand what i am trying to do..

see the attachment for sample.

malik641
07-25-2006, 06:51 PM
If I were doing this, I would change your if statement to a Case Select method. Or I would create a string array with all the months in it (probably use option base 1 to make things simpler too). Something like this small example:



Dim arrMonths As Variant
Dim Sh As Worksheet
arrMonths = Array("Jan", "Feb", "Mar", "Apr", "May", _
"Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
If IsDate(Format(txtSchedule.Text, "mm/dd/yyyy")) Then
'Do code


And btw, you don't need to select anything to do what you are doing. If you leave out the Select in your code and just use the reference, you'll save time by having faster code. As an example (which also changes a cell's color to yellow):



'Instead of ActiveCell.Select, use
Activecell.Interior.Colorindex = 6


Hope this helps out to get you moving. Also, try to be a bit more descriptive. Like "I'm using a user form where you enter a date and based on that date it will find a specific cell and color it yellow. There is one sheet for each month."

:thumb

Alvinkiang
07-25-2006, 08:57 PM
Oooo.. thxxs=D