Home

Blogs

Vishesh's picture

Want to automate report - (Solution)

Submitted by Vishesh on 2 March, 2011 - 15:22

Solution for problem asked on

 http://excelexperts.com/node/1093

»
Vishesh's picture

Create and Remove Hyperlinks (from VBA)

Submitted by Vishesh on 26 February, 2011 - 21:41
Often you have to create and remove hyperlinks at run time. Just paste the following code in a general module: 
Sub 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 picture

Check if exists in List

Submitted by Vishesh on 26 February, 2011 - 21:25
This utility demonstrates how you can use the match function to find a string even in a listbox or a dropdown. 
Sub 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 picture

Assign Macros to Shapes

Submitted by Vishesh on 26 February, 2011 - 21:07

This small utility shows how to assign macros to all the shapes in one go. See the code in module modAssignMacros

 

Assign Macros

»
Vishesh's picture

Form Control vs ActiveX Control

Submitted by Vishesh on 26 February, 2011 - 20:41

In 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

 

»

Facebook Texas Holdem Poker Download

Submitted by Tex1 on 12 February, 2011 - 19:26
»
Vishesh's picture

Zip Files from Excel

Submitted by Vishesh on 11 February, 2011 - 14:51
Copy the following code in a general module and call the TestRun function with the file paths and name as per you requirement.
»
Vishesh's picture

Customised Cell Comments

Submitted by Vishesh on 5 February, 2011 - 15:37
This is a small piece of code showing how to customise your cell comments to make it look better. The code works fine in Excel 2003.
»
Vishesh's picture

Create a blank Access Database from Excel

Submitted by Vishesh on 5 February, 2011 - 14:20
Following is a small piece of code to create a blank Access Database.
»
Vishesh's picture

Record Keeping from Excel to Access

Submitted by Vishesh on 5 February, 2011 - 13:58

 This 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 picture

Multi-Level Sort

Submitted by Vishesh on 31 January, 2011 - 09:38

This 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.

 

Multi-Level Sort

»
Vishesh's picture

Quick Immediate Window Commands useful for debugging

Submitted by Vishesh on 30 January, 2011 - 15:10

1. 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 picture

Get Values from other workbook without opening

Submitted by Vishesh on 29 January, 2011 - 20:47

You can use the following piece of code to get values into an opened workbook from a closed workbook.

 

»
Vishesh's picture

Break External Links with other workbooks

Submitted by Vishesh on 29 January, 2011 - 20:37

At 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.

External Links Exist

 

»
Vishesh's picture

Color Chart Series Utility

Submitted by Vishesh on 29 January, 2011 - 20:23

 Download 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.

Colour Chart Series

»
Vishesh's picture

Custom Number Formats

Submitted by Vishesh on 28 January, 2011 - 16:41

Each 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 picture

Chart Zoom(er)

Submitted by Vishesh on 27 January, 2011 - 17:27

Chart-Zoomer

 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 picture

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 picture

Difference of two Ranges

Submitted by Vishesh on 27 January, 2011 - 04:07

This 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 picture

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 picture

Lotus Email

Submitted by Vishesh on 24 December, 2010 - 09:55
Finally its here...Copy the following code in a general module and call SendEmail function. It returns 0 if successfull else error number is returned. 
Public 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 picture

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 picture

Align Shapes using VBA

Submitted by Vishesh on 21 December, 2010 - 07:43 
'Following is the code to align 2 shapes.
»
Vishesh's picture

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 picture

Outlook Email

Submitted by Vishesh on 21 December, 2010 - 03:46

Copy the following code in a general module and call SendEmail function. It returns 0 if successfull else error number is returned.

»
Vishesh's picture

Read from Access Table into Excel

Submitted by Vishesh on 16 December, 2010 - 15:23
Following is a very simple piece of code to import an Access Table into Excel.

- Run the procedure ReadFromAccess.

- Copy the complete code given below:

»

Move information from one column (automatically) to another

Submitted by wannabe on 4 December, 2010 - 23:37

I 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.

»

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.

»
Vishesh's picture

Changing Data in Image (Its Different). No VBA coding

Submitted by Vishesh on 18 September, 2010 - 20:43

This 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.

Dynamic Image

»
Vishesh's picture

Word Count in Cell using Split

Submitted by Vishesh on 18 September, 2010 - 20:15

This, 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
 

 

Word Count using Split

»
Syndicate content