Hi Excel Gurus,
I built a userform that allows the end user to input data that will ultimately transfer into a table. For 1 risk item, there are 3 possible risk assessments. The 3 possible risk assessments are transferred into a table called "Risk_Assessment". The way the userform functions, the user has an option to check a box to "enable" the fields to input the information for each risk assessment.
The logic is, if the checkbox is checked, when the submit button is clicked, it will transfer all the related information into a table, but if it's not checked, it shouldn't transfer any information. The challenge is for example, I only have one risk assessment, the other two risk assessments still get transfered into the excel table but with blank values.
How do i make this work? The vba code is below. Thanks in advance!
I built a userform that allows the end user to input data that will ultimately transfer into a table. For 1 risk item, there are 3 possible risk assessments. The 3 possible risk assessments are transferred into a table called "Risk_Assessment". The way the userform functions, the user has an option to check a box to "enable" the fields to input the information for each risk assessment.
The logic is, if the checkbox is checked, when the submit button is clicked, it will transfer all the related information into a table, but if it's not checked, it shouldn't transfer any information. The challenge is for example, I only have one risk assessment, the other two risk assessments still get transfered into the excel table but with blank values.
How do i make this work? The vba code is below. Thanks in advance!
VBA Code:
Private Sub cmdSubmit_Click()
'Transfer to Risk Register table
Dim RiskNo As String
Dim RiskCategory As String
Dim RiskStatement As String
Dim RiskOwner As String
Dim ExistingControls As String
Dim ControlOwner As String
Dim ALARP As String
Dim Comments As String
RiskNo = txtRiskNo.Text
RiskCategory = cboRiskCat.Text
RiskStatement = txtRiskStatement.Text
RiskOwner = txtRiskOwner.Text
ExistingControls = txtExistControl.Text
ControlOwner = txtContOwner.Text
ALARP = cboALARP.Text
Comments = txtComments.Text
Dim wsRR As Worksheet
Set wsRR = Sheets("Risk Register")
Dim tblRR As ListObject
Set tblRR = wsRR.ListObjects("Risk_Register")
Dim newRowRR As ListRow
Set newRowRR = tblRR.ListRows.Add
With newRowRR
.Range(1) = RiskNo
.Range(2) = RiskCategory
.Range(3) = RiskStatement
.Range(4) = RiskOwner
.Range(5) = ExistingControls
.Range(6) = ControlOwner
.Range(7) = ALARP
.Range(11) = Comments
End With
'Transfer to Risk Assessment table
'Risk Assessment1
Dim RiskGrouping1 As String
Dim Severity1 As String
Dim Likelihood1 As String
Dim Mitigation1 As String
Dim MitigationOwner1 As String
Dim TargetDate1 As String
Dim MitSeverity1 As String
Dim MitLikelihood1 As String
Dim Comments1 As String
Dim MitStatus1 As String
RiskGrouping1 = cboRiskGrouping1.Text
Severity1 = cboSeverity1.Text
Likelihood1 = cboLikelihood1.Text
Mitigation1 = txtMitigation1.Text
MitigationOwner1 = txtMitigationOwner1.Text
TargetDate1 = txtTargetDate1.Text
MitSeverity1 = cboMSeverity1.Text
MitLikelihood1 = cboMLikelihood1.Text
Comments1 = txtComments1.Text
MitStatus1 = cboMitStatus1.Text
Dim wsRA1 As Worksheet
Set wsRA1 = Sheets("Risk Assessment")
Dim tblRA1 As ListObject
Set tblRA1 = wsRA1.ListObjects("Risk_Assessment")
Dim newRowRA1 As ListRow
Set newRowRA1 = tblRA1.ListRows.Add
If chkEnable1.Value = True Then
With newRowRA1
.Range(1) = RiskNo
.Range(2) = 1
.Range(3) = RiskGrouping1
.Range(4) = Severity1
.Range(5) = Likelihood1
.Range(7) = Mitigation1
.Range(8) = MitigationOwner1
.Range(9) = TargetDate1
.Range(10) = MitSeverity1
.Range(11) = MitLikelihood1
.Range(13) = MitStatus1
.Range(14) = Comments1
End With
Else
End If
'Risk Assessment2
Dim RiskGrouping2 As String
Dim Severity2 As String
Dim Likelihood2 As String
Dim Mitigation2 As String
Dim MitigationOwner2 As String
Dim TargetDate2 As String
Dim MitSeverity2 As String
Dim MitLikelihood2 As String
Dim Comments2 As String
Dim Mitstatus2 As String
RiskGrouping2 = cboRiskGrouping2.Text
Severity2 = cboSeverity2.Text
Likelihood2 = cboLikelihood2.Text
Mitigation2 = txtMitigation2.Text
MitigationOwner2 = txtMitigationOwner2.Text
TargetDate2 = txtTargetDate2.Text
MitSeverity2 = cboMSeverity2.Text
MitLikelihood2 = cboMLikelihood2.Text
Comments2 = txtComments2.Text
Mitstatus2 = cboMitStatus2.Text
Dim wsRA2 As Worksheet
Set wsRA2 = Sheets("Risk Assessment")
Dim tblRA2 As ListObject
Set tblRA2 = wsRA2.ListObjects("Risk_Assessment")
Dim newRowRA2 As ListRow
Set newRowRA2 = tblRA2.ListRows.Add
If chkEnable2.Value = True Then
With newRowRA2
.Range(1) = RiskNo
.Range(2) = 2
.Range(3) = RiskGrouping2
.Range(4) = Severity2
.Range(5) = Likelihood2
.Range(7) = Mitigation2
.Range(8) = MitigationOwner2
.Range(9) = TargetDate2
.Range(10) = MitSeverity2
.Range(11) = MitLikelihood2
.Range(13) = Mitstatus2
.Range(14) = Comments2
End With
Else
End If
'Risk Assessment3
Dim RiskGrouping3 As String
Dim Severity3 As String
Dim Likelihood3 As String
Dim Mitigation3 As String
Dim MitigationOwner3 As String
Dim TargetDate3 As String
Dim MitSeverity3 As String
Dim MitLikelihood3 As String
Dim Comments3 As String
Dim MitStatus3 As String
RiskGrouping3 = cboRiskGrouping3.Text
Severity3 = cboSeverity3.Text
Likelihood3 = cboLikelihood3.Text
Mitigation3 = txtMitigation3.Text
MitigationOwner3 = txtMitigationOwner3.Text
TargetDate3 = txtTargetDate3.Text
MitSeverity3 = cboMSeverity3.Text
MitLikelihood3 = cboMLikelihood3.Text
Comments3 = txtComments3.Text
MitStatus3 = cboMitStatus3.Text
Dim wsRA3 As Worksheet
Set wsRA3 = Sheets("Risk Assessment")
Dim tblRA3 As ListObject
Set tblRA3 = wsRA3.ListObjects("Risk_Assessment")
Dim newRowRA3 As ListRow
Set newRowRA3 = tblRA3.ListRows.Add
If chkEnable3.Value = True Then
With newRowRA3
.Range(1) = RiskNo
.Range(2) = 3
.Range(3) = RiskGrouping3
.Range(4) = Severity3
.Range(5) = Likelihood3
.Range(7) = Mitigation3
.Range(8) = MitigationOwner3
.Range(9) = TargetDate3
.Range(10) = MitSeverity3
.Range(11) = MitLikelihood3
.Range(13) = MitStatus3
.Range(14) = Comments3
End With
Else
End If
Unload Me
End Sub
