looking for form approach suggestion

  • Thread starter Thread starter mark kubicki
  • Start date Start date
M

mark kubicki

this is for a weekend summer house to track expenses

i setting up an attendance form, and will be tracking 2 to 10 housemates for
3 to 7 days, and for 2 time periods each day (daytime, and dinner). note: a
housemate's attendance might not be continuous (ex: if he has dinner at
someone else's house, that meal would not count; or if they were at the
house for 2 days, then left for 1 day, and came back for the next 2...; or
didnot arrive till halfway through the weekend... )

i am looking for direction on how a form could be setup.

originally i set up a form with an array of checkboxes (housemates /
date-daytime, date-dinner, next date...) (on the form, you could enter how
many days, and housemates were to be tracked; an appropriate number of check
boxes would be shown). ***checking that many boxes has proved top be
cumbersome, and i am hoping that a different approach may provide easier
data entry ???

thanks in advance
 
Why not just set up a matrix in the spread sheet. then put an x in the cell

You could use the double click event to actually do the entry if you want to
stay on your mouse.

An alternative is to assume full attendence, then only put an X for
absences - less entry.
 
....using the number of days and housemates entries (and some VB code) to set
borders and cell protection to create a limited range that data can (should)
be entered into... ?
 
Sub SetupSheet()
Dim numMates As Long
Dim numDays As Long
Dim rng As Range
numMates = 3
numDays = 7
Dim mates As Variant
mates = Array("John", "Jack", "Jason")
Cells.Locked = True
j = 0
For i = 2 To numDays * 2 Step 2
j = j + 1
Cells(1, i).Value = "Day " & j
Cells(1, i).Resize(1, 2).HorizontalAlignment = _
xlHAlignCenterAcrossSelection
Cells(1, i).Resize(1, 2).BorderAround , xlThin
Cells(2, i).Value = "Lunch"
Cells(2, i + 1).Value = "Dinner"
Next
j = 2
For i = LBound(mates) To UBound(mates)
j = j + 1
Cells(j, 1).Value = mates(i)
Next

Set rng = Range("B3").Resize(numMates, numDays * 2)
rng.Locked = False
appborders rng
appborders Range("B2").Resize(1, numDays * 2)
appborders Range("A3").Resize(numMates)
ActiveSheet.Protect
End Sub

Sub appborders(rng As Range)
For Each cell In rng
cell.BorderAround , xlThin
Next
End Sub
 
Back
Top