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