AW: Dynamische Range per Listbox in UserForm anzei
18.07.2005 19:35:44
Karl
Hi Matthias,
jetzt wird es mir aber langsam peinlich das ich Deine Zeit raube...
Vielleicht kannst Du ja mal kurz schauen und wenn du was findest mir zurueckschreiben. Vielen Dank auf jeden Fall fuer deinen Einsatz,
Public
Sub CommandButton1_Click()
LoadUserForm1
End Sub
Public
Sub LoadUserForm1()
Dim n As Integer
Dim EIB As Range
Dim IRS As Range
' n = Worksheets("Input").[n].Value
'UserForm1.List_EIB.List = WorksheetFunction.Transpose(Range("A1:D60").Value
'Listboxes and Comboboxes
n = ActiveSheet.[n].Value
EIB = Worksheets("EIB").Range("A1", ActiveCell.Offset(69, n + 3))
IRS = Worksheets("IRS").Range("A1", ActiveCell.Offset(70, n + 3))
UserForm1.List_EIB.RowSource = EIB
UserForm1.List_IRS.RowSource = IRS
With UserForm1.Financing
.RowSource = ""
.AddItem "All Equity"
.AddItem "Partially Amortizing"
.AddItem "Fully Amortizing"
End With
'With UserForm1.Sensitivity
'.RowSource = ""
'.AddItem "IRR"
'.AddItem "NPV"
'.AddItem "Minimum Cash Balance"
'.AddItem "Operating expenses"
'End With
With UserForm1.Mainuse
.RowSource = ""
.AddItem "A"
.AddItem "R"
.AddItem "ST"
.AddItem "STS"
End With
'Cost
'Expenses
UserForm1.Utilities.Value = Format(ActiveSheet.[UT], "$#,###0")
UserForm1.Propertytaxes.Value = Format(ActiveSheet.[PT], "$#,###0")
UserForm1.Insurance.Value = Format(ActiveSheet.[Ins], "$#,###0")
UserForm1.Managementfee.Value = Format(ActiveSheet.[MF], "$#,###0")
UserForm1.Commonarea.Value = Format(ActiveSheet.[CAM], "$#,###0")
UserForm1.OE.Value = Format(ActiveSheet.[OE], "$#,###0")
UserForm1.OEGN.Value = Format(ActiveSheet.[OEGN], "#0.00%")
'Fees
UserForm1.Brokeragefees.Value = Format(ActiveSheet.[BF], "#0.00%")
UserForm1.Leasingcommissionnew.Value = Format(ActiveSheet.[lcn], "#0.00%")
UserForm1.Leasingcommissionrenew.Value = Format(ActiveSheet.[lcr], "#0.00%")
UserForm1.Upfitexpenses.Value = Format(ActiveSheet.[UpfExp], "$#,###0")
UserForm1.Upfitexpensesg.Value = Format(ActiveSheet.[UpfExpG], "#0.00%")
'Acquisition / Sale Cost
UserForm1.Bidprice.Value = Format(ActiveSheet.[Bidprice], "$#,###0")
UserForm1.Acquisition.Value = Format(ActiveSheet.[acqcost], "#0.00%")
UserForm1.Salecost.Value = Format(ActiveSheet.[sc], "#0.00%")
'Output Yields
UserForm1.RentalincomeFY.Value = Format(ActiveSheet.[RI], "$#,###0")
UserForm1.RentalincomeLY.Value = Format(ActiveSheet.[LYRI], "$#,###0")
UserForm1.OutputInitialyield.Value = Format(ActiveSheet.[RI] / (ActiveSheet.[Bidprice] * (1 + ActiveSheet.[sc])), "#0.00%")
UserForm1.OutputExityield.Value = Format(ActiveSheet.[LYRI] / (1 + ActiveSheet.[sc]) / ActiveSheet.[Saleprice], "#0.00%")
'Investment
'General
UserForm1.Inflation.Value = Format(Worksheets("Input").[Inf], "#0.00%")
UserForm1.Bondrate.Value = Format(ActiveSheet.[brn], "#0.00%")
UserForm1.Returnmarket.Value = Format(ActiveSheet.[Rm], "#0.00%")
'Market Data
UserForm1.MYield.Value = Format(ActiveSheet.[MIY], "#0.00%")
UserForm1.Yieldadjustment.Value = Format(ActiveSheet.[Yieldadj], "#0.00%")
UserForm1.Exityield.Value = Format(ActiveSheet.[MEY], "#0.00%")
UserForm1.ERV.Value = Format(ActiveSheet.[ERV], "$#,###0")
UserForm1.nren.Value = ActiveSheet.[nren]
'Taxes
UserForm1.TRincome.Value = Format(Worksheets("Input").[t], "#0.00%")
UserForm1.TRcapitalgains.Value = Format(ActiveSheet.[tcg], "#0.00%")
UserForm1.TRdepreciation.Value = Format(ActiveSheet.[tdep], "#0.00%")
'Depreciation
UserForm1.Buildingratio.Value = Format(ActiveSheet.[BuiRatPro], "#0.00%")
UserForm1.Ndepreciation.Value = ActiveSheet.[ndep]
'Investment Horizon
UserForm1.Ninvestment.Value = ActiveSheet.[n]
'Rollover
UserForm1.Nroll.Value = ActiveSheet.[RR]
UserForm1.RRoptions.Value = ActiveSheet.[RRoptions]
'Capital Structure
'Capital Structure
UserForm1.LTV.Value = Format(ActiveSheet.[LTV], "#0.00%")
UserForm1.DCRMin.Value = ActiveSheet.[DCRMin]
UserForm1.Financing.Value = ActiveSheet.[Fin]
UserForm1.Namortization.Value = ActiveSheet.[nl]
UserForm1.Kdebt.Value = Format(ActiveSheet.[BasPoi], "#0.00%")
'Sensitivity Analysis
'Inputs
UserForm1.Vary.Value = ActiveSheet.[Columninput]
UserForm1.VarYstart.Value = ActiveSheet.[VarYstart]
UserForm1.Varyg.Value = ActiveSheet.[Varyg]
UserForm1.Show
End Sub
Liebe Gruesse,
Karl
Hallo Karl,
vielleicht postest du nochmal die gesamte Prozedur CommandButton1_Click(), wenn ich die Kinder im Bett habe, werd' ich nochmal schauen.
Gruß Matthias