EXCEL Tip : How to Change the Excel Theme

If you are bored with the usual Excel Theme - You can change it! Just follow the 2 easy steps below:

There are 4 different themes in Office 2016

DARK GRAY

BLACK

WHITE

Colourful (by default) - Green in Excel 2016

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 89 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

Excel FINANCE Tip : Connect Excel to Stock Historical Data

If you wish to connect your Excel file to Live or Historical Stock Data please follow the instructions below.

After Opening a new Excel Workbook:

The "New Web Query" Excel Browser will open as below:

Search the Stock in the address bar as shown below:

A Start Date  and End Date for the Historical Data can be selected above.( Click on "Get Prices" Afterwards). TIP: If date is left far in the future Excel will always have the latest data when the connection is refreshed.

After Step 4) just Click IMPORT and proceed to the step below:

The Data Connection can always be refrehes by clicking on the "Refresh All" button as below:

Thank you for your attention!

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 89 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to exit a userform in EXCEL VBA

Simply use the "End" method:

Sub commandbutton1_click()

End

End Sub

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 89 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to manage the Cancel response from an inputbox in EXCEL VBA

To exit the inputbox form when a user press the cancel button, simply write exit sub when the result of the inputbox function is nothing:

Sub create_array()

pp = InputBox("How are you today?")
If pp = "" Then Exit Sub
msgbox("Your answer is " & pp)

End Sub

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 89 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to create a file picker interface in EXCEL VBA with browse button in 3 steps

1. First create a userform with a textbox and a command button:



2. Then enter the following code in the commandbutton click event:

Sub CommandButton1_Click()

    Dim fd As Office.FileDialog

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

   With fd

      .InitialFileName = ThisWorkbook.Path
      .AllowMultiSelect = False
      .Title = "Please select the file."
      .Filters.Clear
      .Filters.Add "All Files", "*.*"

      ' Show the dialog box. If the .Show method returns True, the user picked at least one file. If the .Show method returns False, the user clicked Cancel.
      If .Show = True Then
        UserForm1.TextBox1.Value = .SelectedItems(1) 'replace txtFileName with your textbox

      End If
   End With

End Sub

3. Launch your userform by entering following macro in new module for example:

Sub launch_userform1()

userform1.show

End Sub

>> As a result, you will have a user interface where you can pick a file in your folder and get the file path in the textbox for future usage:

 

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 89 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to run a macro from an hyperlink event EXCEL VBA

Follow these steps to run a macro from an hyperlink:

First, create manually an hyperlink which is linked to its own cell in EXCEL:
Note you can create hyperlinks automatically using a macro: see post http://domyexcelblog.blogspot.com/2016/03/how-to-add-hyperlinks-for-macro-call.html

Then, create the Hyperlink event in VBA:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

MsgBox ("You just clicked on the hyperlink on cell column = " & ActiveCell.Column & ", row = " & ActiveCell.Row)

End Sub

Finally, test your macro: when you click on the hyperlink the following message from your macro will appear:

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 89 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to add hyperlinks for macro call outs in EXCEL VBA

This program will create hyperlinks that link to their own cell in excel, it is then easy to use these hyperlinks to create macros (see blog post http://domyexcelblog.blogspot.com/2016/03/how-to-run-macro-from-hyperlink-event.html ) :

Sub Add_Hyperlinks_for_macros()

For i = 6 To 200
    If Cells(i, 5) <> "" Then
                ActiveSheet.Cells(i, 5).Select
                ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
                    "'My Contacts'!e" & i, TextToDisplay:="Add Photo"
    End If
Next

End Sub

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to check the size of a picture in bytes in EXCEL VBA

To check the size in bytes of your pictures, use the "FileLen()" function of VBA:

Sub Check_pic_size(mypic)

Set mypic = ActiveSheet.Shapes.AddPicture(pp, msoFalse, msoTrue, ActiveCell.Left, ActiveCell.Top, -1, -1)

If FileLen(pp) > 100000 Then
     MsgBox ("The size of your file is too big, please reduce to 100,000 bytes. Current size is " &      FileLen(pp) & " bytes.")
     mypic.Delete
     Exit Sub
End If

End Sub

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

Video example: Import and size pictures in EXCEL VBA

This video show an example of contact file where up to 30 profiles can be added with profile info and autosized profile pic.

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

Excel Tip: Clear / Unfilter FILTERs Quickly

In Order to Clear / Unfilter a filter quickly you can use the Filter Hotkey Two Times :

Press 2 Times : CTRL + SHIFT + L 

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

Example: Add ON EXCEL to create and export custom pictures

Take a look at this EXCEL Add on we created in order to easily apply custom format, caption and other elements to pictures and to export them:

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to select all the pictures of a worksheet in EXCEL

To select all the shapes / objects / pictures of a worksheet in EXCEL, simply select one of the shapes and press "CTRL"+ A. You can then copy them, delete them all, etc.

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to receive an text input from a message box in EXCEL VBA

If you want to ask the user for an input, you can create a userform or you can simply use the Inputbox() method of VBA. For example:

a = InputBox("Enter the name of the picture")

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to display all the elements of an array in a message box VBA

If you want to know what is contained in your VBA array simply use the Join method:

Join(my_array,vbCr)

Example: if you want to add the following data into an array and then display the array

Sub print_array_example()

Dim arr() As Variant

ReDim arr(1 To 5) As Variant

For i = 1 To 5

    arr(i) = Cells(i, 1)

Next

  

MsgBox Join(arr, vbCr)

End Sub

 

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to highlight an option button in EXCEL when it is selected by the user (no macro needed)

If you want to highlight the selected option button for your custom form in EXCEL, first set the background of the option button as "Transparent": Right click on the option button > "Format Control..." then on tab "Colors and Lines, put the "Transparency" level at 100% and press "OK":

Then if the result of your option button appear in cell "A5" for example (see previous post here to see how to do) then in cell A1 and A2 (behind the option buttons), enter respectivelly the following formulas:

"=if(A5=1,"color","")"

"=if(A5=2,"color","")"

Finally, create a conditional format to color the cell when the value of the cell is equal to "color". 

Trick to not show the formula: put the default font color of A1 and A2 as white and in the conditional formating options, set the font color to be same as the cell background color when the value is equal to "color".

As a result, the option button will be highlighted once the user select it:

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com