|
本帖最后由 jasoncheung 于 2013-11-20 14:51 编辑
如图所示,我做了一个物业租赁合同管理系统,从“主界面”向“数据源”赋值。问题如下:
1.如何根据“基础物业信息”表达数据筛选A5到D5单元格的值,即假如我A5选“写字楼”,B5单元格如何根据A5单元格的条件设置下拉菜单选择“租赁楼层”,C5单元格根据B5单元格的条件设置下拉单元格选择租赁单元,E5单元格根据D5单元格的条件选择相应单元的“建筑面积”。
2. 写字楼的租赁单元是空的,当选写字楼时,C5单元格是空值,建筑面积直接为租赁楼层面积,商场有租赁单元,当选商场单元时,C5单元格不是空值,建筑面积为租赁单元面积,这两者如何综合考虑?
另外:当我点击“确定”输入一条信息后,B7和C7的租赁起始日和租赁终止日如何编写代码能自动跳转到下一个月的第一天和最后一天?
谢谢大家都支持,帮我完善这个系统,方便大家
密码是:851221
附注:源数据代码:
Dim s As Integer
Private Sub CommandButton1_Click()
Dim a As Integer
a = Sheets("数据源").[a65536].End(xlUp).Row
With Sheets("数据源")
.Unprotect Password:=""
.Range("B" & a + 1) = Range("A3").Value
.Range("C" & a + 1) = Range("A5").Value
.Range("D" & a + 1) = Range("B5").Value
.Range("E" & a + 1) = Range("C5").Value
.Range("F" & a + 1) = Range("D5").Value
.Range("G" & a + 1) = Range("E5").Value
.Range("H" & a + 1) = Range("F5").Value
.Range("I" & a + 1) = Range("A7").Value
.Range("J" & a + 1) = Range("B7").Value
.Range("K" & a + 1) = Range("C7").Value
.Range("L" & a + 1) = Range("D7").Value
.Range("M" & a + 1) = Range("E7").Value
.Range("N" & a + 1) = Range("F7").Value
.Range("O" & a + 1) = Range("A9").Value
.Range("P" & a + 1) = Range("B9").Value
.Range("Q" & a + 1) = Range("C9").Value
.Range("R" & a + 1) = Range("D9").Value
.Range("S" & a + 1) = Range("E9").Value
.Range("T" & a + 1) = Range("F9").Value
.Range("A" & a + 1) = a
.Range("A1:T" & a + 1).Locked = True
.Protect Password:=""
End With
End Sub
Private Sub CommandButton2_Click()
Sheets(1).Cells(3, 1).Formula = ""
Sheets(1).Cells(5, 1).Formula = ""
Sheets(1).Cells(5, 2).Formula = ""
Sheets(1).Cells(5, 3).Formula = ""
Sheets(1).Cells(5, 4).Formula = ""
Sheets(1).Cells(5, 5).Formula = ""
Sheets(1).Cells(7, 1).Formula = ""
Sheets(1).Cells(7, 2).Formula = ""
Sheets(1).Cells(7, 3).Formula = ""
Sheets(1).Cells(7, 4).Formula = ""
Sheets(1).Cells(7, 5).Formula = ""
Sheets(1).Cells(7, 6).Formula = ""
Sheets(1).Cells(9, 1).Formula = ""
Sheets(1).Cells(9, 2).Formula = ""
Sheets(1).Cells(9, 3).Formula = ""
Sheets(1).Cells(9, 4).Formula = ""
Sheets(1).Cells(9, 5).Formula = ""
Sheets(1).Cells(9, 6).Formula = ""
End Sub
附件请测试,由于电脑OS关系,将“零星物业”改成了“other”,你可以自己改回来,记得代码也要相应修改。
|
|