Blogs

Want to automate report - (Solution)
Submitted by Vishesh on 2 March, 2011 - 15:22Solution for problem asked on
- Vishesh's blog
- 2 comments
- 6336 reads

Create and Remove Hyperlinks (from VBA)
Submitted by Vishesh on 26 February, 2011 - 21:41Sub Create() Call CreateHyperlink(Sheet1.Range("A1:A10")) End Sub Sub Delete() Call RemoveHyperlink(Sheet1.Range("A1:A10")) End Sub Sub CreateHyperlink(rng As Range) Dim rngCell As Range For Each rngCell In rng rngCell.Hyperlinks.Add rngCell, "", , "Screen_Tip", rngCell.Value Next rngCell End Sub Sub RemoveHyperlink(rng As Range) rng.Hyperlinks.Delete End Sub
- Vishesh's blog
- Login or register to post comments
- 19228 reads

Check if exists in List
Submitted by Vishesh on 26 February, 2011 - 21:25Sub Check() If blnExists(Sheet1.Range("C13").Value, Sheet1.ListBoxes("List Box 1")) = True Then MsgBox "Exists" Else MsgBox "Does not exists" End If End Sub Function blnExists(strFind As String, lst As ListBox) As Boolean On Error Resume Next blnExists = (Application.WorksheetFunction.Match(strFind, lst.List, 0) > 0) If Err.Number > 0 Then blnExists = False On Error GoTo 0 End Function </
- Vishesh's blog
- Login or register to post comments
- Read more
- 29510 reads

Assign Macros to Shapes
Submitted by Vishesh on 26 February, 2011 - 21:07This small utility shows how to assign macros to all the shapes in one go. See the code in module modAssignMacros
- Vishesh's blog
- Login or register to post comments
- 9270 reads

Form Control vs ActiveX Control
Submitted by Vishesh on 26 February, 2011 - 20:41In Excel 2003
to view Form Controls toolbar: Menu: View->Toolbar->Forms
to view ActiveX toolbar: Menu: View->Toolbar->Control Toolbox
Draw a listbox from each of the toolbars
Form Control:
1. The code is always written in a general module.
2. Right Click the control and using assign macro option choose the right procedure that you want to call on the click/change in that control
To see code for form control goto FormControl module
- Vishesh's blog
- Login or register to post comments
- Read more
- 45418 reads
Facebook Texas Holdem Poker Download
Submitted by Tex1 on 12 February, 2011 - 19:26- Tex1's blog
- 2 comments
- 8629 reads

Zip Files from Excel
Submitted by Vishesh on 11 February, 2011 - 14:51- Vishesh's blog
- 15 comments
- Read more
- 99050 reads

Customised Cell Comments
Submitted by Vishesh on 5 February, 2011 - 15:37- Vishesh's blog
- Login or register to post comments
- Read more
- 7541 reads

Create a blank Access Database from Excel
Submitted by Vishesh on 5 February, 2011 - 14:20- Vishesh's blog
- 1 comment
- Read more
- 16158 reads

Record Keeping from Excel to Access
Submitted by Vishesh on 5 February, 2011 - 13:58This is a small utility to show how to use Access as a backend to the Excel interface. Using this utility you can enter data in Excel which is then saved in Access. The use of Access makes the data sharing easier.
Access Database Details used in the example (Create an access database with same details):
Database name: Trade_DB
Table Name: TRADING_DETAILS
Field 1: TradeNumber ; Type: Number
Field 2: Trader ; Type: Text
- Vishesh's blog
- 2 comments
- Read more
- 22360 reads

Multi-Level Sort
Submitted by Vishesh on 31 January, 2011 - 09:38This utility works with Excel 2007 version only as Excel 2003 doesn't allow for more than 3-level sorting. Try and use it. Its very useful and easy to use and implement in your own projects.
- Vishesh's blog
- Login or register to post comments
- 12685 reads

Quick Immediate Window Commands useful for debugging
Submitted by Vishesh on 30 January, 2011 - 15:101. When you have a form open and you encounter an error and you want to go to the excel sheet
You need to hide the form first before you can click on the sheet
Me.Hide
2. If you want to activate a sheet other than the current activesheet its better to use sheet code name than the names which are displayed in sheet tabs
Sheet3.Activate
3. To display/hide sheet tabs
ActiveWindow.DisplayWorkbookTabs = True
4. If you are working on more than one workbooks, then to activate a particular workbook
- Vishesh's blog
- Login or register to post comments
- Read more
- 18989 reads

Get Values from other workbook without opening
Submitted by Vishesh on 29 January, 2011 - 20:47You can use the following piece of code to get values into an opened workbook from a closed workbook.
- Vishesh's blog
- 1 comment
- Read more
- 16654 reads

Break External Links with other workbooks
Submitted by Vishesh on 29 January, 2011 - 20:37At times you come across the this dialog box when you open an Excel workbook. To get rid of this you can run a small piece of code just once. This code would break all links.
- Vishesh's blog
- Login or register to post comments
- Read more
- 12764 reads

Color Chart Series Utility
Submitted by Vishesh on 29 January, 2011 - 20:23Download the attached Excel file to see the code behind it and how this utility works. Its makes colouring of chart series easier in a very user-friendly way.
- Vishesh's blog
- 1 comment
- Read more
- 7572 reads

