We have a preregistration Excel spreadsheet database for a retirement community's continuing education program. The database has 37 data columns of which 3 are calculated, one is a date format one and all the rest receive either a zero or a one as indicators of whether a resident registers or not for a lecture/class (really a 1 or the field is left blank). It currently has 1126 rows reflecting 1123 residents.
If the residents preregister they mostly pay $1 for the class, but some events are musical performances which are $8 upfront. If they show up without having preregistered they pay $2 and $9 respectively. Also, some are classes that are more than one time events and registration for these can be up to $111, thus there is a need for calculations to figure out how much of a check the resident needs to have sent in with their registration form. What the check amount is the grand total column. We also have subtotals for the one-time events and another one for the multiple-day classes.
I found an an excellent recently defunct mostly free Excel add-in, which allows an unlimited number of fields, so the 32 limit on the fields for the form is not an issue. I cracked the password, so I have access to the VBA of the form and was able to add a couple of buttons, for instance, one that fills in a one for all the evening events as that is a common choice for the registers. I would be happy to pay the $15 to the programmer for the password privilege that he used to charge and will try to get in touch to do that.
What I would like to know is if it is possible for the form to instantly update the spreadsheet and the calculated fields and then the calculated fields to be updated on the form right away. I do have an update button but would like the spreadsheet to be updated as the data is entered in case the enterer forgets to press the update button. I realize that entering the data directly into the spreadsheet accomplishes that but I would like to stick with the form.
I figure this would involve somehow recognizing a change in the state of the form, but I don't know how to write the VBA to do that. I have been able to figure out how to re-enter the calculated amounts in the form, once the update button is pressed, which can then be compared against the resident's check amount from the total presented on the form.
Here is I believe where the relevant VBA would be inserted or a separate subroutine based on one or both subs would come from:
Sub UpdateForm()
'This sub updates the fields in the form
Dim ctl As Control
Dim Col As Long
Dim CurrentCell As Range
Col = 0
On Error Resume Next
For Each ctl In Frame1.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
Col = Col + 1
Set CurrentCell = Cells(CurrentRecord + RowOffset, Col + ColumnOffset)
ctl = CurrentCell
If CurrentCell.PrefixCharacter = "'" Then ctl = "'" & ctl
'Check for True/False cells (they would appear as 0 or -1)
If Application.WorksheetFunction.IsLogical(CurrentCell) Then
ctl = CurrentCell.Text
End If
'Is the cell displaying an error value?
If Err <> 0 Then
ctl = CurrentCell.Text 'Display this if the cell has an error value
Err = 0
End If
' Date?
If IsDate(Cells(CurrentRecord + RowOffset, Col + ColumnOffset)) Then
ctl = CurrentCell.Text
End If
'Formula?
If Cells(CurrentRecord + RowOffset, Col + ColumnOffset).HasFormula Then
ctl = FormatCurrency(CurrentCell)
ctl.Enabled = False
ctl.BackColor = RGB(240, 240, 240)
Else
ctl.Enabled = True
ctl.BackColor = RGB(255, 255, 255)
End If
End If
Next ctl
LabelRecNum = Text(9) & " " & CurrentRecord & " " & Text(10) & " " & RecordCount
On Error GoTo 0
End Sub
Sub UpdateDatabase()
'Updates the database with new data from the form
Dim ctl As Control
Dim Col As Long
Dim TestCell As Range
Dim NumberWritten As Long
Col = 0
NumberWritten = 0
For Each ctl In Frame1.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
Col = Col + 1
Set TestCell = Cells(CurrentRecord + RowOffset, Col + ColumnOffset)
If Not TestCell.HasFormula Then ' Don't check formula cells
'Use Clean so cells with non-printing characters will be compared correctly
If TestCell.PrefixCharacter & Application.WorksheetFunction.Clean(TestCell) <> Application.WorksheetFunction.Clean(ctl.Text) Then
'Save original data for undo
NumberWritten = NumberWritten + 1
ReDim Preserve UndoArray(1 To NumberWritten)
With UndoArray(NumberWritten)
.Address = TestCell.Address
.Contents = TestCell.PrefixCharacter & TestCell.Text
.RecNum = CurrentRecord
End With
'write the new data
'(date check code is new in v3) - revised Aug 6-08
If IsRealDate(ctl.Text) Then
If IsDate(DateValue(ctl.Text)) Then
TestCell.Value = CDate(ctl.Text)
End If
Else
TestCell.Value = ctl.Text
End If
End If
End If
End If
Next ctl
If NumberWritten <> 0 Then
UndoButton.Caption = Text(18) '"Undo Entry"
UndoButton.Visible = True
End If
End Sub
Also if anybody has any suggestions on how to gather the data and calculate more efficiently, I'd be happy to hear it. We are using manual forms now with checkboxes and rely on the seniors to calculate their check amounts. The checkmarks are transferred to the Excel spreadsheet manually. I proposed putting a computer-readable form in the course catalog, such as the ones you find for taking standardized tests, but that was shot down as being too complicated for seniors. I don't agree and would like to try it, but I'm new here and not in charge, so I can't implement the experiment.
Edit per PEH: Here is the code that doesn't work -
Sub FormMain_Change()
UpdateDatabase
Call UpdateForm
End Sub
Nor does adding ".ControlSource" to my controls ("ctl"s) in the UpdateForm codeblock fixes things.
