-
Notifications
You must be signed in to change notification settings - Fork 0
/
ExcelTool.vba
46 lines (41 loc) · 1.22 KB
/
ExcelTool.vba
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Option Explicit
Sub AddCustomMenu()
Dim menu As Object
Dim flag As Boolean
For Each menu In MenuBars(xlWorksheet).Menus
If menu.Caption = "CustomMenu(&S)" Then
flag = False
Exit For
End If
flag = True
Next
If flag Then
MenuBars(xlWorksheet).Menus.Add Caption:="CustomMenu(&S)"
With MenuBars(xlWorksheet).Menus("CustomMenu")
Call .MenuItems.Add("SetFocusToHome(&H)", "SetFocusToHome")
Call .MenuItems.Add("SetFocusToA1(&A)", "SetFocusToA1")
End With
End If
End Sub
Sub SetFocusToHome()
Call setFocus(True)
MsgBox ("Foucs set to Home")
End Sub
Sub SetFocusToA1()
Call setFocus(False)
MsgBox ("Foucs set to A1")
End Sub
Sub setFocus(homeFlg As Boolean)
Dim book As Workbook
Set book = ActiveWorkbook
Dim sheetCount As Integer
For sheetCount = book.Sheets.Count To 1 Step -1
book.Sheets(sheetCount).Select
book.Sheets(sheetCount).Range("A1").Select
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
If homeFlg Then
SendKeys "^{HOME}", Wait:=True
End If
Next sheetCount
End Sub