ALL SHAPE STYLES

Name	Value
msoShape16pointStar	94
msoShape24pointStar	95
msoShape32pointStar	96
msoShape4pointStar	91
msoShape5pointStar	92
msoShape8pointStar	93
msoShapeActionButtonBackorPrevious	129
msoShapeActionButtonBeginning	131
msoShapeActionButtonCustom	125
msoShapeActionButtonDocument	134
msoShapeActionButtonEnd	132
msoShapeActionButtonForwardorNext	130
msoShapeActionButtonHelp	127
msoShapeActionButtonHome	126
msoShapeActionButtonInformation	128
msoShapeActionButtonMovie	136
msoShapeActionButtonReturn	133
msoShapeActionButtonSound	135
msoShapeArc	25
msoShapeBalloon	137
msoShapeBentArrow	41
msoShapeBentUpArrow	44
msoShapeBevel	15
msoShapeBlockArc	20
msoShapeCan	13
msoShapeChevron	52
msoShapeCircularArrow	60
msoShapeCloudCallout	108
msoShapeCross	11
msoShapeCube	14
msoShapeCurvedDownArrow	48
msoShapeCurvedDownRibbon	100
msoShapeCurvedLeftArrow	46
msoShapeCurvedRightArrow	45
msoShapeCurvedUpArrow	47
msoShapeCurvedUpRibbon	99
msoShapeDiamond	4
msoShapeDonut	18
msoShapeDoubleBrace	27
msoShapeDoubleBracket	26
msoShapeDoubleWave	104
msoShapeDownArrow	36
msoShapeDownArrowCallout	56
msoShapeDownRibbon	98
msoShapeExplosion1	89
msoShapeExplosion2	90
msoShapeFlowchartAlternateProcess	62
msoShapeFlowchartCard	75
msoShapeFlowchartCollate	79
msoShapeFlowchartConnector	73
msoShapeFlowchartData	64
msoShapeFlowchartDecision	63
msoShapeFlowchartDelay	84
msoShapeFlowchartDirectAccessStorage	87
msoShapeFlowchartDisplay	88
msoShapeFlowchartDocument	67
msoShapeFlowchartExtract	81
msoShapeFlowchartInternalStorage	66
msoShapeFlowchartMagneticDisk	86
msoShapeFlowchartManualInput	71
msoShapeFlowchartManualOperation	72
msoShapeFlowchartMerge	82
msoShapeFlowchartMultidocument	68
msoShapeFlowchartOffpageConnector	74
msoShapeFlowchartOr	78
msoShapeFlowchartPredefinedProcess	65
msoShapeFlowchartPreparation	70
msoShapeFlowchartProcess	61
msoShapeFlowchartPunchedTape	76
msoShapeFlowchartSequentialAccessStorage	85
msoShapeFlowchartSort	80
msoShapeFlowchartStoredData	83
msoShapeFlowchartSummingJunction	77
msoShapeFlowchartTerminator	69
msoShapeFoldedCorner	16
msoShapeHeart	21
msoShapeHexagon	10
msoShapeHorizontalScroll	102
msoShapeIsoscelesTriangle	7
msoShapeLeftArrow	34
msoShapeLeftArrowCallout	54
msoShapeLeftBrace	31
msoShapeLeftBracket	29
msoShapeLeftRightArrow	37
msoShapeLeftRightArrowCallout	57
msoShapeLeftRightUpArrow	40
msoShapeLeftUpArrow	43
msoShapeLightningBolt	22
msoShapeLineCallout1	109
msoShapeLineCallout1AccentBar	113
msoShapeLineCallout1BorderandAccentBar	121
msoShapeLineCallout1NoBorder	117
msoShapeLineCallout2	110
msoShapeLineCallout2AccentBar	114
msoShapeLineCallout2BorderandAccentBar	122
msoShapeLineCallout2NoBorder	118
msoShapeLineCallout3	111
msoShapeLineCallout3AccentBar	115
msoShapeLineCallout3BorderandAccentBar	123
msoShapeLineCallout3NoBorder	119
msoShapeLineCallout4	112
msoShapeLineCallout4AccentBar	116
msoShapeLineCallout4BorderandAccentBar	124
msoShapeLineCallout4NoBorder	120
msoShapeMixed	-2
msoShapeMoon	24
msoShapeNoSymbol	19
msoShapeNotchedRightArrow	50
msoShapeNotPrimitive	138
msoShapeOctagon	6
msoShapeOval	9
msoShapeOvalCallout	107
msoShapeParallelogram	2
msoShapePentagon	51
msoShapePlaque	28
msoShapeQuadArrow	39
msoShapeQuadArrowCallout	59
msoShapeRectangle	1
msoShapeRectangularCallout	105
msoShapeRegularPentagon	12
msoShapeRightArrow	33
msoShapeRightArrowCallout	53
msoShapeRightBrace	32
msoShapeRightBracket	30
msoShapeRightTriangle	8
msoShapeRoundedRectangle	5
msoShapeRoundedRectangularCallout	106
msoShapeSmileyFace	17
msoShapeStripedRightArrow	49
msoShapeSun	23
msoShapeTrapezoid	3
msoShapeUpArrow	35
msoShapeUpArrowCallout	55
msoShapeUpDownArrow	38
msoShapeUpDownArrowCallout	58
msoShapeUpRibbon	97
msoShapeUTurnArrow	42
msoShapeVerticalScroll	101
msoShapeWave	103

CHANGE COMMENT BOX STYLE USING VBA

