Excelで、リストボックスの値によってVBAの記述を変えたい

質問

エクセルの表（データベース）を
並び替えるマクロを作っています。

コマンドボタンを押したらあらかじめ登録された
条件で並び替えされる、というところまではできました。
（ここでいう登録とは私が条件指定してVBAに記述したものです）

そこで質問なのですが、ユーザーフォームの上で
並び替えの条件（優先順位・降順、昇順）をユーザーに
指定してもらい、ボタンを押したら並び替えされる、
というものを作りたいのですが可能でしょうか。

なぜこんな面倒なことをするかというと、
複数のユーザーが扱うデータベースで、
並び替えの操作を簡略化したいのと、
間違った操作によるデータベースの破壊を
防止したいからです。

まずユーザーフォーム上のリストボックスの中に
優先順位をかける項目を入れておき、それを３つ
作りました。（並び替えの条件が３つまであるので）

次に各リストボックスの横にラジオボタンを置き、
降順、昇順を指定できるようにする。
（ここで、ラジオボタンのどちらかしか選択できないように
したいのですがその方法もわからないので教えてください）

そして、それぞれの指定された結果でVBAの記述（値）
を変えてコマンドボタンを押したら並び替えされる、
という風にしたいのです。（あくまでイメージですが）

Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, ・・・とつづく記述で

上の記述のRange("A2")と、xlDescendingの値を
ユーザーフォーム上で選んだ結果によって
変更したい、ということなんですが、こんなことって
出来るものでしょうか。もしくは同じ様な事を
可能な書き方とかがあれば教えて頂きたいです。

よろしくお願いします。

回答No.1

papayuka · ベストアンサー

OptionButton でどちらかしか選べない組み合わせを複数持ちたい場合は、それぞれの組み合わせをフレームの上に置くか、若しくはプロパティで GroupName を同じにするかで対応可能です。

昇順・降順については

　xlAscending = 1
　xlDescending = 2

ですので、

　i = 2
　Order1:= i

のように書くことが出来ます。

それよりも、、、
希望しているのはソートダイアログと寸分違わぬ物に思えます。
ソートダイアログを呼んじゃえば良いのでは？

例）UserFormにボタンを置いて

Private Sub CommandButton1_Click()
　　'一番左のシートのA1からデータがあるとして
　　Worksheets(1).Range("A1").CurrentRegion.Select
　　Application.Dialogs(xlDialogSort).Show
End Sub

お礼

ありがとうございます。

まったくおっしゃる通りで、
ダイアログを呼べばいいんですね。

できればもう一つ教えて頂きたいのですが、
ソートダイアログを呼ぶ方法において、

１．範囲の先頭行で、選択可能項目を
タイトル行のみとする

２．オプションを選択不可にする

この２つを実現したいのですができますか？

回答No.5

imogasi

#3の補足
例データ
a b d e
12 t 23 w
34 r 5 e
22 e 4 e
のようなデータを範囲指定して
Sub test01()
Application.Dialogs(xlDialogSort).Show 1, "b", 2, "a", 2, "d", 1, 2, 1
End Sub
Showの次からパラメーターは
１ー　列方向キー、２ー行方向
第１キーの見出し文字列、
１－昇順、２－降順
第２キーの見出し文字列
１－昇順、２－降順
第３キーの見出し文字列
１－昇順、２－降順
１－タイトル行、２－データ行
で
Sub test01()
Application.Dialogs(xlDialogSort).Show 1, "b", 2, "a", 2, "d", 1, 1
End Sub
のように指定できます
オプションボタン選択不可方法は、すみませんわかりません。

お礼

早速試してみます。

数々のご返答、本当にありがとうございました。

回答No.4

papayuka

#1です。

> １．範囲の先頭行で、選択可能項目をタイトル行のみとする

ご質問の意味が解りません良く掴めません。
headerを指定するって事？
Application.Dialogs(xlDialogSort).Show 1, , , , , , , xlYes

> ２．オプションを選択不可にする

組み込みダイアログなので難しいと思います。
APIを駆使してやれば出来るのかも知れませんが、、、

お礼

お礼が遅くなりすいませんでした。

自分には難しすぎました。

もう少し時間のあるときに勉強してみます。

どうもありがとうございました。

回答No.3

imogasi

#2です。
＃２の回答をしているとき、引数でわからないことがあったので、書かなかったが、＃１のご回答にも出ているDialogs(xlDialogSort)を使うのが、簡便で安定性があると思います。
Sub test01()
Range("a2:C10").Select
Application.Dialogs(xlDialogSort).Show 1, "年齢", 2, "氏名", 1
End Sub
のように、引数の順序は
orientation、key1、order1、key2、order2、key3、order3、header、custom、caseの順ですので
（http://www.geocities.jp/dgscs970/_homepage2/jiten/Jiten2.html
など参考に）
、これをプログラムで指定すると、
ダイアログ画面が出たときに、ダイアロウグ画面が既にそのように選択された状態で出すことができます。
すなわちOKだけクリックすればよい状態にできます。
これが進むと、マクロの記録のコードをそのまま書く形まえ行きます。
ご参考に。
データ例はA2:C５
住所 氏名 年齢
名古屋市 佐々木 54
千葉市 近藤 34
静岡市 木下 23
のような例で考えています。

お礼

ありがとうございます。

試してみたところ上手くいきました。

これに加えて、

１．範囲の先頭行で、選択可能項目を
タイトル行のみとする

２．オプションを選択不可にする

この２つを実現したいのですができますか？

回答No.2

imogasi

UserForm1に
Combobox1　 ソートキー選択用
OptionButton1　昇順選択用
OptionButton2　降順選択用
CommandButton1　ソート実行トリガ
のコントロールを各々１つはりつける。
UserForm1のコード部に
Dim cl
Dim asc
Private Sub ComboBox1_Click()
MsgBox UserForm1.ComboBox1.Text & "列でソート"
cl = UserForm1.ComboBox1.ListIndex + 1
End Sub

Private Sub CommandButton1_Click()
'MsgBox xlDescending 'Ascending
'MsgBox cl
Range("a3:H100").Sort key1:=Cells(1, cl), order1:=asc ',accending
End Sub

Private Sub OptionButton1_Click()
asc = 1
End Sub
Private Sub OptionButton2_Click()
asc = 2
End Sub
Private Sub UserForm_Initialize()
r = Range("iv2").End(xlToLeft).Column
'MsgBox r
For j = 1 To r
ComboBox1.AddItem Cells(2, j)
Next j
End Sub
のようなコードを作成。
これで実行して
下記例とした場合
コンボで「年齢」をクリック
オプションボタンで昇順のボタンをクリック
コマンドボタンをクリックでソートが実行され
A2:D7に
氏名 郵便番号 住所 年齢
山田 123-1111 千代田区 4
小島 234-2222 台東区 21
大橋 345-3333 名古屋市 23
野島 567-3333 岐阜市 34
上野 432-2222 千葉市 45
となった。
焦点がぼやけないように、体裁的なことは一切省略している。
コントロール利用の経験がなければ、難しいのではないですか。