PDA

View Full Version : Solved: UserForms & Modules



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

Teeroy
12-10-2012, 01:40 AM
What error are you getting and what do you want out of this code? Without context it is hard to provide much help. Can you post a sample workbook?

snb
12-10-2012, 02:36 AM
If you are using named ranges, it's hard to tell anything as long as you do not provide any information on those named ranges.
Your whole code can be reduced to a 5-liner, using a loop.