Help! Please help with my code

[inbound03]

Hi all VBA genius:

Can someone help me with the following?

I have a UserForm with 5 check boxes; 5 Frames, each with optYes,
optNo, and optNA; one Frame with a combo box and an Enter button; one
Frame with a text box and an Enter button. The layout of the UserForm
is as follows:

Chk09 optYes optNo optN/A
Chk10
Chk11 optYes optNo optN/A
Chk12 optYes optNo optN/A
Chk13 optYes optNo optN/A

Combo box
Enter button


Text box (for chk10)
Enter button

When the UserForm is initialized, only chk09 is visible on the
UserForm. When user checks chk09, the corresponding frame with optYes,
optNo, and optN/A appears. If user selects optNo, the combo box and
the Enter button appear. When user clicks the Enter button, chk11
appears. This procedure runs every time the user chooses ¡§No¡¨ and
then hits the Enter button.

My problem is that I am able to make chk09, chk10, and chk11 visible
but not chk12 and chk13 when user clicks the enter button. How can I
make it work?

Here is the code I wrote for the Enter button with the combo box:

'Hide the combo box and the OK button after transferring respective
concern to "Recap"
Private Sub cmdGroundsEnter_Click()

cmdGroundsEnter.Default = True

Call chkUnhide

'Hide the frame
FrameConcernGrounds.Visible = False

'Check for completeness
If cmbConcernGrounds.Text = "" Then
MsgBox "Please select the security concern"
FrameConcernGrounds.Visible = True
Exit Sub
End If

' Find next available row
NextRow = Sheets("Recap").Range("a5000").End(xlUp).Row + 1

' Transfer the data
Sheets("Recap").Cells(NextRow, 1) = Qnum
Sheets("Recap").Cells(NextRow, 2) = Cat
Sheets("Recap").Cells(NextRow, 4) = cmbConcernGrounds.Text & Chr(15)
Sheets("Recap").Cells(NextRow, 7) = Action

' Reset the Userform for the next row
cmbConcernGrounds = ""

End Sub


Here is the code I wrote for optNo09:

'Action to be performed when the "No" button is clicked
Private Sub opt09No_Click()

Sheets("Report").Range("H119") = "No"
Sheets("Report").Range("H124") = "N/A"

'Show combo box in UserForm
If opt09No = True Then
FrameConcernGrounds.Visible = True

'Select the corresponding question number, category & Corrective
Action
Qnum = Sheets("RiskMatrix").Range("A2")
Cat = Sheets("RiskMatrix").Range("b2")
Action = Sheets("RiskMatrix").Range("c2")
'Select and show the corresponding concern in the combo box in
UserForm
cmbConcernGrounds.RowSource = "RiskMatrix!$e$2:$e$3"

Else
FrameConcernGrounds.Visible = False
End If

End Sub


Here is the call procedure I wrote:

Public Sub chkUnhide()

Select Case CheckBox
Case chk09.Value = True
chk11.Visible = True
Case chk11.Value = True
chk12.Visible = True
Case chk12.Value = True
chk13.Visible = True
End Select

End sub



Alex

 

[Rob van Gelder]

Alex,

I see opt09No_Click() but I don't see opt11No_Click() or opt12No_Click()
Perhaps you need to do the same for those missing procedures?

Rob

 

[inbound03]

Hi Rob:

Here are the code for chk11, chk12, and chk13. Except for minor
differenence, the code for the check boxes are almost identical. I
wonder if I code it wrong in the Enter button procedure. Thanks in
advance.

code for opt11No_click:

'Action to be performed when the "No" button is clicked
Private Sub opt11No_Click()

Sheets("Report").Range("H126") = "No"

'Show combo box in UserForm
If opt11No = True Then
FrameConcernGrounds.Visible = True

'Select the corresponding question number, category &
Corrective Action
Qnum = Sheets("RiskMatrix").Range("A4")
Cat = Sheets("RiskMatrix").Range("b4")
Action = Sheets("RiskMatrix").Range("c4")
'Select and show the corresponding concern in the combo box in
UserForm
cmbConcernGrounds.RowSource = "RiskMatrix!$G$2:$G$4"
Else
FrameConcernGrounds.Visible = False
End If

End Sub


Code for opt12No_click()

'Action to be performed when the "No" button is clicked
Private Sub opt12No_Click()

Sheets("Report").Range("H128") = "No"

'Show combo box in UserForm
If opt12No = True Then
FrameConcernGrounds.Visible = True

'Select the corresponding question number, category &
Corrective Action
Qnum = Sheets("RiskMatrix").Range("A5")
Cat = Sheets("RiskMatrix").Range("b5")
Action = Sheets("RiskMatrix").Range("c5")
'Select and show the corresponding concern in the combo box in
UserForm
cmbConcernGrounds.RowSource = "RiskMatrix!$h$2:$h$4"
Else
FrameConcernGrounds.Visible = False
End If

End Sub



Alex

 

[inbound03]

Can anyone help?

 

[Rob van Gelder]

Alex,

Not so sure about your Unhide procedure:
You could try something like:

Public Sub chkUnhide()
If chk09.Value = True Then chk11.Visible = True
If chk11.Value = True Then chk12.Visible = True
If chk12.Value = True Then chk13.Visible = True
End Sub


The other suggestion I would make is for you to use the debugger. It is your
friend.
Instead of running the form (F5), press F8 instead. F8 will step you through
the code, line by line.

Hope this helps

Rob