Custom Number Formats
Submitted by Vishesh on 28 January, 2011 - 16:41Each cell in Excel has a format which is divided into four Sections
from left to right:
- Positive numbers
- Negative Numbers
- Zero Values
- Text values
... each separated by a semi colon (;).
- Vishesh's blog
- Login or register to post comments
- Read more
- 9552 reads

Chart Zoom(er)
Submitted by Vishesh on 27 January, 2011 - 17:27
Download the attached Excel file to see how the Chart Zoomer works. Click on the chart to Zoom and click again to size it back to normal.
- Vishesh's blog
- 6 comments
- 19839 reads

Show Blank Chart or 'No Data' on Empty chart
Submitted by Vishesh on 27 January, 2011 - 09:59'Copy the following code in a general module and call TestRun function. Alternatively you can download the attachment and test run it.
- Vishesh's blog
- Login or register to post comments
- Read more
- 13667 reads

Difference of two Ranges
Submitted by Vishesh on 27 January, 2011 - 04:07This function gives the difference of two ranges (output is range). It is opposite of union of ranges. Its NOT intersection either.
Copy the following code in a general module and call the TestRun function with required ranges to see how it works.
'--------------------------
- Vishesh's blog
- Login or register to post comments
- Read more
- 12958 reads

Find All Occurrences of string in range
Submitted by Vishesh on 24 December, 2010 - 16:01'The following code finds all occurrences of a string in a given range. Just call the following function with the required parameters - string to find and range to find in. It returns a range.
- Vishesh's blog
- 2 comments
- Read more
- 29252 reads

Lotus Email
Submitted by Vishesh on 24 December, 2010 - 09:55Public Function SendEmail(strMailTo As String, strSubject As String, strBodyText _ As String, blnReceipt As Boolean, Optional strAttachment As String, Optional _ strCCTo As String, Optional strBCCTo As String) As Long Dim objApp As Object Dim objMail As Object Dim objLotusDB As Object Dim objLotusItem As Object Dim arrAttachment() A
- Vishesh's blog
- 8 comments
- Read more
- 15589 reads

Compact Access Database
Submitted by Vishesh on 22 December, 2010 - 08:33'Copy the following code in a general module and call it with required parameters. 'This function returns 0 is successful else error number is returned Option Explicit Function CompactDB_JRO(strDBPath As String, Optional strDBPass As String = "") As Long On Error GoTo ErrFailed 'Delete the existing temp database If Len(Dir$(strDBPath & ".tmp")) Then VBA.Kill strDBPath & ".tmp" End If With CreateObject("JRO.JetEngine") If strDBPass = "" Then 'DB without password .CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
- Vishesh's blog
- 2 comments
- Read more
- 16506 reads

Align Shapes using VBA
Submitted by Vishesh on 21 December, 2010 - 07:43'Following is the code to align 2 shapes.
- Vishesh's blog
- 1 comment
- Read more
- 17177 reads

Export Excel Range to Access
Submitted by Vishesh on 21 December, 2010 - 07:29'Following is a very simple piece of code to Export an Excel range to Access.
'Run the procedure ExportToAccess.
- Vishesh's blog
- Login or register to post comments
- Read more
- 20039 reads

Outlook Email
Submitted by Vishesh on 21 December, 2010 - 03:46Copy the following code in a general module and call SendEmail function. It returns 0 if successfull else error number is returned.
- Vishesh's blog
- 1 comment
- Read more
- 16108 reads

Read from Access Table into Excel
Submitted by Vishesh on 16 December, 2010 - 15:23- Run the procedure ReadFromAccess.
- Copy the complete code given below:
- Vishesh's blog
- Login or register to post comments
- Read more
- 20769 reads
Move information from one column (automatically) to another
Submitted by wannabe on 4 December, 2010 - 23:37I have a row of names, with numerical data in 10 ajoining rows (name in row A, with numerical date in rows B-K). I want to automatically move these entries, from rows A-K to matching names in row M along with the corresponding numerical data. There are single entry names in Row A, with numerous corresponding names in Row M. Very time consuming to individually copy and past.
Appreciate any help you can provide.
- wannabe's blog
- 2 comments
- 7141 reads
Automated email from excel / outlook
Submitted by jawed_h777 on 11 October, 2010 - 15:25
I have a spreadsheet, which we maintain for work allocation for our team, we have a 24 hours Turn around Time (TAT) for the work to processed. I want an automated email to be sent through excel once a particular work has exceeded the 24 hours TAT.
This spreadsheet is a shared file, as we all need to update it.
Please advice.
- jawed_h777's blog
- 1 comment
- 8229 reads

Changing Data in Image (Its Different). No VBA coding
Submitted by Vishesh on 18 September, 2010 - 20:43This can be a unique way of displaying dynamic data in an image without actually playing with the image. Only change the data and the image would reflect the changes including the formatting changes in the cells. Download the attachment and see the steps required.
- Vishesh's blog
- 3 comments
- 9228 reads

Word Count in Cell using Split
Submitted by Vishesh on 18 September, 2010 - 20:15This, I think, is the simplest of code to find the word count in a cell using the Split function. Its a single line code but very useful.
Download the attachment file to see how this is used as user defined function in excel and as a formula.
Function intWordCount(rng As Range) As Integer intWordCount = UBound(Split(rng.Value, " "), 1) + 1 End Function
- Vishesh's blog
- Login or register to post comments
- 14652 reads

Recent comments
5 years 50 weeks ago
6 years 36 weeks ago
6 years 47 weeks ago
6 years 50 weeks ago
6 years 51 weeks ago
7 years 5 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago