prompt for weekday and distribute the dates in columns

[deepika :excel help]

Is it possible that after clicking on a macro button taht does lot of other
work in a sheet - a prompt box or wateva shud ask me to pick from list the
weekdays . say the box should contain a list with ranges like
31-Dec-207 to 4-Jan-2008
7-Jan-2008 to 11-Jan-2008
...
and this range of working days should be till 29-Dec-2008 to 2-Jan-2009

So when i pick one of teh above range , the five days in a week shud
distribute in 5 columns say D3,E3,F3,G3,H3 and the ranges include only mon to
friday dates and do not include sat and sunday. so whteversystem date im in
now , when i picjk from the list i shud get the dates corresponding to teh
range only in those 5 columns... pls help

 

[Joel]

yes it can be done. here is a start which displays the range of dates for
the prevvious 6 weeks. Without seeing the rest of your copde I do not know
how to connect the date picked from the list to the actual spreadhseet and
the previous code that working.

Sub displaydates()
Const NumofWeeks As Integer = 6

MyDate = Date
OffsetMonday = Weekday(MyDate, vbMonday) - 1
StartDate = (MyDate - OffsetMonday) - (7 * NumofWeeks)
displaystring = ""
For wks = 0 To NumofWeeks
FormatDate = Format(StartDate + (7 * wks), "DD-MMM-YYYY")
displaystring = displaystring & FormatDate & " to "
FormatDate = Format(StartDate + (7 * wks) + 4, "DD-MMM-YYYY")
displaystring = displaystring & FormatDate & Chr(10)
Next wks
MsgBox (displaystring)

End Sub

 

[deepika :excel help]

'Im am actaully automating a sheet called FTP from some inputs from sheet
called WAS
'The rows in WAS are dynamic. And begins from row 6. In the code j=6.. the
cell name is at j=5
'Thr FTP sheet needs to be automatically filled on clicking a macro and the
row headers are placed at 3.. teh row entries start at i=4)
'The input details from WAS are:
Column D: Project Name
E: task
F: assigned to
H: Planned start date
I: planned End date
J: actual Start
K: actual end
L: Planned Effort
O: Status

The output fileds in FTP sheet are
Column B: Assigned To
C: work item (this is from WAS' Project name_Task i.e, concat of Columns
D&E with an _)
D,E,F,G,H : these contain monday to friday of a week i.,5 working days of a
week
What i have to do is when i click on the macro i should get a prompt box
with a list asking to select the date range for a working week .
say the box should contain a list with ranges like
31-Dec-207 to 4-Jan-2008
7-Jan-2008 to 11-Jan-2008
...
and this range of working days should be till 29-Dec-2008 to 2-Jan-2009

So when i pick one of teh above range , the five days in a week shud
distribute in 5 columns say D3,E3,F3,G3,H3 and the ranges include only mon to
friday dates and do not include sat and sunday. so whtever current date im in
now , i shud be able to pick any date range and get the dates corresponding
to the
range only in those 5 columns...
This is the code that i have witten below Sub automateFTP()

After this i have to place the following logic

The distribution of the day wise effort will be based on a logic which would
check for 8 hours of work on a
particular day (say it checks for 8 for an individual(asigned to)). If 8
hours has already been consumed in
a particular day then no other task will be planned for the day then it will
try to distribute 8 hours for the remaining day
, If less than 8 hours has been planned then the new task will be
distributed as 8-x
where x is the hours for already planned task.

I had jsut written the code for the folwing . Can u pls help me as
ThisWorkbook task is quite urgent to finish and im unaware of how to do..
'bringing in ProjectName_taskname and assigned to name from WAS into FTP

Sheets("WAS").Activate
J = 6 'WAS entries start from 6th row . so j=6
I = 4 'FTP entries start from 4th row. so i=4

While Cells(J, 4).Value <> ""

If (Cells(J, 15).Value <> "Completed") Then

Sheets("FTP").Cells(I, 3) = Cells(J, 4).Value & "_" & Cells(J, 5).Value
Sheets("FTP").Cells(I, 2) = Cells(J, 6).Value


