[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数階層の右クリックメニューの層上のコントロール取得』(デンデ)
よろしくお願いいたします。
http://www.excel.studio-kazu.jp/kw/20150802235219.html
こちらを参考にして
右クリックメニューに5階層の数字の入力とA〜Zの2階層のメニューを作りたいのですが
数字 12345
英数字 AA
日付選択 年月日
途中で行き詰まってしまいました
よろしくお願い致します
'標準モジュール
Option Explicit
Dim iti As String Dim ni As String Dim san As String Dim cnt As Integer
Sub Add1Menu() Dim myCommandBar As CommandBar Dim myCommandBarControl As CommandBarControl Dim i As Integer Dim j As Integer Dim k As Integer Set myCommandBar = Application.CommandBars("Cell") myCommandBar.Reset Set myCommandBarControl = myCommandBar.Controls.Add(Before:=1, Type:=msoControlPopup) With myCommandBarControl .Caption = "数" .OnAction = "'itiadd'" End With End Sub
Sub able1Menu() Dim mycmd As Object Dim flg As Boolean Dim Newb flg = False For Each mycmd In Application.CommandBars("Cell").Controls If mycmd.Caption = "数" Then flg = True End If Next mycmd If flg = True Then Application.CommandBars("Cell").Controls("数").Visible = True End If End Sub
Sub Del1Menu() Dim mycmd As Object Dim flg As Boolean Dim Newb flg = False For Each mycmd In Application.CommandBars("Cell").Controls If mycmd.Caption = "数" Then flg = True End If Next mycmd If flg = True Then Application.CommandBars("Cell").Controls("数").Visible = False End If End Sub
'Del1Menuは単に↓でもOK Sub Del1Menu2() Application.CommandBars("Cell").Reset End Sub
Function itiadd() Dim myCommandBarControl As CommandBarControl Dim mycmd As Object Dim i As Integer On Error Resume Next For Each mycmd In Application.CommandBars.ActionControl.Controls mycmd.Delete Next On Error GoTo 0 For i = 0 To 9 With Application.CommandBars.ActionControl.Controls.Add(msoControlPopup, , , , True) 'サブメニュー .Caption = i .OnAction = "'niadd'" End With Next i End Function
Function niadd() Dim myCommandBarControl As CommandBarControl Dim mycmd As Object Dim i As Integer iti = Application.CommandBars.ActionControl.Caption '★ここで「年」を変数格納 On Error Resume Next For Each mycmd In Application.CommandBars.ActionControl.Controls mycmd.Delete Next On Error GoTo 0 For i = 0 To 9 With Application.CommandBars.ActionControl.Controls.Add(msoControlPopup, , , , True) 'サブメニュー .Caption = i .OnAction = "'sanadd'" End With Next i End Function
Function sanadd() Dim myCommandBarControl As CommandBarControl Dim mycmd As Object Dim matu As Integer Dim i As Integer On Error Resume Next For Each mycmd In Application.CommandBars.ActionControl.Controls mycmd.Delete Next On Error GoTo 0 ni = Application.CommandBars.ActionControl.Caption '★ここで「月」を変数格納
For i = 0 To 9 With Application.CommandBars.ActionControl.Controls.Add(msoControlButton, , , , True) 'サブメニュー .Caption = i .OnAction = "'sanduke'" End With Next i End Function
Sub sanduke() Dim san As String san = Application.CommandBars.ActionControl.Caption ActiveCell.Value = (iti & ni & san) End Sub
Option Explicit
Dim tosi As String Dim tuki As String Dim hi As String Dim cnt As Integer
Sub AddMenu() Dim myCommandBar As CommandBar Dim myCommandBarControl As CommandBarControl Dim i As Integer Dim j As Integer Dim k As Integer Set myCommandBar = Application.CommandBars("Cell") myCommandBar.Reset Set myCommandBarControl = myCommandBar.Controls.Add(Before:=1, Type:=msoControlPopup) With myCommandBarControl .Caption = "日付選択" .OnAction = "'tosiadd'" End With End Sub
Sub ableMenu() Dim mycmd As Object Dim flg As Boolean Dim Newb flg = False For Each mycmd In Application.CommandBars("Cell").Controls If mycmd.Caption = "日付選択" Then flg = True End If Next mycmd If flg = True Then Application.CommandBars("Cell").Controls("日付選択").Visible = True End If End Sub
Sub DelMenu() Dim mycmd As Object Dim flg As Boolean Dim Newb flg = False For Each mycmd In Application.CommandBars("Cell").Controls If mycmd.Caption = "日付選択" Then flg = True End If Next mycmd If flg = True Then Application.CommandBars("Cell").Controls("日付選択").Visible = False End If End Sub
'DelMenuは単に↓でもOK Sub DelMenu2() Application.CommandBars("Cell").Reset End Sub
Function tosiadd() Dim myCommandBarControl As CommandBarControl Dim mycmd As Object Dim i As Integer On Error Resume Next For Each mycmd In Application.CommandBars.ActionControl.Controls mycmd.Delete Next On Error GoTo 0 For i = 1 To 10 With Application.CommandBars.ActionControl.Controls.Add(msoControlPopup, , , , True) 'サブメニュー .Caption = -5 + i + Year(Date) & "年" .OnAction = "'tukiadd'" End With Next i End Function
Function tukiadd() Dim myCommandBarControl As CommandBarControl Dim mycmd As Object Dim i As Integer tosi = Application.CommandBars.ActionControl.Caption '★ここで「年」を変数格納 On Error Resume Next For Each mycmd In Application.CommandBars.ActionControl.Controls mycmd.Delete Next On Error GoTo 0 For i = 1 To 12 With Application.CommandBars.ActionControl.Controls.Add(msoControlPopup, , , , True) 'サブメニュー .Caption = i & "月" .OnAction = "'hiadd'" End With Next i End Function
Function hiadd() Dim myCommandBarControl As CommandBarControl Dim mycmd As Object Dim matu As Integer Dim i As Integer On Error Resume Next For Each mycmd In Application.CommandBars.ActionControl.Controls mycmd.Delete Next On Error GoTo 0 tuki = Application.CommandBars.ActionControl.Caption '★ここで「月」を変数格納 matu = Day(DateAdd("d", -1, DateAdd("m", 1, DateValue(tosi & tuki & "1日")))) For i = 1 To matu With Application.CommandBars.ActionControl.Controls.Add(msoControlButton, , , , True) 'サブメニュー .Caption = i & "日" .OnAction = "'hiduke'" End With Next i End Function
Sub hiduke() Dim hi As String hi = Application.CommandBars.ActionControl.Caption ActiveCell.Value = DateValue(tosi & tuki & hi) End Sub
< 使用 Excel:Excel2003、使用 OS:Windows7 >
>右クリックメニューに5階層の数字の入力とA〜Zの2階層のメニュー
(マナ) 2016/08/21(日) 11:03
日付選択 年 月 日 2016年1月23日選択したなら 結果2016年1月23日
アクティブのセルに入力できますように
よろしくお願いいたします
(デンデ) 2016/08/21(日) 11:20
(マナ) 2016/08/21(日) 12:49
本題と違ってしまうが、階層化したメニューで入力するよりもユーザーフォームを作った方が操作性が 良さそうな気がする。
日付入力に関しても、カレンダーを表示してそこから選ぶ方が良いと思う。
(とおりすがり) 2016/08/21(日) 14:18
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.