Timetable.frm

[Robert Couchman]

i have a form "timetable"

when this is opened from form "pns", it will fill a list
in combobox1 of dates used in "Sheet1" (these dates have
been inputted in a way that only 1 of each value appears
and they are in date order) the dates are found in
column "AZ" in "Sheet1"

when a date is selected in combobox1 then times from
column "BA" are looked at, the times that are related to
the dates are then put into the relevant textboxes along
the left hand side of the form (these textboxes are in
order from textbox1 to textbox10)

for each textbox along the left there are 4 comboboxes
going horizontaly across the form they are labeled with
reference to the textbox e.g. the line where textbox1
appears they are labeled "combo11" "combo21" "combo31"
and "combo41", where the line where textbox5 they are
named "combo15" "combo25" "combo35" and "combo45"

each column of comboboxes represents a panel e.g. panel1
would contain "combo11" "combo12" "combo13" ... "combo19"
and "combo10" (ROW10 OF TEXTBOXES IS REPRESENTED WITH 0 IN
THE COMBOBOXES *KEEPS IT TO TWO DIGITS*)

the number of the panel for each person invited to an
interview will be located on column "BB"

what i need is :-

when a user selects a date for "combobox1", after the
times have been put into the correct textboxes

each combobox from "combo10" through to "combo49" will
find if a member already exists for there appointment time
slot e.g. if date "15/03/2004" is selected
from "combobox1" then the times for textboxes will be
inserted (in this case lets refer to the 11O'clock time
slot) where "textbox4" = "11:00" the comboboxes will be
filled accordinglly, therefore if a persons record
indicates that they have already got a time booked
for "15/03/2004" @ "11:00" and they are in panel "2"
**presuming textbox4 contained 11:00**
then "combo24" will contain that persons name and surname
(name data will be found in column "B" and the surname
data in column "C")

***************************************************

if a blank combobox is selected a list needs to be created
for the combobox, this needs to contain members who have
not got an appointment booked "no date in column AZ" but
have also passed a test ("PASS" will need to be in
column "AL")

once a member is selected off of this list (ID may be used
from column "A" to identify the person)
the date from "combobox1" needs to be inserted into
column "AZ" on that persons row, and also the time
from "textbox4" needs to be inserted in column "BA", while
the panel number (can only be specified by the combobox)
**lets use panel 4 as an example**
the panel number will be inserted into column "BB" on the
members row

*******************************************************

whilst if a blank is selected, or a different member is
selected, then the details of the member will be deleted
from columns "AZ", "BA", and "BB" for that member

*******************************************************

if anyone knows how to help me with this please can they
say!! or even get in touch via e-mail address after my name

Thank you,

Robert Couchman
([email protected])

 

[Dick Kusleika]

Robert

i have a form "timetable"

when this is opened from form "pns", it will fill a list
in combobox1 of dates used in "Sheet1" (these dates have
been inputted in a way that only 1 of each value appears
and they are in date order) the dates are found in
column "AZ" in "Sheet1"

when a date is selected in combobox1 then times from
column "BA" are looked at, the times that are related to
the dates are then put into the relevant textboxes along
the left hand side of the form (these textboxes are in
order from textbox1 to textbox10)

If you only have one date, how can you have multiple times for that date?

for each textbox along the left there are 4 comboboxes
going horizontaly across the form they are labeled with
reference to the textbox e.g. the line where textbox1
appears they are labeled "combo11" "combo21" "combo31"
and "combo41", where the line where textbox5 they are
named "combo15" "combo25" "combo35" and "combo45"

each column of comboboxes represents a panel e.g. panel1
would contain "combo11" "combo12" "combo13" ... "combo19"
and "combo10" (ROW10 OF TEXTBOXES IS REPRESENTED WITH 0 IN
THE COMBOBOXES *KEEPS IT TO TWO DIGITS*)

the number of the panel for each person invited to an
interview will be located on column "BB"

Are these dates, times, and panels related to the names in previous columns?

what i need is :-

when a user selects a date for "combobox1", after the
times have been put into the correct textboxes

each combobox from "combo10" through to "combo49" will
find if a member already exists for there appointment time
slot e.g. if date "15/03/2004" is selected
from "combobox1" then the times for textboxes will be
inserted (in this case lets refer to the 11O'clock time
slot) where "textbox4" = "11:00" the comboboxes will be
filled accordinglly, therefore if a persons record
indicates that they have already got a time booked
for "15/03/2004" @ "11:00" and they are in panel "2"
**presuming textbox4 contained 11:00**
then "combo24" will contain that persons name and surname
(name data will be found in column "B" and the surname
data in column "C")

You'll use the Combobox_Change event for the combobox that holds the date.
Loop through all the cells and find the combination of cells that meets your
date time criteria, then pull the name. I'm not sure how you would do this
because I don't understand how AZ and BA relate, if at all, to B and C.

If AZ and BA are the date and time for the name at B and C, then it might
look like this

Dim cell As Range
Dim i as Long

For Each cell in Range("Az1:AZ100").Cells
If cell.Value = CDate(Me.ComboBox1.Value) Then
For i = 1 to 10
If cell.Offset(0,1).Value = CDate(Me.Controls("TextBox" &
i).Text) Then
If i = 10 Then
Me.Controls("TextBox" & "0" &
cell.Offset(0,2).Value).Text = _
cell.Offset(0,-52).Value & " " &
cell.Offset(0,-51).Value
Else
Me.Controls("TextBox" & i & cell.Offset(0,2).Value).Text
= _
cell.Offset(0,-52).Value & " " &
cell.Offset(0,-51).Value
End If
Exit For
End If
Next i
End If
Next cell

***************************************************

if a blank combobox is selected a list needs to be created
for the combobox, this needs to contain members who have
not got an appointment booked "no date in column AZ" but
have also passed a test ("PASS" will need to be in
column "AL")

Use the Enter event for the combobox. Loop through all the cells and add
those that meet the criteria to the combobox

Dim cell As Range

For Each cell in Range("A1:A100").Cells
If cell.Offset(0,37).Value = "PASS" And IsEmpty(cell.Offset(0,51)) Then
Me.ComboBox(x).AddItem cell.Value & " " & cell.Offset(0,1).Value
End If
Next cell

once a member is selected off of this list (ID may be used
from column "A" to identify the person)
the date from "combobox1" needs to be inserted into
column "AZ" on that persons row, and also the time
from "textbox4" needs to be inserted in column "BA", while
the panel number (can only be specified by the combobox)
**lets use panel 4 as an example**
the panel number will be inserted into column "BB" on the
members row

Use combobox change event. Loop through cells looking for names and write
the values when found.

Dim cell As Range

For Each cell In Range("A1:A100").Cells
If Me.ComboBox(x).Value = cell.Value & " " & cell.Offset(0,1).Value Then
cell.Offset(0,51).Value = Me.ComboBox1.Value
cell.Offset(0,52).Value = Me.Controls("TextBox" & [Combobox last
number]).Text
cell.Offset(0,53).Value = [CB penultimate number]
End If
Next cell

*******************************************************

whilst if a blank is selected, or a different member is
selected, then the details of the member will be deleted
from columns "AZ", "BA", and "BB" for that member

*******************************************************

Loop through the cells as in the combobox1 example and clear the contents
for the date and time if that date and time are found (and the panel), then
re-loop through the cells and find the name (if not blank) and write the
date and time in that row.

It will be well worth your while to read this

http://j-walk.com/ss/excel/tips/tip44.htm

since you will have 40 subroutines that are virtually identical.