AutomateNow

VBA Programming in Excel 2010

2012-05-10T09:35:00.002-07:00

Are you upgrading to Excel 2010 soon? My suggestion would be to familiarize yourself with all the rich features now! Unlock your full potential with the following release from John Walkenbach available now on Amazon.com:

Top-10-valentines-day-templates

2012-02-10T08:00:00.000-08:00

Top-10-valentines-day-templates

Data Validation List based on another data validation list

2011-05-07T10:26:00.000-07:00

Have you ever thought how simple life could be if you could make your spreadsheets act more like a form without going through the trouble of creating one? In today's example you can create a list of values in one field where another list is populated based on what the user selects!

Sub list2()

With Cells(1, 2).Validation

.Delete

If Cells(1, 1) = "one" Then

.Add Type:=xlValidateList, Formula1:="A, B, C"

ElseIf Cells(1, 1) = "two" Then

.Add Type:=xlValidateList, Formula1:="D, E, F"

ElseIf Cells(1, 1) = "three" Then

.Add Type:=xlValidateList, Formula1:="G, H, I"

End If

End With

End Sub

The above example will only refresh when the macro is run. If you want this to run as soon as a value is chosen you may want to set this into the worksheet where an event will trigger it to run.

Printing Multiple Sheets with VBA

2011-03-03T16:33:00.000-08:00

Printing Multiple Sheets

Unhiding Multiple Worksheets

2011-03-03T08:00:00.000-08:00

Most people who use Excel know that you can hide one or more sheets in Excel. Thanks to thesimplemachine.com we now have a short code for unhiding these sheets all at once rather than one at a time. Take a look!

Unhiding Multiple Sheets with VBA

Edit Multiple Selections in Excel Drop Down Lists

2011-03-01T06:09:00.000-08:00

Microsoft has come up with an amazing array of features in Excel. One such feature is the drop down list which creates the ability for the user to provide feedback that drives other parts of the workbook. Gathering feedback from your audience not only allows your them to change scenarios as they see fit but it prevents you from recreating reports for each scenario.

As with any feature there are limitations. This is where the beauty of VBA comes into play! In yesterday's blog post from Contextures.com we are shown how you can handle problems with editing drop down lists:

Contextures Blog » Edit Multiple Selections in Excel Drop Down Lists

Get Rid of Page Break Lines with a snap [macros] | Chandoo.org - Learn Microsoft Excel Online

2011-02-28T10:23:00.000-08:00

Get Rid of Page Break Lines with a snap [macros] | Chandoo.org - Learn Microsoft Excel Online

John Walkenbach on Excel

2011-02-24T14:03:00.000-08:00

To learn more about Excel, take a look at the selection of books now available from:

Mr Spreadsheet

Creating arrays using VBA

2011-02-24T07:42:00.000-08:00

Now with an understanding of the power of arrays we can build upon this using VBA. Here is a very thorough overview of arrays from a highly respected source:

http://www.cpearson.com/excel/vbaarrays.htm

Please visit our sponsors to the right for more resources available on Excel & VBA today!

Mr Excel & excelisfun Trick 67: Extract Initials From Full Name: VBA or ...

2011-02-22T08:00:00.000-08:00

Multiple methods to use built in Functions from Excel in VBA

2011-02-19T08:00:00.000-08:00

How to Use an Excel Formula from VBA by Yoav Eze

Excel Date functions

2011-02-18T08:00:00.000-08:00

Date Functions in Excel

Date Functions in VBA for Excel

Excel User: Excel Charts – Use an Image as Line Chart Markers

2011-02-17T08:00:00.000-08:00

Excel User: Excel Charts – Use an Image as Line Chart Markers

Quickly Compare Data using Row Differences

2011-02-16T15:00:00.000-08:00

Quickly Compare Data using Row Differences

Excel Array Formula Series 14.4: Stock Portfolio Analysis

2011-02-16T08:00:00.000-08:00

Excel Array Formula Series 14.3 Portfolio Standard Deviation

2011-02-15T08:00:00.000-08:00

Valentines Day Excel Workbook VBA

2011-02-10T08:00:00.001-08:00

Admittedly, this could be made into so much more of an elaborate greeting but then perhaps flowers or chocolates would be more suitable.

This short script, when placed within the 'This Workbook' section in the project section in your VB browser, will activate any time a change is made to that workbook (i.e. selecting a cell).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

x = MsgBox("Will you be my valentine?", vbYesNo, "Valentine Greeting")

If x = 6 Then

MsgBox "I am yours"

Else

MsgBox "Happy Valentines Day!"

End If

End Sub

As you can see, a pop-up window will appear with the question "Will you be my valentine?" If the user selects yes you see a new pop-up window with "I am yours". If no is selected they will only see "Happy Valentines Day!". Surprise the Excel geek in your life with a workbook today and a custom message of your choosing and have a happy valentine's day!

Excel Array Formula Series #14.2: Beginning Stock Analysis

2011-02-09T08:00:00.000-08:00

Excel Array Formula Series 14.1: Stock Returns

2011-02-08T08:00:00.000-08:00

Highlight fields in vba rather than conditional formatting

2011-02-07T08:00:00.001-08:00

Ever wanted to quickly highlight cells where the values are not found in another list? Here is an example of how VBA can accomplish just that!

Notice that there is an error handling used 'On Error Resume Next'? If you run this macro without it you will have problems but this statement allows the script to continue on even though the error exists!

Excel Array Formula Series #13: LINEST function

2011-02-06T08:00:00.000-08:00

Excel Array Formula Series 12: MMULT function Matrix Algebra

2011-02-05T08:00:00.000-08:00

Excel Array Formula Series #11: ROW & COLUMN functions

2011-02-04T08:00:00.000-08:00

Excel Array Formula Series #10: Amazing Syntax Array Trick

2011-02-03T08:00:00.000-08:00

Excel Array Formula Series #9: Count Characters in Range

2011-02-02T08:00:00.000-08:00