0824 note

Private Sub CommandButton1_Click()
    Dim id As String
   
    id = TextBox1.Value
    Range("a1").AutoFilter Field:=1, Criteria1:=id
    Range("a2").Select
   
End Sub

Private Sub CommandButton2_Click()
    TextBox1.Value = ""
    Range("A1").AutoFilter
   
End Sub

Private Sub CommandButton3_Click()
     Dim id1 As String
     Dim id2 As String
   
    id1 = TextBox2.Value
    id2 = TextBox3.Value
    Range("a1").AutoFilter Field:=1, Criteria1:=">=" & id1, Operator:=xlAnd, Criteria2:="<=" & id2
    Range("a2").Select
   
End Sub

Private Sub CommandButton4_Click()
    Dim name As String
   
    name = TextBox4.Value
    Range("a1").AutoFilter Field:=2, Criteria1:="=*" & name & "*"
   
End Sub

Private Sub CommandButton5_Click()
    Dim school As String
   
    school = TextBox5.Value
    Range("a1").AutoFilter Field:=5, Criteria1:=school
    Range("a2").Select
End Sub


Private Sub CommandButton6_Click()
    With ActiveSheet.PageSetup
        .LeftHeader = "職訓名單"
        .CenterHeader = "個人基本資料"
        .RightHeader = "列印日" & "&D"
    End With
   
    With ActiveSheet.PageSetup
        .LeftFooter = ""
        .CenterFooter = "&P/N"
        .RightFooter = ""
    End With
   
    UserForm2.Hide
    Range("A1").CurrentRegion.PrintPreview
   
End Sub

0821 note

最後一筆資料
Private Sub CommandButton1_Click()
    Sheets(4).Cells(Rows.Count, 1).End(xlUp).Select
End Sub
最後一個欄位
Private Sub CommandButton2_Click()
    Dim s As String
    Dim kath As String
    Dim h As Variant
 
   ' s = Sheets(4).Cells(Rows.Count, 1).End(xlUp).End(xlUp).Value
    'msgbox s
    Sheets(4).Cells(Rows.Count, 1).End(xlUp).End(xlUp).Select
    kpath = Selection.Address
    'msgbox kpath
    h = Split(kpath, "$")
    'MsgBox h(2)
    Sheets(4).Cells(h(2), Columns.Count).End(xlToLeft).Select
 
   'Sheets(4).Cells(7, Columns.Count).End(xlToLeft).Select
End Sub

0819 note

Multipage1:多重頁面
Mutipage1.value=0  =>第一頁面
Mutipage1.value=1  =>第二頁面

tabstrip1:索引標籤
1.Tabstrip1.value=0  ->
   Tabstrip1.value=1  ->
2.Tabstrip1.tab(0).caption=string
   Tabstrip1.tab(1).caption=string

Private Sub TabStrip1_Change()
    Dim choose As Integer
 
    choose = TabStrip1.SelectedItem.Index    '標籤索引值
    If choose = 0 Then     ' 利用索引值控制多重頁面
        MultiPage1.Value = 0
    Else
        MultiPage1.Value = 1
    End If
End Sub

Private Sub UserForm_Initialize()
    TabStrip1.Tabs(0).Caption = "輸入資料"
    TabStrip1.Tabs(0).Caption = "查詢資料"
    TabStrip1.Value = 0     '停在第一頁面
End Sub
=================================
ListView要新增工具控制項

Private Sub UserForm_Initialize()
With ListView1
    .View = lvwReport
    .LabelEdit = lvwManual
    .HideSelection = False
    .AllowColumnReorder = True
    .FullRowSelect = True
    .Gridlines = True
    .ColumnHeaders.Add , "編號", "編號"
    .ColumnHeaders.Add , "姓名", "姓名"
    .ColumnHeaders.Add , "生日", "生日"
    .ColumnHeaders.Add , "血型", "血型"
    .ColumnHeaders.Add , "學歷", "學歷"
