Ask Question
Help Others
Home

Visual Basic

Save as File Using Visual Basic Excel Macro

Wed, 01/30/2008 - 11:49pm — Lepa

I have written this macro a while back and thought it would be of some benefit for some of you.
I've created a button on an excel sheet that when clicked will fire an excel macro to open the well familiar windows "Browse Save as" box and allow the user to save the file to his/her desired location.
The macro limits the "Save as type" to a .txt file format.

save as macro example

Here is the code:

Private Sub CommandButton1_Click()

Application.DisplayAlerts = False

Visual Basic Macro to Delete Blank Rows

Mon, 01/14/2008 - 12:07am — Lepa

Users love "copy & past" from one excel document to another. This however can result in having some empty rows in the document and could cause some major headache if you are trying to generate a .txt file and upload it for processing.

One way around this is to create an excel macro that will delete blank rows once the user generates the .txt file.

Here is the code snippet:

'Deletes blank rows before creating the TAB delimited file
    Dim R As Long
    Dim C As Range
    Dim Rng As Range

    On Error GoTo EndMacro
    Application.ScreenUpdating = False

Validate Character Limits Using Visual Basic

Thu, 12/13/2007 - 12:38am — Lepa

This is a VB excel macro code to prevent users from entering data that exceeds the field limit.

For example, if a cell needs to be only 5 characters long and user entered 7 characters, an error message will popup notifying the user and highlighting the cell containing the 7 characters.

ActiveSheet.Range("A" + posrow).Activate
If Len(ActiveCell.Value) > 5 Then
   Application.ScreenUpdating = True
   ActiveSheet.Range("A" + posrow).Activate
   MsgBox "This Cell Can Not Exceed 5 Characters.", 0, "Data Error."
End If

Force Value to Upper Case Visual Basic Excel Macro

Thu, 12/13/2007 - 12:37am — Lepa

The below Visual Basic code is used in an excel macro. It reads the values on Row "R", trim spaces, checks if the length of the value is equal to zero (nothing in that cell). If so the it will output the letter "N" otherwise it will output an upper case value entered by the user (Format(UCase((ActiveCell.Value))).

The "!@" is the VB way in saying you want to output one character. So, if you have a cell that has 8 character you would code "!@@@@@@@@"

ActiveSheet.Range("R" + posrow).Activate
ActiveCell.Value = Left(Trim(ActiveCell.Value), 1)