Paste the below code in the module and run it


Sub Comments_Style()
Dim MyComments As Comment
Dim LArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.TextFrame.Characters.Font.Name = "Tahoma"
.Shape.TextFrame.Characters.Font.Size = 8
.Shape.TextFrame.Characters.Font.ColorIndex = 2
.Shape.Line.ForeColor.RGB = RGB(0, 0, 0)
.Shape.Line.BackColor.RGB = RGB(255, 255, 255)
.Shape.Fill.Visible = msoTrue
.Shape.Fill.ForeColor.RGB = RGB(58, 82, 184)
.Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
End With
Next
End Sub

Change the shape style, font name, font size, font colour etc. as per your requirement.

To know all shape styles click on:
http://excelvbatipsforbeginners.blogspot.in/2012/08/all-shape-styles.html

ENTER DATA ON ALL SHEETS TOGETHER: QUICK TIP

Hold the ctrl key and click on the sheet tabs on which you want to enter the data. Start entering your data and do formatting. Now check the sheets which you have selected. Your data has been entered in all the sheets you have selected with the formatting.

ADD VALUES FROM DIFFERENT SHEETS OF A WORKBOOK WITH A CRITERIA

This is required when we need to add up the values from different sheets to a master sheet with a criteria( Name here).

Example:
Sheet1:
D6: Shweta
E6: 200

Sheet2:
A2: Shweta
B2: 300

Sheet3:
G10: Shweta
H10: 500

Sheet4:
E13: Shweta
F13: Need sum here of all values of "Shweta" from all sheets

Enter formula in F13:
=SUM(SUMIF(INDIRECT("Sheet"&{1,2,3}&"!A:G"),E13,INDIRECT("Sheet"&{1,2,3} &"!B:H")))

Now the question arises what if I have a large number of sheets in my workbook. Say, I need to maintain a different sheet for each day of the month to track the transactions processed by the associates. Hence there are 31 sheets for 31 days of the month.Then it would not be possible to write the sheet number 1,2,3,....31 in the above formula. 

Here is a solution:

Write all the sheet names in a column and give a named range to them. Say MySheets and use below array formula

=SUM(SUMIF(INDIRECT(MySheets & "!A:G"),E13,INDIRECT(MySheets & "!B:H")))
with CSE

WRITE YOUR TEXT IN SHAPES USING VBA

Select a cell (where you want to get the output). Paste the below code in the module and run it.


Sub DrawName()
Const pi = 3.1416
Dim i As Integer
Dim x As Single, y As Single
Dim z As Single
Dim rng As Range
Dim n As Single
Dim k As Integer
Dim sSize As Single
Dim sh As Shape
Dim sName As String
Dim StartLeft As Integer
Dim StartTop As Integer

StartLeft = ActiveCell.Left
StartTop = ActiveCell.Top
sName = InputBox("Enter your text here")
n = 5
k = Len(sName)
sSize = Application.InchesToPoints(0.5)
Randomize Timer
z = 0#
For i = 1 To k
If Mid(sName, i, 1) <> " " Then
x = n * i / k
x = Application.InchesToPoints(x)
If Int(2 * Rnd) = 0 Then
z = z + 0.2
Else
z = z - 0.2
End If
y = Application.InchesToPoints(z)
Set sh = ActiveSheet.Shapes.AddShape _
(msoShapeSun, StartLeft + x, StartTop + y, sSize, sSize)

sh.Fill.ForeColor.RGB = RGB(255, 0, 0)
sh.Fill.Visible = msoTrue
sh.TextFrame.Characters.Text = Mid(sName, i, 1)
sh.TextFrame.Characters.Font.Size = 12
sh.TextFrame.Characters.Font.Name = "Arial"
sh.TextFrame.Characters.Font.Bold = True
sh.TextFrame.Characters.Font.Color = vbBlack
End If
Next i
End Sub

Change the highlighted part to change the shape type. To know all shape types click on:
http://excelvbatipsforbeginners.blogspot.in/2012/08/all-shape-styles.html

After running this code, an inputbox will appear. Enter your text in the inputbox and press enter. Result would be like: (See the image below)

MOVING TITLE FOR USERFORM

Click on the video to see how it will look like.

Follow the below steps to create a moving title for your userform:

1. Launch Microsoft Excel
2. Press ALT+F11 to open Visual Basic Editor
3. Insert an userform
4. Put a label control on your userform ( say, label 1) and clear the text (delete the text under caption in properties)
5.Put another label (say, label 2) above label 1and write your text in label 2( I wrote "EXCEL VBA TIPS").

See the below image

6. Double click on any userform control, code window will appear.
7. Select "General" as object and paste the below code there as shown in the below image(Click to enlarge)

Code:

Private Sub MoveTitle()
    On Error Resume Next
Again:
    Label2.Left = Label2.Left - 0.05
    DoEvents
    For j = 1 To 50000: Next
    If Label2.Left + Label2.Width < 0 Then Label2.Left = Me.Width
    GoTo Again
End Sub

8. Now select "Userform" as object and "Activate" as procedure and paste the below code there as shown in below image.(Click to enlarge)

Code:

Private Sub UserForm_Activate()
Call MoveTitle
End Sub

Run the userform and your userform title will start moving as shown in the above video.

Did this post help you? Please post your valuable comment. Thanks!!

CHANGE TO SENTENCE CASE

Formula in B2:  =UPPER(LEFT(A2,1))&MID(LOWER(A2),2,999) and drag it down.

Did it help you? Please post your valuable comment. Thanks!!