ExcelJet (website)

1. Formula's
2. Functions
3. Key Board Short Cuts

Send email from Excel

Embedding a table in excel to a word doc. Update the table from excel to word.

1. Create a table in excel. The table should be the only thing on the sheet. Hide guideline as well.
2. Save and Close the excel workbook
3. Open Word
4. From the insert tab click on Text section click: Object..
   
   
   
5. Click "Create from File" > Click "Link to file" > Click Browse...
   
6. Browse to the excel file and click OK. The table is now in word.
7. Go back to excel and add new data to the table. Save and close excel.
8. Go back to Word. Right click anywhere in the table and choose "Update Link" The table will be updated with the new data added in excel.

Environ
Getting Username, special folders and more using the Environ function

Web site for Environ function, with examples.

Using Environ to get user name / logon ID
menu environ to get user name The name (logon ID) of the user that is currently logged on.

Environ("USERNAME ")

example result: johndoe


Sub owner()

Dim owner As String
owner = Environ("username")

MsgBox owner

End Sub ' this will result in the computers username


example: Dim TempFile As String
TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

How to add a video to a Excel worksheet.

1. Click Design Mode
2. Click Insert and the tool icon
   
   
3. Scroll down to Windows Media Player
   
   
4. This will give you the + sign to draw the size of the player in the worksheet
   
   
5. Now click properties
   
   
6. Add File or URL to play in the player. You can also set autoplay placement and more.
   

How to share Excel and allow others to edit file online

1. Owner of file uploads file to Microsoft OneDrive.
2. Owner then opens file from Microsoft OneDrive.
3. Owner then clicks share and sends email('s) to people whom he wants to share with.
4. Email Recipiant's open file from the email they received. Then they can click edit from browser to edit file. File is auto saved, there is no save button.
5. Once file has been completed, it can be downloaded by all to there computer if necessary.
6. Everyone will see changes being made by others.
7. Macro's will not work online, however all macros will be intact if a person downloads the file to there local machine. One problem with this is that the macro button will not be seen however if there is a keystroke equivalent the macro will work. After down load you can still add a button and then assign to the macro if you choose to do so.

Cells Speak

Right click on the quick reference bar and choose "More Commands". Choose "Commands not in the ribbon" "Speak Cell on Enter".
To make a cell talk using a macro:

Sub hello()


Range("A1").Speak 'What ever is in cell A1 will be spoken

end sub


---

Sub direct_speech()

Application.Speech.Speak "Hello world"        ' This will say what is written in quotes .... Use this in a macro to make it talk
Application.Speech.Speak (Range("B12"))      ' This will say what is in the specified cell address
' MsgBox "Any messsage you want to display here"
End Sub

Make Sound on Error

Follow this link

What color is the active cell? - Convert Hex to RGB here

Sub test_color()
MsgBox ActiveCell.Interior.Color
End Sub


Sub colorchange()

If Range("A1").Interior.Color = RGB(55, 86, 35) Then ActiveCell.Offset(0, 1).Select

End Sub

How to add a Bullet Point

Goto the insert Tab. Then Symbols or shortcut = (Alt + N +U)

Add a Comment to the Activecell using VBA

Sub AdComment()
Dim X As String
Dim d As Date

X = InputBox("Enter Comment")
d = Date

ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=d & vbNewLine & X
End Sub

Add a Comment Plus Font Color, Bold, Text Size

Sub adcomment()
Dim X As String
Dim d As Variant

X = InputBox("Enter Your Comment")
d = Date

ActiveCell.AddComment
ActiveCell.comment.Visible = False
ActiveCell.comment.Text Text:=d & vbNewLine & X

With ActiveCell.comment.Shape.TextFrame.Characters.Font

.ColorIndex = 0 ' This is the Text Color
.Size = 8
.Name = "Bell MT"
'.Name = "Arial Black" ' Change the font to whatever you like
.Bold = True
.Italic = True

End With

End Sub


Here is another example from Chandoo


This will change all comments on the page. But will not affect new comments added after the macro is run.
Sub Comments_Fun()
Dim MyComments As Comment
'Dim LArea As Long ' This is not needed but was in Chandoo's code so I will leave it there commented out.
For Each MyComments In ActiveSheet.Comments
With MyComments

.Shape.AutoShapeType = msoShapeRoundedRectangle ' Change this using the Ctrl + Spacebar and choose shape.
.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) ' Change to brown (128, 0, 0) or any RGB color
.Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23

End With
Next 'comment
End Sub

Link to Microsoft Color Index

Link to Microsoft Color Index with examples

Control the Zoom on the page

Sub zoom()
ActiveWindow.zoom = 100
End Sub

How to read the filenames of all the files in a directory into an array

For instance, in order to populate a list box

Article contributed by Dave Rado

You can use the Dir$ function to do this. (Dir and Dir$ are functionally identical, but Dir$ runs marginally faster).

Dir$ is a lot faster than using FileSearch, unless many subdirectories need to be searched or you need to use the advanced features of FileSearch such as the LastModified property.

Dim MyFile As String
Dim Counter As Long

'Create a dynamic array variable, and then declare its initial size
Dim DirectoryListArray() As String
ReDim DirectoryListArray(1000)

'Loop through all the files in the directory by using Dir$ function
MyFile = Dir$("c:\temp\*.*")
Do While MyFile <> ""
DirectoryListArray(Counter) = MyFile
MyFile = Dir$
Counter = Counter + 1
Loop

'Reset the size of the array without losing its values by using Redim Preserve
Redim Preserve DirectoryListArray(Counter - 1)

To prove it worked you could run the following:

For Counter = 0 To UBound(DirectoryListArray)
'Debug.Print writes the results to the Immediate window (press Ctrl + G to view it)'
Debug.Print DirectoryListArray(Counter)
Next Counter

To populate a Listbox from the array you could use:

ListBox1.List = DirectoryListArray

Standard RGB Colors

Remove the Windows Login Screen

Search for: NETPLWIZ
Uncheck: Users must enter a user name and password.
Click apply and reboot system.