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:mad:

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()
Unload Me
End Sub


Private Sub cmdUpdate_Click()
'Set appropriate worksheet as active for input
If optMod = True Then
ElseIf optSupSedan = True Then
ElseIf optModBike = True Then
ElseIf optStBike = True Then
ElseIf optStreet = True Then
ElseIf optJun = True Then
ElseIf optBurn = True Then
End If
'Find the next empty row for input
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

End Sub

Private Sub UserForm_Click()

End Sub

End code.

Thanks in advance




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
worksheets("Totals").Range("B9") = num

End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question