Excel Experts Update
Chart Event (Double Click on Chart)
The example file attached shows how to create events for charts. Here, I have created double click event for charts. The chart which is double-clicked is exported to PowerPoint. Download the example file and have a look for yourself. See the code in Thisworkbook and mod_PPT module.
Types of Message Boxes (Simple ones)
Please see attached xl sheet to get an understanding of some simple message boxes. Feel free to let me know if you want something on other types of message boxes as well...will try and upload that...
Here is the code that's there in the sheet as well.
Option Explicit
Sub MsgInfo()
MsgBox "This is a info msg", vbInformation, "Info Message"
End Sub
Sub MsgErr()
Sorting ListBox Items alphabetically
This is the simple code to sort ListBox items alphabetically. Alternatively, you can download the file and test run it for yourself. Just pass the listbox object as function parameter.
Option Explicit
Sub SortListBox(lst As MSForms.ListBox)
Dim arrItems As Variant
Dim arrTemp As Variant
Dim intOuter As Long
Export Excel Charts to PowerPoint
Here is the code to export all charts in a sheet to powerpoint. Just pass the worksheet object as function parameter and it will export all charts in it onto Powerpoint. Alternatively, you can also download the attached file and see how the code works.
Option Explicit
Function getPPPres() As PowerPoint.Presentation
Dim PPApp As PowerPoint.Application
'Reference instance of PowerPoint
On Error Resume Next
Creating a low cost GIS using Excel and Depiction
Geographic Information Systems (GIS) can be powerful tools for making sense of information, but building a full system has, in the past, been an expensive and technically demanding process, reserved for large corporations or those with significant technical training. However, new tools--and old ones--are beginning to change that.
Add Custom Options to Right Click Menu
Often when using Excel, you want the ability to call a macro, but don't want to display a button. Adding Custom Options to Right Click Menu gives you this functionality. Create the data as in the first sheet of the attached xl file and copy the following code in Thisworkbook module. Right click to see that your menus appear in the right click menu list. There is also an option to specify whether to show 'Begin Group' separator line or not.
Creation Date of Excel File
There are two ways to get the creation date of a file...one using the filesystem object for any file (opened or not) and other is through workbook's buitin properties. Paste the following in a general module of a workbook test run it by running the ExecuteFunc procedure in the same module. Alternatively, you can download the attachment file.
File Manipulation from VBA
Following are some of the functions that you can frequently use to copy, move and rename your file from VBA. You can either copy the following code directly in a module or alternatively download the attached file. For renaming a file there are two methods given for this...Try this out!
Using Instr with Optional Backward Search
Below code can be used to find the starting position of a string in a text from start or can be used to search backward as well.
Start Position of a Nth Instance of String in a piece of Text
Code below returns the Starting position of Nth Instance of a string in a piece of text. Just paste the below code in the code module and run the 'TestIt' procedure. Alternatively download the attached file and run the same procedure from there from the module 'mod_NthInstance'.
Option Compare Text
Option Explicit
Function lngStartPosition(strSearchIn As String, strSearchString As String, lngInstance As Long)
Customised OnKey with example
Download the attached example file. Goto the module 'mod_OnKey' and run the procedure named 'Testit'.
Alternatively, paste the following code in any module and call it from anywhere in the workbook to set application onkey.
Sub setAppOnkey(blnShiftKey As Boolean, blnCtrlKey As Boolean, blnAltKey As Boolean, strKey As String, strCallFunction As String, Optional blnSetNormal As Boolean)
Dim strShift As String
Dim strCtrl As String
Check if a worksheet already exists in a workbook
Before adding a new sheet if you want to check if a sheet with the same name already exists then use the following function procedure. Download the attachment try the TestIt function in it in the module named 'mod_WorksheetExists'.
Paste the following code in a general module and use it from anywhere in the workbook.
Function blnWorksheetExists(strWorksheet As String) As Boolean
Dim wksWorksheet As Worksheet
On Error Resume Next
Set wksWorksheet = ThisWorkbook.Worksheets(strWorksheet)
Extract Nth Word from a String
Function GetNthWord(strStringFrom As String, strSplitCharacter As String, intExctractWordNumber As Integer) As String
On Error Resume Next
GetNthWord = VBA.Split(strStringFrom, strSplitCharacter)(intExctractWordNumber - 1)
If Err.Number <> 0 Then
GetNthWord = ""
End If
On Error GoTo 0
End Function
ShiSoft-India
Hello All,

Recent comments
3 days 14 hours ago
3 days 14 hours ago
1 week 11 hours ago
1 week 2 days ago
1 week 3 days ago
2 weeks 3 days ago
3 weeks 6 days ago
3 weeks 6 days ago
4 weeks 2 days ago
4 weeks 2 days ago