PDA

View Full Version : Solved: Another userform problem - frames



blackie42
10-17-2007, 07:14 AM
I have a userform set up which requires some text box details to be completed before moving on to a number of frames.

In each frame there is combo box (refers to a sheet in book) followed by a couple of text boxes (one for ? one for shares - at least one must be completed). This is followed by 6 option buttons - I have some additional code in a separate module that decides which option buttons are 'live' depending on whats in the combo box. Then a togglebutton.

I have also got messages set up so that if any of the details in any of the text boxes outside the frames OR any details in the first frame are omitted - clicking the "ADD" command button doesn't parse the info to the sheet.

My problem is that if I select frame 2 and do not select an option button then I get the message but it adds the deatils from frame 1 - when I select the option in frame 2 it then adds frame 2 but also frame 1 again.

excerpt of code - works from command event

Sub Validate_Form()
Dim msg, i, stat, OptCol

'-----------------------------------------
' Check for Name
'-----------------------------------------
If form.txtName = "" Then
MsgBox "No Client Name entered"
End If
'-----------------------------------------
'Check for Plan
'-----------------------------------------
If form.txtPlan = "" Then
MsgBox "No Plan Entered"

End If

'-----------------'-----------------------
'Check for reserved by
'-----------------------------------------
If form.txtres = "" Then
MsgBox "Must complete reserved by!"
Exit Sub
ElseIf form.cmb1 = "" Then
MsgBox "Select Fund"
Exit Sub
End If
'-----------------------------------------
' For each set, if fund is selected, check for GBP or Shares.
'-----------------------------------------
' Set 1
'-----------------------------------------
If (form.cmb1.Value <> "") Then
'Validate GBP or Shares
'-----------------------------------------
If (form.txtGBP1 = "" And form.txtShare1 = "") Then
MsgBox "Enter amount in GBP or Shares"
Exit Sub
End If

'------------------------------------------------
If (form.optB1 = False _
And form.optC1 = False _
And form.optD1 = False _
And form.OptE1 = False _
And form.OptF1 = False _
And form.OptG1 = False) Then
MsgBox "Select Share Class"
Exit Sub
Else
If form.optB1 = True Then
OptCol = 6
ElseIf form.optC1 = True Then
OptCol = 9
ElseIf form.optD1 = True Then
OptCol = 12
ElseIf form.OptE1 = True Then
OptCol = 15
ElseIf form.OptF1 = True Then
OptCol = 18
ElseIf gorm.OptG1 = True Then
OptCol = 21
End If

End If
stat = EnterDataInWorksheet(form.txtName, form.txtPlan, form.cmbFund1, form.txtres, form.txtGBP1, form.txtShare1, OptCol, form.ToggleButton1.Caption, form.txtDate)

End If

'------------------------------------------------
' Set 2
'------------------------------------------------

If (form.cmbFund2.Value <> "") Then
'Validate GBP or Shares
'------------------------------------------------
If (form.txtGBP2 = "" And form.txtShare2 = "") Then
MsgBox "Enter amount in GBP or Shares"
Exit Sub
End If
'------------------------------------------------
If (form.optB2 = False _
And form.optC2 = False _
And form.optD2 = False _
And form.OptE2 = False _
And form.OptF2 = False _
And form.OptG2 = False) Then
MsgBox "Select Share Class"
Exit Sub
Else
If form.optB2 = True Then
OptCol = 6
ElseIf form.optC2 = True Then
OptCol = 9
ElseIf form.optD2 = True Then
OptCol = 12
ElseIf form.OptE2 = True Then
OptCol = 15
ElseIf form.OptF2 = True Then
OptCol = 18
ElseIf form.OptG2 = True Then
OptCol = 21
End If
End If
stat = EnterDataInWorksheet(form.txtName, form.txtPlan, form.cmbFund2, form.txtres, form.txtGBP2, form.txtShare2, OptCol, form.ToggleButton2.Caption, form.txtDate)
End If

stat is a function that parses the info to the correct sheet.

I tried clearing each frame set after "ADD" but this disallows any other frame sets to be enter - as the first is now empty.

I've ended up putting message out in userform_initilaize to be careful that all details are complete before commiting -any ideas what to do. File is to big to upload as it has much more code in it.

thanks

blackie42
10-17-2007, 07:33 AM
I have put a messgae in the command-click event prior to going in to
the sub

Private Sub cmdAdd_Click()
Dim reply As Integer
reply = MsgBox("Sure all details are complete?", vbYesNo)
If reply = vbYes Then
Validate_Form
'EnterDataInWorksheet
Else: Exit Sub
End If
End Sub

This should prompt user to double check each selected frame - but if anyone has any ideas how to 'fix' would be interested and grateful

regards

Jon

- big learning curve

lucas
10-17-2007, 07:42 AM
Hi Blackie,
would it be feasible to disable the submit button until all fields are filled?
Private Sub Userform_Initialize()
CommandButton1.Enabled = False
End Sub