Excel/Power point VBA FAQ - Part -II
Google
Web www.mahipalreddy.com

 

 

 

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

Top

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


Top

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

Top

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

Top

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


Top

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


Top

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

Top

 
www.mahipalreddy.com
Terms and Conditions of Use
Copyright 2004 - 2006 Mahipal Padigela. All rights reserved.