Automation is the future -
Below you will find VBA macros to be used in Microsoft Office Excel with their associated descriptions -
----------------------------------------------------------------------------------------------------------
Load up an Excel UserForm with Drop Down Combo Boxes
=================================================================
Private Sub CommandButtonSubmit_Click()
Dim port As Currency
Dim bg_dbc_st, bg_eem_st, bg_efa_st, bg_iwm_st, bg_iyr_st, bg_mdy_st, bg_spy_st As Currency
Dim md_dbc_st, md_eem_st, md_efa_st, md_iwm_st, md_iyr_st, md_mdy_st, md_spy_st As Currency
Dim nd_dbc_st, nd_eem_st, nd_efa_st, nd_iwm_st, nd_iyr_st, nd_mdy_st, nd_spy_st As Currency
Dim beginNum, endNum, thirteen_add, loop_counter As Integer
Dim dbc_st, eem_st, efa_st, iwm_st, iyr_st, mdy_st, spy_st As Single
Application.ScreenUpdating = False
Worksheets("Home").Range("bz2").Select
loop_counter = ActiveCell.Value
ActiveCell.Value = (ActiveCell.Value + 1)
If start_int_cmbo.Value = "T1" Then
beginNum = 1
ElseIf start_int_cmbo.Value = "T2" Then
beginNum = 2
ElseIf start_int_cmbo.Value = "T3" Then
beginNum = 3
ElseIf start_int_cmbo.Value = "T4" Then
beginNum = 4
ElseIf start_int_cmbo.Value = "T5" Then
beginNum = 5
ElseIf start_int_cmbo.Value = "T6" Then
beginNum = 6
ElseIf start_int_cmbo.Value = "T7" Then
beginNum = 7
ElseIf start_int_cmbo.Value = "T8" Then
beginNum = 8
ElseIf start_int_cmbo.Value = "T9" Then
beginNum = 9
ElseIf start_int_cmbo.Value = "T10" Then
beginNum = 10
ElseIf start_int_cmbo.Value = "T11" Then
beginNum = 11
ElseIf start_int_cmbo.Value = "T12" Then
beginNum = 12
ElseIf start_int_cmbo.Value = "T13" Then
beginNum = 13
ElseIf start_int_cmbo.Value = "T14" Then
beginNum = 14
ElseIf start_int_cmbo.Value = "T15" Then
beginNum = 15
ElseIf start_int_cmbo.Value = "T16" Then
beginNum = 16
ElseIf start_int_cmbo.Value = "T17" Then
beginNum = 17
ElseIf start_int_cmbo.Value = "T18" Then
beginNum = 18
ElseIf start_int_cmbo.Value = "T19" Then
beginNum = 19
ElseIf start_int_cmbo.Value = "T20" Then
beginNum = 20
ElseIf start_int_cmbo.Value = "T21" Then
beginNum = 21
ElseIf start_int_cmbo.Value = "T22" Then
beginNum = 22
ElseIf start_int_cmbo.Value = "T23" Then
beginNum = 23
ElseIf start_int_cmbo.Value = "T24" Then
beginNum = 24
ElseIf start_int_cmbo.Value = "T25" Then
beginNum = 25
ElseIf start_int_cmbo.Value = "T26" Then
beginNum = 26
ElseIf start_int_cmbo.Value = "T27" Then
beginNum = 27
ElseIf start_int_cmbo.Value = "T28" Then
beginNum = 28
ElseIf start_int_cmbo.Value = "T29" Then
beginNum = 29
ElseIf start_int_cmbo.Value = "T30" Then
beginNum = 30
ElseIf start_int_cmbo.Value = "T31" Then
beginNum = 31
ElseIf start_int_cmbo.Value = "T32" Then
beginNum = 32
ElseIf start_int_cmbo.Value = "T33" Then
beginNum = 33
ElseIf start_int_cmbo.Value = "T34" Then
beginNum = 34
ElseIf start_int_cmbo.Value = "T35" Then
beginNum = 35
ElseIf start_int_cmbo.Value = "T36" Then
beginNum = 36
End If
If end_int_cmbo.Value = "T1" Then
endNum = 1
ElseIf end_int_cmbo.Value = "T2" Then
endNum = 2
ElseIf end_int_cmbo.Value = "T3" Then
endNum = 3
ElseIf end_int_cmbo.Value = "T4" Then
endNum = 4
ElseIf end_int_cmbo.Value = "T5" Then
endNum = 5
ElseIf end_int_cmbo.Value = "T6" Then
endNum = 6
ElseIf end_int_cmbo.Value = "T7" Then
endNum = 7
ElseIf end_int_cmbo.Value = "T8" Then
endNum = 8
ElseIf end_int_cmbo.Value = "T9" Then
endNum = 9
ElseIf end_int_cmbo.Value = "T10" Then
endNum = 10
ElseIf end_int_cmbo.Value = "T11" Then
endNum = 11
ElseIf end_int_cmbo.Value = "T12" Then
endNum = 12
ElseIf end_int_cmbo.Value = "T13" Then
endNum = 13
ElseIf end_int_cmbo.Value = "T14" Then
endNum = 14
ElseIf end_int_cmbo.Value = "T15" Then
endNum = 15
ElseIf end_int_cmbo.Value = "T16" Then
endNum = 16
ElseIf end_int_cmbo.Value = "T17" Then
endNum = 17
ElseIf end_int_cmbo.Value = "T18" Then
endNum = 18
ElseIf end_int_cmbo.Value = "T19" Then
endNum = 19
ElseIf end_int_cmbo.Value = "T20" Then
endNum = 20
ElseIf end_int_cmbo.Value = "T21" Then
endNum = 21
ElseIf end_int_cmbo.Value = "T22" Then
endNum = 22
ElseIf end_int_cmbo.Value = "T23" Then
endNum = 23
ElseIf end_int_cmbo.Value = "T24" Then
endNum = 24
ElseIf end_int_cmbo.Value = "T25" Then
endNum = 25
ElseIf end_int_cmbo.Value = "T26" Then
endNum = 26
ElseIf end_int_cmbo.Value = "T27" Then
endNum = 27
ElseIf end_int_cmbo.Value = "T28" Then
endNum = 28
ElseIf end_int_cmbo.Value = "T29" Then
endNum = 29
ElseIf end_int_cmbo.Value = "T30" Then
endNum = 30
ElseIf end_int_cmbo.Value = "T31" Then
endNum = 31
ElseIf end_int_cmbo.Value = "T32" Then
endNum = 32
ElseIf end_int_cmbo.Value = "T33" Then
endNum = 33
ElseIf end_int_cmbo.Value = "T34" Then
endNum = 34
ElseIf end_int_cmbo.Value = "T35" Then
endNum = 35
ElseIf end_int_cmbo.Value = "T36" Then
endNum = 36
End If
On Error GoTo Blanks
bg_dbc_st = (DBC_interval_w8_start.Value / 100)
bg_eem_st = (EEM_interval_w8_start.Value / 100)
bg_efa_st = (EFA_interval_w8_start.Value / 100)
bg_iwm_st = (IWM_interval_w8_start.Value / 100)
bg_iyr_st = (IYR_interval_w8_start.Value / 100)
bg_mdy_st = (MDY_interval_w8_start.Value / 100)
bg_spy_st = (SPY_interval_w8_start.Value / 100)
Blanks:
MsgBox "Blanks values aren't acceptable. Please make entries: zero is ok for blanks."
Exit Sub
If (100 * (bg_dbc_st + bg_eem_st + bg_efa_st + bg_iwm_st + bg_iyr_st + bg_mdy_st + bg_spy_st) <> 100) Then GoTo Pass
Worksheets("Home").Range("bz2").Select
loop_counter = ActiveCell.Value
ActiveCell.Value = loop_counter + 1
Worksheets("Home").Cells(3, loop_counter + 79).Value = (bg_dbc_st * 100)
Worksheets("Home").Cells(4, loop_counter + 79).Value = (bg_eem_st * 100)
Worksheets("Home").Cells(5, loop_counter + 79).Value = (bg_efa_st * 100)
Worksheets("Home").Cells(6, loop_counter + 79).Value = (bg_iwm_st * 100)
Worksheets("Home").Cells(7, loop_counter + 79).Value = (bg_iyr_st * 100)
Worksheets("Home").Cells(8, loop_counter + 79).Value = (bg_mdy_st * 100)
Worksheets("Home").Cells(9, loop_counter + 79).Value = (bg_spy_st * 100)
thirteen_add = 5
For a_counter = beginNum To endNum
thirteen_add = (thirteen_add + 13)
begin_c_val = (thirteen_add + 5)
Sheets("Home").Cells(begin_c_val, 5).Select
ActiveCell.Value = bg_dbc_st * Sheets("Home").Cells(thirteen_add, 22)
Sheets("Home").Cells(begin_c_val + 1, 5).Select
ActiveCell.Value = bg_eem_st * Sheets("Home").Cells(thirteen_add, 22)
Sheets("Home").Cells(begin_c_val + 2, 5).Select
ActiveCell.Value = bg_efa_st * Sheets("Home").Cells(thirteen_add, 22)
Sheets("Home").Cells(begin_c_val + 3, 5).Select
ActiveCell.Value = bg_iwm_st * Sheets("Home").Cells(thirteen_add, 22)
Sheets("Home").Cells(begin_c_val + 4, 5).Select
ActiveCell.Value = bg_iyr_st * Sheets("Home").Cells(thirteen_add, 22)
Sheets("Home").Cells(begin_c_val + 5, 5).Select
ActiveCell.Value = bg_mdy_st * Sheets("Home").Cells(thirteen_add, 22)
Sheets("Home").Cells(begin_c_val + 6, 5).Select
ActiveCell.Value = bg_spy_st * Sheets("Home").Cells(thirteen_add, 22)
Next a_counter
Pass:
MsgBox "The weights do not add up to 100%. Please make the necessary corrections, and resubmit."
Exit Sub
Sheets("Home").Cells(1, 1).Select
Application.ScreenUpdating = True
MsgBox "start_int_cmbo box value is " & start_int_cmbo.Value & vbCrLf & "start number equals " & beginNum & vbCrLf & "end number equals " & endNum & vbCrLf & "bg_dbc_st value is " & bg_dbc_st & vbCrLf & "Loop Counting Value is: " & loop_counter & vbCrLf & vbInformation
End Sub
