davesexcel.com

Excel information

Most Popular Excel Questions

view:  full / summary

Copy File from one folder to another folder

Posted by davesexcel on March 30, 2013 at 1:25 PM Comments comments (0)

Copy a file from one folder to another folder


This will copy one file from a folder to another folder


Sub CopyToDifferentFolder()

    FileCopy "C:\Users\Dave\Downloads\SourceFolder\OriginalWorkbook.xlsm", _

             "C:\Users\Dave\Downloads\DestinationFolder\OriginalWorkbook.xlsm"

End Sub

Read Full Post »

Loop Through a Folder of Workbooks

Posted by davesexcel on November 26, 2012 at 12:45 AM Comments comments (0)

Get data from many workbooks in a folder.



This code will loop through a folder called "WorkBookLoop".

The code then Copies and pastes whatever is in column A:B and paste it to the workbook that is running the code.


 

Sub LoopThroughFolder()

 ...

Read Full Post »

EMAIL A FILE FROM EXCEL

Posted by davesexcel on November 13, 2012 at 10:40 AM Comments comments (0)

Select a file to be emailed using excel VBA

This example uses three combo boxes to select the folder,file,then email address to send a file.

This 1st code will list the subfolders from the C:\ drive and populate a combobox that is on the worksheet. The combobox and command button are drawn from the controls toolbar and the code is located in the worksheet module.

Read Full Post »

Enter Password

Posted by davesexcel on November 11, 2012 at 5:45 AM Comments comments (0)

If you want the user to enter a password before completing an action use a User Form.

How to make a userform click the link.

http://www.davesexcel.com/createauserform.htm

Create your userform and draw a textbox and two buttons on it.

Select the textbox and then in the properties window under PasswordChar enter * (astrix)

Read Full Post »

Make a screen Pop Up when Opening Excel

Posted by davesexcel on April 14, 2012 at 3:30 PM Comments comments (0)

I want a screen to pop up when I Open an Excel Workbook and then the screen closes after 5 seconds.


This can be done by creating a UserForm, and then use VBA code to Show the userform.(Splash Screen)

We use 3 types of modules here

  1. TheWorkbook Module
  2. The UserForm Module
  3. The Regu...
Read Full Post »

Add Buttons to a Range of Cells

Posted by davesexcel on April 12, 2012 at 4:15 AM Comments comments (0)

I want to add buttons, name them and assign macros to them.


Use this code, it will add buttons to A1:A3, name each button and assign a macro to them.

Make sure you have the macros in place before you run the CreateButtons code.


 

Sub CreateButtons()

    Dim butn As Button, Rng As Range, c As Range, i

   ...

Read Full Post »

Add items to a ListBox

Posted by davesexcel on April 1, 2012 at 7:05 PM Comments comments (0)

These codes will populate ListBoxes from the controls toolbar.

Populate a ListBox with Unique Items from Column A

Private Sub CommandButton1_Click()

'Written by Dave Morrison

'November 17, 2010

'This code will populate ListBox1 with items from Column1, with no duplicates

    Dim rng As Range, c As Range, r As Range

    Dim rws As Long, y As ...

Read Full Post »

Create a list of months excel VBA

Posted by davesexcel on March 29, 2012 at 9:05 PM Comments comments (0)

Create a list of months for each row in Column A


Sub ListMonthInRow()

Dim c As Long

For c = 1 To 12

Cells(c, 1) = Format(DateSerial(2012, c, 1), "mmmm")

Next c

End Sub

Create a list of months In Row 1


Read Full Post »

Comment to include value from another cell

Posted by davesexcel on March 29, 2012 at 7:50 PM Comments comments (0)

I want to add a comment to H1 and place what is in Cell A1 to be in the comment.

You can use VBA to add a comment and have what is in A1 in the comment box.


Sub CommentBox()

    Dim A1 As Range

    Set A1 = Range("A1")

    With Range("H1")

Read Full Post »

Using an If Statement in VBA

Posted by davesexcel on March 29, 2012 at 7:15 PM Comments comments (0)

You have an If statement in Cell B1 that goes like this.

=IF(A1=20,"Yep","Nope")

In VBA the code would be.

 

Sub VBA_If_Statement()

    Dim Ar As Range

    Set Ar = Range("A1")

    Range("B1") = IIf(Ar = 20, "Yep", "Nope")

Read Full Post »

Rss_feed