Dec
28

Excel: VBA Examples-3

Get the max row cotain value of the sheet
Sub GetMaxRow()
n = Cells.Find(“*”, , , , 1, 2).Row
MsgBox n
End Sub
Get the max row of column A which is not empty
Sub GetMaxRowOfColumnA()
n = Range(“a65536″).End(xlUp).Row
Range(“B1″) = n
End Sub
Insert text frame in selected area
Sub InsertTextFrame()
For Each rag In Selection
n = n & rag.Value & Chr(10)
Next
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, ActiveCell.Left + ActiveCell.Width, ActiveCell.Top + ActiveCell.Height, 250#, 100).Select
Selection.Characters.Text = “issues:” & n
With Selection.Characters(Start:=1, Length:=3).Font
.Name = “bold”
.FontStyle = “regula”
.Size = 12
End With
End Sub
Input multi address comment
Sub InsertComments()
On Error Resume Next
Dim r As Range
If Selection.Cells.Count > 0 Then
For Each r In Selection
r.Comment.Delete
r.AddComment
r.Comment.Visible = False
r.Comment.Text Text:=”cell” & r.Address & ” of ” & Selection.Address
Next
End If
End Sub
Input comments automatically
Sub InsertCom

Dec
27

Excel: VBA Examples-2

Change the button name and loop around 3 macros.(Code of the button)
Option Explicit
Private Sub CommandButton1_Click()
With CommandButton1
If .Caption = “macro1″ Then
Call macro1
.Caption = “macro2″
Exit Sub
End If
If .Caption = “macro2″ Then
Call macro2
.Caption = “macro3″
Exit Sub
End If
If .Caption = “macro3″ Then
Call macro3
.Caption = “macro1″
Exit Sub
End If
End With
End Sub
Show/Hide the button according value in A1
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range(“A1″) > 2 Then
CommandButton1.Visible = 1
Else
CommandButton1.Visible = 0
End If
End Sub
Private Sub CommandButton1_Click()
arrangewindow
End Sub
Return the name of the button in current cell
Private Sub CommandButton1_Click()
ActiveCell = CommandButton1.Caption
End Sub
Set the button text to the value of current cell
Private Sub CommandButton1_Click()
CommandButton1.Caption = ActiveCell
End Sub
Print page by odd or even page
Sub pintoddevenpagesseparately()
Dim i%, Ps%
Ps = ExecuteExcel4Macro(“GET.DOCUMENT(50)”) ‘Total pages number
MsgBox “start print odd pages,press OK to start.”
For i = 1 To Ps Step 2
ActiveSheet.PrintOut from:=i, To:=i
Next i
MsgBox “Print even pages,press OK to start.”
For i = 2 To Ps Step 2
ActiveSheet.PrintOut from:=i, To:=i
Next i
End Sub
Print worksheet first page automatically
Sub printfirstpageofsheetsautomatically()
Dim sh As Integer
Dim x, y, sy, syz
x = InputBox(“Please input start sheet name:”)
sy = InputBox(“Please input end sheet name:”)
y = Sheets(x).Index
syz = Sheets(sy).Index
For sh = y To syz
Sheets(sh).Select
Sheets(sh).PrintOut from:=1, To:=1
Next sh
End Sub
Find the text in cloumn A and ipnt page break
Sub insertpagebreakloop()
‘ Selection = Workbooks(“temp sheet”).Sheets(“sheet 2″).Range(“A1″) ‘调用指定地址内容

Dim i As Long
Dim times As Long
times = Application.WorksheetFunction.CountIf(Sheet1.Range(“a:a”), “pagebreak”)
‘times Means loop time.Set value to time(1<=times<=2147483647)
For i = 1 To times
Call insertpagebreak
Next i
End Sub

