To add series to an existing chart easily, copy the data, select the chart and paste as shown in GIF below:
Sunday, June 19, 2016
EXCEL VBA - How to link Option buttons together in a VBA Userform
Labels:
do my excel,
domyexcel.com,
Excel,
group,
groupname,
link,
option button,
optionbutton,
properties,
Userform,
VBA
To manage the option buttons on a VBA userform, use the "option button group" parameter: Enter one name per group of option button, so the program will know which ones are linked.
EXCEL VBA: How to only accept number format in a VBA Userform Textbox
Labels:
authorized,
constraint,
do my excel,
domyexcel.com,
Excel,
number,
only numbers,
textbox,
Userform,
VBA
To accept only numbers in a VBA Userform Textbox for example, use the following code:
Private Sub TextBox1_Change()
OnlyNumbers
End Sub
Private Sub OnlyNumbers()
If TypeName(Me.ActiveControl) = "TextBox" Then
With Me.ActiveControl
If Not IsNumeric(.Value) And .Value <> vbNullString Then
MsgBox "Sorry, only numbers allowed"
.Value = vbNullString
End If
End With
End If
End Sub
EXCEL VBA: How to convert a Textbox value in number
Labels:
convert,
do my excel,
domyexcel.com,
Excel,
number,
string,
textbox,
Userform,
VBA
The textbox always return a string, you have to use one of the function below to convert it to the format you want:
Example if we want to multiply by 2 the number entered in Textbox1:
CBool(expression)CByte(expression)CChar(expression)CDate(expression)CDbl(expression)CDec(expression)CInt(expression)CLng(expression)CObj(expression)CSByte(expression)CShort(expression)CSng(expression)CStr(expression)CUInt(expression)CULng(expression)
CUShort(expression) Example if we want to multiply by 2 the number entered in Textbox1:
Private Sub CommandButton1_Click()
a = CInt(TextBox1.Value)
MsgBox ("If we multiply Textbox1 value by 2, the result is: " & a * 2)
End Sub
Monday, May 2, 2016
How to highlight the text of a Userform Textbox at mouse click in EXCEL VBA
Labels:
entire text,
highlight,
key down,
mouse click,
select,
text,
textbox,
Userform
First create a userform with a text box (Pic 1, pic 2, pic 3)
Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
With UserForm1.TextBox1
.SelStart = 0
.SelLength = Len(.Text)
.ForeColor = &H80000001
End With
End Sub
Now, whenever the user click on the textbox, it will highlight the text inside automatically
Saturday, April 23, 2016
Excel FINANCE Tip : Live Connection to Actual Stock Price (YAHOO FINANCE)
If you want to create a live connection with the latest stock price for any company/symbol you can follwow the following steps:
Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 89 USD
Friday, April 1, 2016
EXCEL TIP: How to Break and Interrupt an Excel VBA Macro
If you want to break a VBA macro and stop it from running or if your code is stuck in a loop just press the following hotkey:
CTRL + BREAK
(If you dont have the "Break" Key on your keyboard you can use the "On-Screen Keyboard" from Windows)
Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 89 USD
Wednesday, March 30, 2016
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
Tuesday, March 22, 2016
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.
Search the Stock in the address bar as shown below:
The Data Connection can always be refrehes by clicking on the "Refresh All" button as 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
Tuesday, March 15, 2016
How to exit a userform in EXCEL VBA
Labels:
commandbutton,
do my excel,
domyexcel.com,
end,
Excel,
exit,
hide,
stop,
unload,
Userform,
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
How to manage the Cancel response from an inputbox in EXCEL VBA
Labels:
answer,
button,
cancel,
do my excel,
domyexcel.com,
exit sub,
inputbox,
interface,
user
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)
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
How to create a file picker interface in EXCEL VBA with browse button in 3 steps
Labels:
browse,
commandbutton,
do my excel,
domyexcel.com,
file,
filepicker,
interface,
pick,
textbox,
Userform
1. First create a userform with a textbox and a command button:
2. Then enter the following code in the commandbutton click event:
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
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
Monday, March 14, 2016
How to run a macro from an hyperlink event EXCEL VBA
Labels:
do my excel,
domyexcel.com,
event,
followhyperlink,
from,
hyperlink,
macro,
run
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:
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
How to add hyperlinks for macro call outs in EXCEL VBA
Labels:
automatically,
call out,
cell,
do my excel,
domyexcel.com,
Excel,
hyperlink,
macros,
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
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
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
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
Video example: Import and size pictures in EXCEL VBA
Labels:
cell,
do my excel,
domyexcel.com,
example,
Excel,
fit picture,
import,
picture,
profile pic,
range,
shape,
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
Subscribe to:
Posts (Atom)