Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Please help with my code
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 --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Please help with my code
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 " wrote in message ... 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 --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Please help with my code
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 --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Please help with my code
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Please help with my code
Alex,
Not so sure about your Unhide procedu 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 "inbound03 " wrote in message ... 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 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) |