Sub insertpagebreak()
Cells.Find(What:="pagebreak", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
End Sub

Sub cancelpagebreak()
Cells.Select
ActiveSheet.ResetAllPageBreaks
End Sub

Change the picture in column B to the size of the cell
Sub ChangePictureInColumnBToSizeOfTheCell()
Dim Pic As Picture, i&
i = [A65536].End(xlUp).Row
For Each Pic In Sheet1.Pictures
 If Not Application.Intersect(Pic.TopLeftCell, Range(“B1:B” & i)) Is Nothing Then
  Pic.Top = Pic.TopLeftCell.Top
  Pic.Left = Pic.TopLeftCell.Left
  Pic.Height = Pic.TopLeftCell.Height
  Pic.Width = Pic.TopLeftCell.Width
 End If
Next
End Sub
Return the row of the cursor
Sub returntherowofthecursor()
  x = ActiveCell.Row
   Range(“A1″) = x
End Sub
Return the selected cells count in A1
Sub GetSelectedCellsCount()
  [A1] = Selection.Count
End Sub
Get the sheets count in current workbook
Sub GetSheetsCount()
  t = Application.Sheets.Count
  MsgBox t
End Sub
Get the rows and columns count of the cursor selected area
Sub GetSelectedColunsAndRows()
  x = Selection.Rows.Count
  y = Selection.Columns.Count
  Range(“A1″) = x
  Range(“A2″) = y
End Sub

Dec
26

Excel: VBA Examples-1

Open all hidden sheets.
Sub openallhiddensheets()
Dim i As Integer
For i = 1 To Sheets.Count
  Sheets(i).Visible = True
Next i
End Sub
loop example
sub loopexample()
AAA = Range(“C2″)
Dim i As Long
Dim times As Long
times = AAA
‘times – means the loop times, set value to it before for loop(it can’t less than and more than 2147483647)
For i = 1 To times
 Call subcall1′call customized suburoutine
 If Range(“Complete”) = “Complete” Then Exit For ‘If range Complete’s value equal Complete then exit.
Next i
End Sub
Call stop record
sub stoprecord()
 Application.CommandBars(“Stop Recording”).Visible = True
End Sub
Advanced filter and copy the data from sheet 1 to sheet 2
Sub advancedfilterandcopytosheet2()
Sheets(“Sheet2″).Range(“A1:E65536″) = “” ‘Clear the contnet in Sheet2 column A:D
Range(“A1:E65536″).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet2.Range( _
“A1″), Unique:=True
Sheet2.Columns(“A:E”).Sort Key1:=Sheet2.Range(“A2″), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin
End Sub
Execute macro when double click a cell
‘Handle the event before doubel click the sheet
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Range(“$A$1″) = “close” Then Exit Sub
Select Case Target.Address
 Case “$A$4″
 Call macro1
 Cancel = True
 Case “$B$4″
 Call macro2
 Cancel = True
 Case “$C$4″
 Call macro3
 Cancel = True
 Case “$E$4″
 Call macro4
 Cancel = True
End Select
End Sub
Run macro when double specified area
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Range(“$A$1″) = “Exit” Then Exit Sub
  If Not Application.Intersect(Target, Range(“A4:A9″, “C4:C9″)) Is Nothing Then Call OpenHiddenTable
End Sub
Macro to run when into cell
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
‘Using cell instead button to call macro
If Range(“$A$1″) = “Exit” Then Exit Sub
Select Case Target.Address
Case “$A$5″ ‘cell address (Target.Address),or cell name(Target.Name)
Call Macro1
Case “$B$5″
Call Macro2
Case “$C$5″
Call Macro3
End Select
End Sub
run macro when select specified cell
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range(“$A$1″) = “Exit” Then Exit Sub
If Not Application.Intersect(Target, Range(“A4:A9″,”C4:C9″)) Is Nothing Then Call OpenHiddenTable
End Sub
Run macro sequently when click the button. Add a button at first then handle button event
Private Sub CommandButton1_Click()
Static RunMacro As Integer
Select Case RunMacro
Case 0
  Macro1
   RunMacro = 1
Case 1
  Macro2
  RunMacro = 2
Case 2
  Macro3
  RunMacro = 0
End Select
End Sub
‘Change button name in two macros(code of the button).
‘在两个宏中依次循环执行一个并相应修改按钮名称(控件按钮代码)
Private Sub CommandButton1_Click()
With CommandButton1
If .Caption = “ProtectSheet” Then
    Call ProtectSheet
    .Caption = “CancelProtectSheet”
    Exit Sub
End If
If .Caption = “CanceProtectSheet” Then
    Call CancelProtectSheet
    .Caption = “ProtectSheet”
    Exit Sub
End If
End With
End Sub

Dec
24

Word: Fast way to cut and paste.

In word, normally we will cut/paste by selec the words then select “cut”, move the cursor to the right place ane select “paste”.
There is another faster way worth to try.
1.Select the words need cut.
2.Press Ctrl.
3.Go to the right place and click the right key of the mouse.

Dec
24

Word: Fast way to change the line space.

Select the paragrahp you want to change line space. Press “Ctrl+1″ to change to sing line space. Press “Ctrl+2″ to change to double line space. Press “Ctrl+5″ to change to 1.5 line space.

Dec
24

Word: Fast input.

After input some words in word, press Alt+Enter will copy the last input. Ctl+Y can do the same thing.

Dec
24

Word: Tip to change the word between upper case and lower case.

In a word document, you can change the word between upper case to loser case by Shift+F3.
The word will change between full upper case, first letter upper case and full lower case when you click F3.

Dec
24

Word:Tips to maxmize or restore the window.

You can adjust the window except the maxmize and restore button at the top right corner of the window. First, you can do this by double click the titlbe bar. Secnod, you can click the cross area of the scroll bar and the vertical bar.

Dec
24

Excel: Input in an area and don’t need swith between cells by mouse.

You can do this by following. Select an area by mouse. Then press Tab to move the cell forward and move the cell backward by Shift+Tab. By this way, you can input datas without mouse in order to improve productivity.
By this way, if you are at the cell of the selected area, press Tab can move to the first cell of the area. Press enter can input data by column.

Dec
24

Word: How to find last modification?

I belive you must have this experience In some case you have a long word document with tens of pages and you want to go to the last modification to start work when you open the document.
There is a short way to do this, Press “Shift+F5″ can go the last modification place. It’s very convenient. Wrod can record the last 3 modification placess when you save the document.

Older posts «