Ich wollte fragen ob man eine Range (Bsp. Range("A1", n+3)) per Listbox in einem UserForm anzeigen kann, und wenn ja wie?
Dasselbe gilt uebrigens fuer Graphen.
Wenn ihr eine antwort habt schreibt mir bitte, vielen Dank im Voraus,
Gruss
Karl
Userform1.ListBox1.RowSource="A1:A" & n+3
EIB = Worksheets("EIB").Range("A1", ActiveCell.Offset(69, n + 3)).Address
Sub CommandButton1_Click()
LoadUserForm1
End Sub
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
Sub CommandButton1_Click()
LoadUserForm1
End Sub
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
EIB = Worksheets("EIB").Range("A1:" & ActiveCell.Offset(69, n + 3).Address(0, 0)).Address
IRS = Worksheets("IRS").Range("A1:" & ActiveCell.Offset(70, n + 3).Address(0, 0)).Address