PDA

View Full Version : Sleeper: Two comboboxes update puzzle



skahilu
05-10-2005, 04:30 AM
I have two comboxes named cboClassID and cboClass which update worksheet cells named ClassIDCell2 and ClassCell2.The cboClassId has its rowsource set to ClassID.Index and cboClass is set to rowsource Class.Index. When I type 1 in ID combobox then Class combobox should have an item class corresponding to the 1 entered in ID combobox. This should also be viceversa when typing in cboClass.It should give corresponding value in cboClassId.Problem comes when entering new ClassID.The cboClass needs to be blank so as to allow inserting a new class.The same should happen when entering new class.The cboClassID should be blank.All these should be combobox change events.
Please assist as I have trying to figure this out for a month now.
See my codes below.


Private Sub cboClass_AfterUpdate()
If cboClassID.Value = "" Or Range("ClassIDCell3").Value <> Range ("ClassIDCell2").Value Then
' MsgBox "Hello"
Range("ClassIDCell3").Value = Range("ClassIDCell2").Value
Me.cboClassID.Value = Range("ClassIDCell2").Value
End If
If Range("ClassCell3").Value <> Range("ClassCell2").Value Then
'XX is MsgBox
Call xx
End If
End If
Range("ClassCell3").Value = Me.cboClass.Value
End Sub[/vba]
[vba]Private Sub cboClass_Change()
Range("ClassCell3").Value = Me.cboClass.Value
End Sub

Private Sub cboClass_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If cboClassID.Value = "" Then
MsgBox "Hello"
Range("ClassIDCell3").Value = Range("ClassIDCell2").Value
End If
End Sub

Private Sub cboClassID_Change()
On Error Resume Next
Set ws = Worksheets
Set f = ws("Formulas")
f.Range("ClassIDCell3").Value = Me.cboClassID.Value
Me.cboClass.Value = f.Range("ClassCell2").Value
Then
'***If new Class ID is entered class should be as follows
If Range("ClassIDCell2").Value = "" Or Range("ClassCell2").Value = "" Then
Me.cboClass.Text = "Enter new Class"
Me.cboClass.Font.Size = 7.8
Me.cboClass.ForeColor = RGB(255, 0, 0)
f.Range("ClassCell3").ClearContents
Else
Me.cboClass.ForeColor = &H80000008
Me.cboClass.Font.Size = 8
End If
End Sub

Sub xx()
Select Case MsgBox("MSG", vbCritical + vbYesNoCancel)
Case vbYes
MsgBox "yes"
Case vbNo
MsgBox "no"
Case vbCancel
MsgBox "cancel"
End Select
End Sub

Bob Phillips
05-10-2005, 05:53 AM
I would start by adding Opton Explicit at the head of your code and defining all of your variables, get rid of the On Error resume Next which just masks problems, and correct all the syntax errors.

Then try stepping through and see if you can be a bit more explicit with the problem.