mikeoly
12-09-2012, 08:31 PM
I would like to put my coding into a module from the 'sub cbOK_Click()'
Every time I try I run into trouble.
Sub cbOK_Click()
Dim Risk1 As Range
Dim Risk2 As Range
Dim Risk3 As Range
Dim Risk4 As Range
Dim Risk5 As Range
Dim r As Long, c As Long
Dim CompanyValueChange As String
If cbRisk1.Value = True Then
Set Risk1 = ActiveWorkbook.Worksheets("Sheet3").Range("R1." & ComboBox1.Value)
End If
If cbRisk2.Value = True Then
Set Risk2 = ActiveWorkbook.Worksheets("Sheet3").Range("R2." & ComboBox2.Value)
End If
If cbRisk3.Value = True Then
Set Risk3 = ActiveWorkbook.Worksheets("Sheet3").Range("R3." & ComboBox3.Value)
End If
If cbRisk4.Value = True Then
Set Risk4 = ActiveWorkbook.Worksheets("Sheet3").Range("R4." & ComboBox4.Value)
End If
If cbRisk5.Value = True Then
Set Risk5 = ActiveWorkbook.Worksheets("Sheet3").Range("R5." & ComboBox5.Value)
End If
With Range("RiskCompile")
If cbRisk1.Value = True Then
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count
.Cells(r, c).Value = .Cells(r, c).Value + Risk1.Cells(r, c).Value
Next c
Next r
End If
If cbRisk2.Value = True Then
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count
.Cells(r, c).Value = .Cells(r, c).Value + Risk2.Cells(r, c).Value
Next c
Next r
End If
If cbRisk3.Value = True Then
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count
.Cells(r, c).Value = .Cells(r, c).Value + Risk3.Cells(r, c).Value
Next c
Next r
End If
If cbRisk4.Value = True Then
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count
.Cells(r, c).Value = .Cells(r, c).Value + Risk4.Cells(r, c).Value
Next c
Next r
End If
If cbRisk5.Value = True Then
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count
.Cells(r, c).Value = .Cells(r, c).Value + Risk5.Cells(r, c).Value
Next c
Next r
End If
End With
CompanyValueChange = ActiveWorkbook.Worksheets("Sheet1").Range("C77").Value
MsgBox ("Microsoft's risks can have a " & Format(CompanyValueChange, "Percent") & " or $" & Format((ActiveWorkbook.Worksheets("Sheet1").Range("C76").Value - ActiveWorkbook.Worksheets("Sheet1").Range("C75").Value) * (1000000), "###,###,000,000") & " change on the company's value."), vbInformation
Unload Me
End Sub
Every time I try I run into trouble.
Sub cbOK_Click()
Dim Risk1 As Range
Dim Risk2 As Range
Dim Risk3 As Range
Dim Risk4 As Range
Dim Risk5 As Range
Dim r As Long, c As Long
Dim CompanyValueChange As String
If cbRisk1.Value = True Then
Set Risk1 = ActiveWorkbook.Worksheets("Sheet3").Range("R1." & ComboBox1.Value)
End If
If cbRisk2.Value = True Then
Set Risk2 = ActiveWorkbook.Worksheets("Sheet3").Range("R2." & ComboBox2.Value)
End If
If cbRisk3.Value = True Then
Set Risk3 = ActiveWorkbook.Worksheets("Sheet3").Range("R3." & ComboBox3.Value)
End If
If cbRisk4.Value = True Then
Set Risk4 = ActiveWorkbook.Worksheets("Sheet3").Range("R4." & ComboBox4.Value)
End If
If cbRisk5.Value = True Then
Set Risk5 = ActiveWorkbook.Worksheets("Sheet3").Range("R5." & ComboBox5.Value)
End If
With Range("RiskCompile")
If cbRisk1.Value = True Then
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count
.Cells(r, c).Value = .Cells(r, c).Value + Risk1.Cells(r, c).Value
Next c
Next r
End If
If cbRisk2.Value = True Then
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count
.Cells(r, c).Value = .Cells(r, c).Value + Risk2.Cells(r, c).Value
Next c
Next r
End If
If cbRisk3.Value = True Then
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count
.Cells(r, c).Value = .Cells(r, c).Value + Risk3.Cells(r, c).Value
Next c
Next r
End If
If cbRisk4.Value = True Then
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count
.Cells(r, c).Value = .Cells(r, c).Value + Risk4.Cells(r, c).Value
Next c
Next r
End If
If cbRisk5.Value = True Then
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count
.Cells(r, c).Value = .Cells(r, c).Value + Risk5.Cells(r, c).Value
Next c
Next r
End If
End With
CompanyValueChange = ActiveWorkbook.Worksheets("Sheet1").Range("C77").Value
MsgBox ("Microsoft's risks can have a " & Format(CompanyValueChange, "Percent") & " or $" & Format((ActiveWorkbook.Worksheets("Sheet1").Range("C76").Value - ActiveWorkbook.Worksheets("Sheet1").Range("C75").Value) * (1000000), "###,###,000,000") & " change on the company's value."), vbInformation
Unload Me
End Sub