|
How do I invoke File Open dialog box in Excel using VBA?
How do I use File Open dialog box without using commondialog control in Excel using VBA?
How do I use GetOpenFilename to get user input in Excel VBA ?
This can be achieved using GetOpenFilename method.
This is like using the CommonDialog control without the overhead of the control
or the need for the UserForm. Using this method results in a faster application than one that uses a CommonDialog control
to perform the same action.
expression.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)
'Code by Mahipal Padigela
'Open Microsoft Excel
'Goto VBA Editor (Control + F11 or navigate Menu Tools-->Macro-->Visual BAsic Editor)
'Insert a Module (Right click your file name in the VB editor-->Insert-->Module)
'Paste the following code into the Module1
'Run the Macro from Excel window (Alt + F8 OR goto Tools-->MAcro-->Macros-->click RUN button.
Sub FileOpenDemo()
Dim fname As String
Dim strTemp As String
fname = Application.GetOpenFilename("Excel Files (*.xls), *.xls)")
' Test if Cancel button was clicked.
If InStr(fname, "False") = 0 Then
' Code to open the chosen file would go here.
strTemp = "You selected the '" & fname & "' file."
Else
strTemp = "You clicked Cancel!"
End If
MsgBox strTemp
End Sub
How do I invoke File SaveAs dialog box in Excel using VBA?
How do I use File SaveAs dialog box without using commondialog control in Excel using VBA?
'Open Microsoft Excel
'Goto VBA Editor (Control + F11 or navigate Menu Tools-->Macro-->Visual BAsic Editor)
'Insert a Module (Right click your file name in the VB editor-->Insert-->Module)
'Paste the following code into the Module1
'Run the Macro from Excel window (Alt + F8 OR goto Tools-->MAcro-->Macros-->click RUN button.
Sub FileSaveAsDemo()
Dim fname As String
Dim wb As Workbook
Set wb = Workbooks.Add
fname = Application.GetSaveAsFilename
If InStr(fname, "False") = 0 Then
If UCase(Right(fname, 4)) <> ".XLS" Then
wb.SaveAs fname & ".xls"
Else
wb.SaveAs fname
End If
End If
End Sub
How do I use the InputBox Method to prompt users to choose a Range in Excel using VBA.
How do I allow or give an option to users to select a range in Excel using VBA?
What's the difference between InputBox method and InputBox function?
expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)
The InputBox method differs from the InputBox function in several important ways.
The InputBox method has a Type argument that specifies the data type to be returned, and must be referenced by using
the Application object. In this case, it is set to a cell reference as a Range object (type=8).
If the Cancel button is pressed, the input box returns a False Boolean type variable.
The possible values for the Type parameter are:
If this argument is omitted, the dialog box returns text.
0 - A Formula
1 - A Number
2 - Text (a string)
4 - A logical value (True or False)
8 - A cell reference, as a Range object
16 - An error value, such as #N/A
64 - An array of values
You can use the sum of the allowable values for Type. For example, for an input box that can accept both
text and numbers, set Type to 1 + 2
'Open Microsoft Excel
'Goto VBA Editor (Control + F11 or navigate Menu Tools-->Macro-->Visual BAsic Editor)
'Insert a Module (Right click your file name in the VB editor-->Insert-->Module)
'Paste the following code into the Module1
'Run the Macro from Excel window (Alt + F8 OR goto Tools-->MAcro-->Macros-->click RUN button.
Sub InputboxDemo()
Dim myRange As Range
On Error Resume Next
Set myRange = Application.InputBox(Prompt:="Please select a range of cells", _
Title:="Mahipalreddy.com-VBAhelp", Type:=8)
If (myRange Is Nothing) = False Then
For Each rngCell In myRange.Cells
If IsEmpty(rngCell.Value) = False Then
MsgBox rngCell.Value
End If
Next rngCell
End If
End Sub
How do I suppress or disable alerts in Excel using VBA ?
How do I suppress or disable Prompt dialog box / alert dialog box in Excel using VBA?
There are many instances where you need this bit of functionality, for ex. when you need to delete a worksheet from an excel
workbook programatically, excel comes with a confirmation dialogbox.
'Open Microsoft Excel
'Goto VBA Editor (Control + F11 or navigate Menu Tools-->Macro-->Visual BAsic Editor)
'Insert a Module (Right click your file name in the VB editor-->Insert-->Module)
'Paste the following code into the Module1
'Run the Macro from Excel window (Alt + F8 OR goto Tools-->MAcro-->Macros-->click RUN button.
Sub SuppressAlerts()
'disable the alerts
Application.DisplayAlerts = False
'do some processing
ActiveWorkbook.Sheets(2).Delete
'enable alerts again when you finish
Application.DisplayAlerts = True
End Sub
How do I suppress or disable Excel events / event procedures using VBA?
You want to open an excel workbook either through VBA or through Automation,
without triggering any of its event procedures. Say for example, the workbook to be opened has some code that
excutes when it's opened, but you don't want it to be executed. In that case, you suppress the events before opening the workbook,
do the processing and enable the events when you finish.
'Open Microsoft Excel
'Goto VBA Editor (Control + F11 or navigate Menu Tools-->Macro-->Visual BAsic Editor)
'Insert a Module (Right click your file name in the VB editor-->Insert-->Module)
'Paste the following code into the Module1
'Run the Macro from Excel window (Alt + F8 OR goto Tools-->MAcro-->Macros-->click RUN button.
Sub SuppressEvents()
Dim wb As Workbook
Application.EnableEvents = False
Set wb = Workbooks.Open("C:\Book1.xls")
'processing goes here
wb.Close
Set wb = Nothing
Application.EnableEvents = True
End Sub
What's the difference between the VALUE and TEXT
properties of the Range and cells objects?
Value property returns just the value(contents) in the cell while the text property
returns the value along with it's formatting.
For example, you have a currency column, and the value of the cell in row10, column D is £20.00
Using Activesheet.Cells(10,4).value or Activesheet.Range("D10").value returns '10' while
Activesheet.Cells(10,4).text or Activesheet.Range("D10").Text returns '£20.00'
Sub ValueText()
MsgBox Range("H11").Value & "--" & Cells(11, 8).Text
End Sub
How do I turn off the column and row headings on an excel worksheet ?
How do I toggle column and row headings on an excel worksheet?
Sub ToggleHeadings()
Dim blnHeadings As Boolean
blnHeadings = ActiveWindow.DisplayHeadings
ActiveWindow.DisplayHeadings = Not blnHeadings
End Sub
|
