Excel VBA - Multi sheet filtering/querying(?) & combo box woes

[Don Hansford]

Hi People!

Easy one first - , UserForm will not initialize on loading, unles
user hits the Clear Form button first. But doing that appends ye
another list to the combo boxes. Driving me nuts, and I'll bet th
solution is staring me in the face

Slightly more difficult one now -
I need to be able to sort through the sheets and return to a cell i
the "Totals" sheet, a value for the number of Entrants paying for mor
than one round. I have a column in each sheet containing the number
(1, 2, or 3) they have paid for.

Code follows::
------------------------------------------------
Private Sub cmdClear_Click()
Call frmInput_Initialize
End Sub
------------------------------------------------
Private Sub cmdExit_Click()
ActiveWorkbook.Sheets("Totals").Activate
Unload Me
End Sub

------------------------------------------------

Private Sub cmdUpdate_Click()
'Set appropriate worksheet as active for input
If optMod = True Then
ActiveWorkbook.Sheets("Modified").Activate
ElseIf optSupSedan = True Then
ActiveWorkbook.Sheets("SuperSedan").Activate
ElseIf optModBike = True Then
ActiveWorkbook.Sheets("ModBike").Activate
ElseIf optStBike = True Then
ActiveWorkbook.Sheets("StreetBike").Activate
ElseIf optStreet = True Then
ActiveWorkbook.Sheets("Street").Activate
ElseIf optJun = True Then
ActiveWorkbook.Sheets("Junior").Activate
ElseIf optBurn = True Then
ActiveWorkbook.Sheets("Burnout").Activate
End If
'Find the next empty row for input
Range("A3").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
'Add data from form to worksheet row
ActiveCell.Value = txtName.Value
ActiveCell.Offset(0, 1) = txtNum.Value
ActiveCell.Offset(0, 2) = cboPay.Value
ActiveCell.Offset(0, 3) = txtAmount.Value

If optOne = True Then
ActiveCell.Offset(0, 4).Value = "1"
ElseIf optTwo = True Then
ActiveCell.Offset(0, 4).Value = "2"
ElseIf optThree = True Then
ActiveCell.Offset(0, 4).Value = "3"
End If
If optBurn = True Then
ActiveCell.Offset(0, 5) = cboBurn.Value
End If

'Call msgbox to see if any more input desired
Dim Msg, Style, Title, Response
' Define messagebox content.
Msg = "Entry Added - Do you want to add another entry ?"
Style = vbYesNo ' Define buttons.
Title = "Add Another Entry?" ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
Call frmInput_Initialize ' Restart the form
Else ' User chose No.
Call cmdExit_Click ' Kill the form
End If


End Sub
-----------------------------------------------

'Set up the form for input
Private Sub frmInput_Initialize()
'Clear all text inputs
txtName.Value = ""
txtNum.Value = ""
txtAmount.Value = ""
'Set up the comboboxes
With cboPay
.AddItem "Cash"
.AddItem "Cheque"
.AddItem "Credit Card"
.AddItem "Bank Transfer"
End With
With cboBurn
.AddItem "4 Cyl"
.AddItem "6 Cyl"
.AddItem "8 Cyl"
End With
'Set some defaults
cboPay.Value = "Cash"
cboBurn.Value = ""
optOne.Value = True
optMod.Value = True
txtName.SetFocus

End Sub
-------------------------------------------

Private Sub UserForm_Click()

End Sub
-------------------------------------------

End code.

Thanks in advance

Do

 

[Tom Ogilvy]

Private Sub Userform_Initialize()
Call frmInput_Initialize
End Sub

will initialize the form when it is first loaded.

Sub AddItems
num = 0
for each sh in Activeworkbook.worksheets
if sh.Name <> "Totals" then
num = num + Application.countif(sh.Columns(5),">=2")
end if
Next
worksheets("Totals").Range("B9") = num

End Sub