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.
Showing posts with label VBA. Show all posts
Showing posts with label VBA. Show all posts
Sunday, June 19, 2016
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
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
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
Monday, March 14, 2016
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
Thursday, March 10, 2016
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
Sunday, March 6, 2016
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
Friday, February 26, 2016
Video example: export html files from an EXCEL database using custom templates
This video shows an example of a program made in EXCEl / VBA that exports html files in a chosen directory from an Excel database: one file per row of data. The template file is customizable.
Full version:
Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD
Tuesday, February 23, 2016
How to print the result of a VBA instruction in a separate window / test a VBA instruction without creating a new module
Labels:
debug.print,
do my excel,
domyexcel.com,
ENGLISH,
Excel,
immediate window,
VBA
At any moment of your VBA program, you can test a code line and print the result in a separate window: Use Debug.print command and make sure the immediate window is open. Note: you can also write directly your code in the immediate window as shown below:
Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD
Monday, February 1, 2016
Video example of VBA custom filters macro in Excel
Labels:
create,
custom filters,
domyexcel.com,
ENGLISH,
Excel,
save,
Userform,
VBA
Application to create and save custom filters to sort your data:
Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD
Sunday, January 31, 2016
Video example of a VBA tetris game in Excel
The "Tetris game" on Excel (coded with VBA) :)
Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD
Saturday, January 30, 2016
Video example of a VBA snake game in Excel
This snake game was made with Excel & VBA: you can play against the computer and drop some bombs :)
Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD
Tuesday, October 13, 2015
How to move and resize an Excel chart or shape using a VBA macro
To move a chart to a specific cell or resize to be same as range:
Sub relocate()
Call move_chart_to_cell(Cells(1, 1))
Call resize_chart_same_as_range(Range("A1:E20"))
Call resize_chart_same_as_range(Range("A1:E20"))
End Sub
Sub move_chart_to_cell(cell as range)
ActiveSheet.Shapes("MyChart").Left = cell.Left
ActiveSheet.Shapes("MyChart").Top = cell.Top
ActiveSheet.Shapes("MyChart").Top = cell.Top
End Sub
Sub resize_chart_same_as_range(r as range)
ActiveSheet.Shapes("MyChart").Height = r.Height
ActiveSheet.Shapes("MyChart").Width = r.Width
Sub resize_chart_same_as_range(r as range)
ActiveSheet.Shapes("MyChart").Height = r.Height
ActiveSheet.Shapes("MyChart").Width = r.Width
End Sub
Before Macro run:
After Macro run:
Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD
Monday, October 5, 2015
How to show and hide grouped columns and rows in Excel using a VBA macro
To show grouped columns or range, the command ExecuteExcel4Macro "SHOW DETAIL" can be used. In short, the syntax can be summarized as is:
Example: The VBA code below will first "open" the grouped columns and then close the grouped rows.
ExecuteExcel4Macro "SHOW.DETAIL(2,4,false)"
MsgBox "Grouped columns D to G are ""closed"""
'Open Rows
ExecuteExcel4Macro "SHOW.DETAIL(1,10,true)"
MsgBox "Grouped rows 8 to 11 are ""open"""
End Sub
ExecuteExcel4Macro "SHOW.DETAIL(row_or_column,location,boolean)"
with
row_or_column = 1 for row, 2 for column
location = integer to indicate row number or column number to show or hide
boolean = True to "Open", False to "Close"
with
row_or_column = 1 for row, 2 for column
location = integer to indicate row number or column number to show or hide
boolean = True to "Open", False to "Close"
Sub showdetails()
'Close ColumnsExecuteExcel4Macro "SHOW.DETAIL(2,4,false)"
MsgBox "Grouped columns D to G are ""closed"""
'Open Rows
ExecuteExcel4Macro "SHOW.DETAIL(1,10,true)"
MsgBox "Grouped rows 8 to 11 are ""open"""
End Sub
Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD
Saturday, October 3, 2015
How to create an array in VBA from an Excel range
If you have some data in Excel you want to retrieve in VBA under an Array, just use the "=" sign:
Sub create_array()
newarray = Sheets("Sheet1").Range("A1:B10").Value
MsgBox newarray(6, 2)
End Sub
Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD
How to find a value in an Excel range and return its address in VBA
If you want to find the word "DoMyExcel" in a range of your spreadsheet and return its location / address, create a module with this code:
Sub Return_Search_Result_Adress()
Dim My_Range As RangeSet My_Range = Sheets("Sheet1").Range("A1:A10000").Find("DoMyExcel", lookat:=xlPart)
If Not My_Range Is Nothing Then
MsgBox "Found at " & My_Range.Address
End If
End Sub
- xlPart = looks at the text in the cell for any match. If you search for any part of the text (such as "Excel"), it will return "Found at $A$6".
- xlWhole = looks at the entire/exact entry in the cell to see if it matches.
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 the contents of a VBA userform textbox on mouse click
Highlighting the contents of a textbox on mouse click in a user's interface eases data entering. To do so, simply enter the following code under the Userform VBA code:
Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, _
ByVal X As Single, ByVal Y As Single)With TextBox1
.SelStart = 0
.SelLength = Len(.Text)
End With
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
Subscribe to:
Posts (Atom)