End If

I = I + 1
J = J + 1

Wend
Sheets("FTP").Activate

End Sub

 

[deepika :excel help]

'Im am actaully automating a sheet called FTP from some inputs from sheet
called WAS
'The rows in WAS are dynamic. And begins from row 6. In the code j=6.. the
cell name is at j=5
'Thr FTP sheet needs to be automatically filled on clicking a macro and the
row headers are placed at 3.. teh row entries start at i=4)
'The input details from WAS are:
Column D: Project Name
E: task
F: assigned to
H: Planned start date
I: planned End date
J: actual Start
K: actual end
L: Planned Effort
O: Status

The output fileds in FTP sheet are
Column B: Assigned To
C: work item (this is from WAS' Project name_Task i.e, concat of Columns
D&E with an _)
D,E,F,G,H : these contain monday to friday of a week i.,5 working days of a
week
What i have to do is when i click on the macro i should get a prompt box
with a list asking to select the date range for a working week .
say the box should contain a list with ranges like
31-Dec-207 to 4-Jan-2008
7-Jan-2008 to 11-Jan-2008
...
and this range of working days should be till 29-Dec-2008 to 2-Jan-2009

So when i pick one of teh above range , the five days in a week shud
distribute in 5 columns say D3,E3,F3,G3,H3 and the ranges include only mon to
friday dates and do not include sat and sunday. so whtever current date im in
now , i shud be able to pick any date range and get the dates corresponding
to the
range only in those 5 columns...

After this i have to place the following logic

one important thing here. All entries into the FTP sheet should be

 

[Joel]

This is what i havve so far. You need to create a list box so the dates can
be filled into the box. You need to get the box filled. Then select a date
from the box.

Sub FillListbox()
Const NumofWeeks As Integer = 6

Jan1 = DateValue("Jan 1 " & Year(Date))
NextYear = DateValue("Jan 1 " & (Year(Date) + 1))
OffsetMonday = Weekday(Jan1, vbMonday) - 1
MyDate = Jan1 - OffsetMonday

With Sheets("FTP")
.ListBox1.Clear
Do While MyDate < NextYear
displaystring = ""
FormatDate = Format(MyDate, "DD-MMM-YYYY")
displaystring = displaystring & FormatDate & " to "
FormatDate = Format(MyDate + 4, "DD-MMM-YYYY")
displaystring = displaystring & FormatDate & Chr(10)
.ListBox1.AddItem displaystring
MyDate = MyDate + 7
Loop
End With
End Sub
Sub getlistbox()

With Sheets("FTP")
SelectDate = .ListBox1.Text
firstDate = DateValue(Trim( _
Left(SelectDate, InStr(SelectDate, " "))))
For Dayoffset = 0 To 4
.Range("D3").Offset(0, Dayoffset) = _
Format(firstDate + Dayoffset, "DD-MMM-YYYY")

Next Dayoffset


WASRowCount = 6 'WAS entries start from 6th row . so j=6
FTPRowCount = 4 'FTP entries start from 4th row. so i=4

Do While .Cells(WASRowCount, "D").Value <> ""

If (.Cells(WASRowCount, "O").Value <> "Completed") Then

Sheets("FTP").Cells(FTPRowCount, "C") = _
.Cells(WASRowCount, 4).Value & "_" & _
.Cells(WASRowCount, 5).Value
Sheets("FTP").Cells(FTPRowCount, "B") = .Cells(WASRowCount,
"F").Value
End If

FTPRowCount = FTPRowCount + 1
WASRowCount = WASRowCount + 1
Loop
End With

End Sub

 

[deepika :excel help]

hi joel,
Thank you fo rthe code but this re4turns an error at
SelectDate = .ListBox1.Text
wat cud bethe reason

 

[Joel]

As I said in my posting, You need to add a list box to the worksheet

1) Open a new toolbar from worksheet menu: View - Toolbars - Control Toolbox
2) Select Listbox on toolbar and add to worksheet.
3) re-Run the code.