文書番号: 402467
最終更新日: 1997/10/20
Sub ForEach1() Dim mySheet As Object For Each mySheet In ActiveWorkbook.Sheets MsgBox mySheet.Name Next mySheet End Sub
Sub ForEach2() Dim myBook As Object Dim i As Integer i = 1 For Each myBook In Workbooks Worksheets("sheet1").Cells(i, 1).Value = myBook.Name i = i + 1 Next myBook End Sub
Sub ForEach3() Dim myRange As Object For Each myRange In Worksheets("sheet1").Range("c1:c10") 'セルの値が 0 のときセルの色を赤にします If myRange.Value = 0 Then myRange.Interior.Color = RGB(255, 0, 0) End If Next myRange End Sub
Sub ForEach4() Dim myObj As Object If TypeName(ActiveSheet) = "Module" Then MsgBox "モジュールシートでは、実行できません" Else For Each myObj In ActiveSheet.DrawingObjects MsgBox myObj.Name Next myObj End If End Sub
Sub ForEach5() Dim myBook As Object Dim mySheet As Object For Each myBook In Workbooks For Each mySheet In myBook.Sheets MsgBox myBook.Name & " : " & mySheet.Name Next mySheet Next myBook End Sub
Sub ForEach6() Dim myGraph As Object Sheets("sheet2").Select For Each myGraph In ActiveSheet.ChartObjects With myGraph .Width = 305.4 .Height = 222.6 .Activate End With With ActiveChart .PlotArea.Width = 251 .PlotArea.Height = 202 .Axes(xlValue).MaximumScale = 500 End With Next myGraph End Sub
Sub Dialog_Show() Dim dlgoptbt As Object Set dlgoptbt = DialogSheets("Dialog1").OptionButtons DialogSheets("Dialog1").Show MsgBox Opt(dlgoptbt) End Sub Function Opt(DlgOptBtName As Object) Dim OptBt As Object For Each OptBt In DlgOptBtName If OptBt.Value = xlOn Then Opt = OptBt.Name 'インデックス番号を返したい場合は次のように記述します 'Opt = OptBt.Index Exit For End If Next OptBt End Function
Option Base 1 Public Tb Public i Sub OnlySelectSheet_ToolbarsEnabled() Worksheets("sheet1").OnSheetActivate = "TbarVisible_False" Worksheets("sheet1").OnSheetDeactivate = "TbarVisible_True" End Sub '表示されているツールバーを消す Sub TbarVisible_False() ReDim Tb(Application.Toolbars.Count) i = 0 For Each tbar In Application.Toolbars If tbar.Visible = True Then i = i + 1 Tb(i) = tbar.Name tbar.Visible = False End If Next tbar End Sub 'ツールバーを復元する Sub TbarVisible_True() For j = 1 To i Application.Toolbars(Tb(j)).Visible = True Next j End Sub '元の設定に戻す際に実行するマクロ Sub OnlySelectSheet_ToolbarsReset() Worksheets("sheet1").OnSheetActivate = "" Worksheets("sheet1").OnSheetDeactivate = "" End Sub
Keywords: KBHOWTO KB402467
Technology: kbExcel500 kbExcelSearch kbExcelWinSearch