Introduction

---

Brief Description

Hi Friend, Welcome to ExcelItems –  You’ve been come to right place, if you’re an excel user and looking for some related material. From books to trainers, macros to free downloads, solutions on discussion forums, we’re fully equipped with Excel items here. Ok, have you heard about OpenXL yet? Yessss ! and then you should know it’s our product helping thousands of excel users from newbie to amateur to professionals one. If you’re still unaware of it, then use it today.

---

Discussion Board

Our discussion board is largest Google group with the name, MS Excel and VBA Macros, Here is the link: http://groups.google.com/group/excel-macros/.

This group is 2 years old and 100% resistant to spam, serving above 4000 worldwide members. Microsoft’s Most Valuable Professionals(MVP) like Rodney Powell is active member of this community.

---

Open Excel

It’s Power of Open Source in Microsoft Excel. Yes, It’s a free add-in with source code provided to you on this website from our developer, Ashish Jain. It solves many Excel 2007 problems and automates frequently used tasks for you.

For e.g. it provides Excel 2003 style menu in Excel 2007, super hide a worksheet, run charts slide show, calculate number of business days between two dates, remove all macros, can append 0 to any number of cells and much more. To explore – visit OpenXL here.

---

VBA Macros Collection

Have you ever visited a website with full collection of VBA macros, so that you need not to go on another website? We hope you find us of this type. We are in continuous endeavor to provide you with latest research of us on Macros, so that you don’t waste your precious time in developing what already has been developed. Click here to visit our VBA Macros collection.

---

Tutorials

Today, there are so many institutions, schools, colleges and teachers but where are the good ones? The Good one is you itself, yes we can learn and study better than ever and without anyone. But yes, we need a best guide for best results. Our tutorials are designed in step-by-step manner to help every kind of excel user from newbie to professional and from America to East-Asia. Moreover, they are online and Free.

Click here to access them.

---

Books

If you talk about our experience then we must say books are far better than any online material. If you’re really serious about learning VBA, VSTO or Excel/Access 2007 then we’ve searched and collected some books for our readers. They’ll help you a lot.

and yes the first link on this Books page is MS Office 2007 Home and Student edition in its cheapest price only through our website. Always use Genuine products, since purchasing a product provides you with support and keeps you away from hassles. Click here to access books.

---

Free Downloads

We keep on researching and developing the best excel products and add-ins for you. Moreover they’re free. Please explore our downloads section and you’ll find Stock Trackers for almost major world exchanges, Open XL, EMI Calculator, Budget Planner, CPI calculator and much more . . .

We do not warrant that downloads and examples will meet your requirements or that their operations will be error free but if you report us, we may help you out in removing errors, if any or will improve it to meet your requirements. Please report enhancements/bugs to our Forums. Click here to explore our downloads section.

---

Call a Trainer

If you’re looking to learn excel in step-by-step manner, we’re here to help you. Our professional tutors are Microsoft Certified developers and users and are in the field of training for 3 years (on average).

We deal in every level of training for your every need. i.e. from Beginner to advanced level of training for Excel, VBA and VSTO.

Click here to Call a Trainer.

---

About me

In India, there is saying “If we know us, we’ll get Nirvana (free from the cycle of birth and death)”. So, the one who is still searching about him, can tell you what about himself?

But if you still wanna know, who is the author of http://www.excelitems.com, please visit this link.

---

VBA Macro Codes Examples Collection

---

Visual Basic for Automation (Macros Collection)

---

Workbook

Worksheets

1. Sort all sheets
2. Remove blank worksheets from Current workbook
3. Color all Sheet Tabs
4. Remove color from all sheet tabs
5. Protect all worksheets
6. Remove Password (Unprotect) all worksheets
7. Insert ‘n’ number of Blank Worksheets

Range

1. Color a range based on its percentage value

Select

1. Invert the Selection

Number


Rows/Columns

1. Delete Blank Rows
2. Remove Empty Columns

Text

1. Change Case (Upper, Lower, Proper, Sentence and Toggle)
   
2. Remove Extra Spaces (Left, Right, Both sides and from Inside)

Format

1. Partly Background Color a Cell (Coloring only a portion of Cell)

Form

1. Resize Form during Run-Time

Event Macro

1. Example of Worksheet_Change Event Macro

Miscellaneous

1. Access Internet Explorer (Search Engine Example)
2. Change Image Location
3. Copy Excel Worksheets to MS Word

 

---

Use of Random Function (Rnd) and Select Case statement