End With
 
    Dim i As Integer
    Dim id As Variant
    Dim name As Variant
    Dim birth As Variant
    Dim blood As Variant
    Dim school As Variant
    id = Array("p1001", "p1002")
    name = Array("陳曉明", "李曉東")
    birth = Array("65/02/03", "68/12/31")
    blood = Array("A", "O")
    school = Array("大學", "研究所")
 
    For i = 0 To UBound(id)
        With ListView1.ListItems.Add
            .Text = id(i)
            .SubItems(1) = name(i)
            .SubItems(2) = birth(i)
            .SubItems(3) = blood(i)
            .SubItems(4) = school(i)
        End With
    Next i
 
 
End Sub

Private Sub CommandButton1_Click()
    Dim data As String
    data = ListView1.SelectedItem.Index & "：" & ListView1.SelectedItem.Text & "：" & ListView1.SelectedItem.ListSubItems(1).Text
    MsgBox data
End Sub

=======================================
調色盤

0817 note

form optionbutton
 Private Sub CommandButton1_Click()
    Dim s As String
 
    If OptionButton1.Value = True Then
        s = OptionButton1.Caption
    ElseIf OptionButton2.Value = True Then
        s = OptionButton2.Caption
    ElseIf OptionButton3.Value = True Then
        s = OptionButton3.Caption
    End If
'    Dim i As Integer
'
'    For i = 1 To 3
'        If Me.Controls("OPTIONbutton" & i).Value = True Then
'            s = Me.Controls("OPTIONbutton" & i).Caption
'            Exit For
'        End If
'    Next i
 
    MsgBox s
End Sub

Private Sub CommandButton2_Click()
    OptionButton1.Value = False
    OptionButton2.Value = False
    OptionButton3.Value = False
End Sub

Private Sub UserForm_Click()

End Sub

=============================================
form chectbox
1.checkbox1.value=boolean
   true
  false
2.checkbox1.caption="string"
 顯示值
3checkbox1.Autosize=boolean
自動依文字調整大小
=====================

Private Sub CommandButton1_Click()
    Dim plist As Integer
    Dim i As Integer
    Dim result As String
 
    plist = ListBox1.ListCount
    For i = 1 To plist
        If ListBox1.Selected(i - 1) = True Then
            result = result + ListBox1.List(i - 1)
        End If
    Next i
 
    MsgBox result
End Sub

Private Sub CommandButton2_Click()
    Dim plist As Integer
    Dim i As Integer
 
    plist = ListBox1.ListCount
    For i = 1 To plist
        ListBox1.Selected(i - 1) = False
    Next i
End Sub

Private Sub UserForm_Activate()
    ListBox1.AddItem "百合花"
    ListBox1.AddItem "玫瑰花"
    ListBox1.AddItem "金針花"
    ListBox1.AddItem "杜鵑花"
    ListBox1.AddItem "水仙花"
    ListBox1.MultiSelect = fmMultiSelectMulti
End Sub
======================
Private Sub ScrollBar1_Change()
    TextBox1.Value = ScrollBar1.Value
End Sub



Private Sub UserForm_Click()
    TextBox1.Value = 0
End Sub
==================================
Private Sub SpinButton1_Change()
    TextBox1.Value = SpinButton1.Value
End Sub



Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
 TextBox1.Value = 0
End Sub
====================

0814 note

combobox控制項 1.加入項目     元件.addItem 字串值 2.項目索引控制項 3.元件.clear     =>清除combobox項目 Dim s(4) As String Dim lencount As Integer  '總長度 Dim p As Integer        '指標
固定位置換成不固定位置 絕對位址與相對位址 Dim strpath as string strpath=ActiveWorkbook.path   擷取目前檔案路徑 Right(str,1)  從右邊取 一個 自 元 Left(str,1)   從左邊取 一個字元 Len(str)   計算字串長度 split(str,"符號") 遇符號時將字串分割，回傳陣列
====================================== UserForm基礎 un