Solution for problem on url:

http://www.excelexperts.com/node/1144

Please download the attachment

Solution for problem on url
http://www.excelexperts.com/node/1139

For the user defined function used in the sample file check its Module.

Solution to problem given posted on url.

http://excelexperts.com/node/1137

This is a small piece of code that can be used to concatenate the elements of an array.

You can use it on elements of a listbox or combobox. See the attached file for reference.

Function ConcatenateArrayElements(arrElement As Variant, Optional  _ 
strConcatenator As String = ", ") As String
 
 ConcatenateArrayElements = Join(arrElement, strConcatenator)
 
End Function

The SUMIF function can be used to sum a range on cells based on certain criteria.

With Excel 2007, Microsoft introduced another addition to SUMIF i.e. SUMIFS  that allows you to pass more than one criteria. Both SUMIF and SUMIFS  have been explained in the attached workbook. Try changing the values in yellow cells and see how the result under sales column changes...

 Solution to problem asked on

http://excelexperts.com/node/1113

I have provided a button on the click of which your required output is generated.

Using the following piece of code you can create an Access table specifying the fields and types of your choice. You don't need to have Access on your system.

Here is the small piece of code that unzips file into a target folder. Just use the following code procedure with your own parameter values.

All shapes (including Charts) in Excel have AlternativeText property. This property can be used to store some information as well. Following is the code to read and write to AlternativeText property of a shape.

Sub SetAlternativeText()
 
    Sheet1.Shapes("Rectangle 1").AlternativeText = "ABC"
 
End Sub
 
 
 
Sub GetAlternativeText()
 
    MsgBox Sheet1.Shapes("Rectangle 1").AlternativeText
 
End Sub

Here are a set of commands that you can use to set excel environment.

This is how you can create a Custom Property in an Excel Workbook to save information. Go to File->Property  (as in pic).

Here is the code to read and write to the Custom Property.

Sub SetProperty()
 
    ThisWorkbook.CustomDocumentProperties("EmpName") = "Vishesh"
 
End Sub
 
 
Sub GetProperty()
 
    MsgBox ThisWorkbook.CustomDocumentProperties("EmpName").Value
 
End Sub

Solution for problem asked on

 http://excelexperts.com/node/1093

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

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

 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

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.

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

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

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.