---

Brief Description:
Here, I present the code to use random function in association with the Select Case statement. It use random function to generate a random value of S, H, D, or C for the four card suits (spades hearts diamonds or clubs). When user clicks the button, an input box pops up to accepts a value of S, H, D, C If the guess matches the random number respond with “Good guess”, if not then it respond with “Bad Guess”.

View VBA Macro Code Here

---

Delete or Remove Event Macros, VBA Code and Module

---

Brief Description: 

This macro will delete all event macros, sub routines and user defined functions from Active workbook. This requires reference to “Microsoft Visual Basic for Applications Extensibility 5.3” and trusted access to VBA project model.(Developer –> Macro security –> Trust access to VBA project model).

View VBA Macro Code Here

---

Close all saved workbooks

---

Brief: Imagine you’re working on some important excel files and gradually your taskbar is full of open workbooks. Some have similar names (not same ofcourse) and now you want to close all the saved workbooks and want unsaved workbooks to remain open. This macro will definitely solve your purpose.

View VBA Macro Code Here

---

Force Save and Unsave of Workbooks

---

Brief: Sometimes event macros are of great help. These two macros are example of that. While one macro protects a workbook from saving any changes, other macro force changes to be saved, if the former macro is present in the workbook. Both can be considered hacks of excel programming or a trick, but they’re always useful and as an excel programmer you should know these two fundas.

Macro #1: Make Workbook unacceptable to any changes.
Pre-requisite: Event Programming, VBA and Excel
Logic: Use of Workbook’s Saved property and Workbook’s BeforeSave event. As soon as excel encounter any changes, it changes Saved property value to FALSE so when you attempt to save the workbook, excel checks this property value and save the workbook, if it founds to be FALSE but if it is TRUE, then it ignores the operation.
Now we use BeforeSave event and turn this property value to TRUE everytime BeforeSave event fired. So this makes workbook immune to any changes.
Code:
‘PLACE THIS CODE IN THISWORKBOOK, NOT IN SHEETS OR MODULE
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
‘CHEATING APPLICATION BY FORCING SAVED PROPERTY TO BE TRUE
    ThisWorkbook.Saved = True
End Sub

Macro #2. Force changes to be saved in such workbooks (as mentioned above).
Pre-requisite: Event Programming, VBA and Excel
Logic: Disable Events, Save the workbook and then enable events again. Yes, it is that simple and here is its code.
Code:
 Sub ForceSave()
    On Error Resume Next
‘THIS WILL DISABLE ALL EVENTS INCLUDING Workbook_BeforeSave.
    Application.EnableEvents = False
‘SAVE THE WORKBOOK
    ActiveWorkbook.Save
‘ENABLE THE EVENTS AGAIN
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Note: You fill find workbooks with Macro#1 generally VBE password protected, in such case if you want to use Macro#2, either write it in Personal workbook and assign a shortcut key or assign it to a toolbar button.

---

How to Embed Flash in Excel (Office Applications)

---

Query Source : Excel Macros Google Group
Solution Type : Flash and MS Excel
Query by        : Mahesh Parab
Solution by     : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)

---

Query / Problem:

Dear All, Any one know the way how to embed a flash project into Excel ?

---

Solution:

1. Select the sheet in which you want to embed the flash project.

2. Go to View -> Toolbars -> Control Toolbox and click on the icon (More Controls in the last) which looks like a little hammer.

3. Select “Shockwave Flash Object” from the alphabetical list.

4. Now your cursor has been reformed into the plus shape, this allows you to create a rectangle shape on the sheet to make space for your Flash Object.

5. Right click on the cross-shaped rectangle box object and click on properties.

6. In the properties box, double click the first option. i.e.Custom

7. Now do the following options as suggested in the “Property Pages” dialog box.
    i.   Provide the Movie URL
    ii.  Check the “Embed Movie” checkbox.
    iii. Check the “Play” checkbox.

8. Click Apply and then Ok.

9. Close the properties box and Control Toolbox.

---

Invert the Selection

---

Description: This will deselect the selected and will select the deselected range in Used range of Active sheet.

View VBA Macro Code Here

---

UnProtect all WorkSheets in the WorkBook

---

Description: This macro will protect all the worksheets of the Active workbook with the password you will provide to them.
View VBA Macro Code Here

---

Protect all WorkSheets in the WorkBook

---

Description: This macro will protect all the worksheets of the Active workbook with the password you will provide to them.
View VBA Macro Code Here

---