|
UserFormで作るとこんな様
コマンドボタンとトグルボタンの2通り書いて置きます
Option Explicit
'ソート範囲
Private rngScope As Range
'CommandButtonの場合の状態記憶変数
Private blnSwitch As Boolean
Private Sub CommandButton1_Click()
DataSort blnSwitch
blnSwitch = Not blnSwitch
End Sub
Private Sub ToggleButton1_Click()
DataSort ToggleButton1.Value
End Sub
Private Sub UserForm_Initialize()
'ソート範囲を取得
Set rngScope = ActiveSheet.Cells(1, "A").CurrentRegion
'CommandButtonの場合の表示
blnSwitch = Not blnSwitch
With CommandButton1
.Caption = "Ascending"
End With
'ToggleButton1の場合の表示
With ToggleButton1
.Caption = "Ascending"
End With
End Sub
Private Sub UserForm_Terminate()
'ソート範囲を破棄
Set rngScope = Nothing
End Sub
Private Sub DataSort(blnOrder As Boolean)
Dim lngOrder As Long
'CommandButtonの場合の表示
With CommandButton1
If blnOrder Then
.Caption = "Descending"
Else
.Caption = "Ascending"
End If
End With
lngOrder = 2 + CLng(blnOrder)
With rngScope
.Sort Key1:=.Cells(1, 1), Order1:=lngOrder, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlStroke
End With
End Sub
|
|