|
Dim Rnumber As Integer
Private Sub CommandButton1_Click()
Dim i As Integer, h As Integer, c As Integer, n As Integer
For i = 1 To 17
Me.Controls("textbox" & i).Enabled = True
Me.Controls("textbox" & i).Text = "" '置空
Next i
For h = 1 To 4
Me.Controls("ComboBox" & h).Enabled = True
Me.Controls("ComboBox" & h).Text = "" '置空
Next h
For c = 3 To 6
Me.Controls("CommandButton" & c).Enabled = True
Next c
Me.CommandButton1.Enabled = False
Me.TextBox17.Text = Sheet5.Range("A65536").End(xlUp).Offset(1 - 1, 0) + 1
End Sub
Private Sub CommandButton2_Click() '关闭
Unload Me
End Sub
Private Sub CommandButton3_Click() '保存
Dim i As Integer, h As Integer, c As Integer, n As Integer
If TextBox8.Text = "" Or TextBox15.Text = "" Then
If TextBox8 = "" Then
MsgBox "申请人姓名不能为空!", 48, "提示"
TextBox8.SetFocus
ElseIf TextBox15 = "" Then
MsgBox "采购金额不能为空!", 48, "提示"
TextBox17.SetFocus
End If
Else
With Sheet5.Range("A65536").End(xlUp)
.Offset(1, 0) = TextBox17.Text '序号
.Offset(1, 1) = TextBox8.Text '单号
.Offset(1, 2) = ComboBox1.Text '区域
.Offset(1, 3) = TextBox9.Text '姓名
.Offset(1, 13) = TextBox10.Text '店铺地址
.Offset(1, 14) = TextBox11.Text '收货地址
.Offset(1, 15) = TextBox12.Text '发货方式
.Offset(1, 4) = TextBox13.Text '月收入
.Offset(1, 8) = TextBox14.Text '数量
.Offset(1, 7) = TextBox15.Text '采购价
.Offset(1, 9) = TextBox16.Text '销售价
.Offset(1, 5) = ComboBox2.Text '属性
.Offset(1, 6) = ComboBox3.Text '类别
.Offset(1, 11) = ComboBox4.Text '供应商
.Offset(1, 20) = ComboBox5.Text '操作员
.Offset(1, 16) = ComboBox6.Text '达标否
.Offset(1, 18) = ComboBox7.Text '照片回传否
.Offset(1, 10) = DTPicker1.Text '下单日期
.Offset(1, 11) = DTPicker1.Text '发货日期
End With
If MsgBox("选定的用户【" & TextBox8.Text & "】信息已保存!" & Chr(13) & Chr(13) & "是否继续设置用户信息?" & Chr(13) & Chr(13) & "按【是】继续,按【否】退出。", 32 + vbYesNo, "提示") = vbNo Then
End
End If
CommandButton1_Click
End If
End Sub
Private Sub CommandButton4_Click() '修改
Dim n As Integer
If TextBox8.Text <> "" Then
With Sheet5
For n = 4 To .Range("a65536").End(xlUp).Row
If TextBox8.Text = .Range("b" & n) Then Exit For
Next n
.Range("a" & n) = TextBox17.Text
.Range("b" & n) = TextBox8.Text
.Range("c" & n) = ComboBox1.Text
.Range("d" & n) = TextBox9.Text
.Range("n" & n) = TextBox10.Text
.Range("o" & n) = TextBox11.Text
.Range("p" & n) = TextBox12.Text
.Range("e" & n) = TextBox13.Text
.Range("i" & n) = TextBox14.Text
.Range("h" & n) = TextBox15.Text
.Range("j" & n) = TextBox16.Text
.Range("f" & n) = ComboBox2.Text
.Range("g" & n) = ComboBox3.Text
.Range("l" & n) = ComboBox4.Text
.Range("u" & n) = ComboBox5.Text
.Range("q" & n) = ComboBox6.Text
.Range("s" & n) = ComboBox7.Text
.Range("k" & n) = DTPicker1.Text
.Range("m" & n) = DTPicker2.Text
End With
End If
If MsgBox("选定的用户【" & TextBox8.Text & "】信息已修改!" & Chr(13) & Chr(13) & "是否继续设置用户信息?" & Chr(13) & Chr(13) & "按【是】继续,按【否】退出。", 32 + vbYesNo, "提示") = vbNo Then
End
End If
CommandButton1_Click
End Sub
Private Sub CommandButton5_Click() '查询
Dim n As Integer
If TextBox8.Text <> "" Then
For n = 4 To Sheet5.Range("a65536").End(xlUp).Row
If TextBox8.Text = Sheet5.Range("b" & n) Then Exit For
Next n
If n <= Sheet5.Range("a65536").End(xlUp).Row Then TextToRang n
If TextBox8.Text <> Sheet5.Range("b" & n) Then MsgBox "找不到【" & TextBox8.Text & "】的用户信息,请核查!"
End If
Me.CommandButton1.Enabled = True
End Sub
Private Sub CommandButton6_Click() '删除
Dim n As Integer, i As Integer
For n = 4 To Sheet5.Range("a65536").End(xlUp).Row
If TextBox8.Text = Sheet5.Range("b" & n) Then Exit For
Next n
If MsgBox("是否真的删除数据", vbYesNo) = vbYes Then Sheet5.Rows(n).Delete
For i = n To Sheet5.Range("a65536").End(xlUp).Row
Sheet5.Cells(i, 1) = i - 1
Next i
If MsgBox("选定的用户【" & TextBox8.Text & "】信息已删除!" & Chr(13) & Chr(13) & "是否继续设置用户信息?" & Chr(13) & Chr(13) & "按【是】继续,按【否】退出。", 32 + vbYesNo, "提示") = vbNo Then
End
End If
CommandButton1_Click
Me.CommandButton1.Enabled = True
End Sub
Private Sub CommandButton7_Click() '上一条
If Rnumber > 2 Then
Rnumber = Rnumber - 1
TextToRang Rnumber
Else
Rnumber = Sheet5.Range("A65536").End(xlUp).Row
End If
End Sub
Private Sub CommandButton8_Click() '下一条
If Rnumber < Sheet5.Range("A65536").End(xlUp).Row Then
Rnumber = Rnumber + 1
TextToRang Rnumber
Else
Rnumber = 2
End If
End Sub
Private Sub DTPicker1Value()
End With
End Sub
Private Sub UserForm_Initialize()
Dim i As Integer, h As Integer, c As Integer, n As Integer
For i = 1 To 17
Me.Controls("textbox" & i).Enabled = False '文本框不可控制
Next i
For h = 1 To 4
Me.Controls("ComboBox" & h).Enabled = False
Next h
For c = 3 To 6
Me.Controls("CommandButton" & c).Enabled = False
Next c
Rnumber = 2
TextToRang Rnumber
Me.DTPicker1.Text.Value
Me.DTPicker2.Text.Value
Me.ComboBox1.List = Sheet3.Range("quyu").Value
Me.ComboBox2.List = Sheet3.Range("sx").Value
Me.ComboBox3.List = Sheet3.Range("lb").Value
Me.ComboBox4.List = Sheet3.Range("gys").Value
Me.ComboBox5.List = Sheet3.Range("czy").Value
Me.ComboBox6.List = Sheet3.Range("dbf").Value
Me.ComboBox7.List = Sheet3.Range("zp").Value
End Sub
Function TextToRang(RW As Integer)
With Sheet5
TextBox17.Text = .Range("A" & RW)
TextBox8.Text = .Range("B" & RW)
ComboBox1.Text = .Range("C" & RW)
TextBox9.Text = .Range("D" & RW)
TextBox10.Text = .Range("N" & RW)
TextBox11.Text = .Range("O" & RW)
TextBox12.Text = .Range("P" & RW)
TextBox13.Text = .Range("E" & RW)
TextBox14.Text = .Range("I" & RW)
TextBox15.Text = .Range("H" & RW)
TextBox16.Text = .Range("J" & RW)
ComboBox2.Text = .Range("F" & RW)
ComboBox3.Text = .Range("G" & RW)
ComboBox4.Text = .Range("L" & RW)
ComboBox5.Text = .Range("U" & RW)
ComboBox6.Text = .Range("Q" & RW)
ComboBox7.Text = .Range("S" & RW)
DTPicker1.Text = .Range("K" & RW)
DTPicker2.Text = .Range("M" & RW)
End With
End Function
|
|