Home > Excel Help Portal > Programming Excel

<<  (Page 10)  Previous              Next  (Page 12)  >>

Programming Excel

The ExcelUser portal offers links to help pages created by top
Excel web sites. This section discusses ways to program
Microsoft Excel using VBA.

501. How to Create a Template for a Toolbox Control The UserForm Toolbox in the Visual Basic Editor contains all of the default controls that you can add to a UserForm. You can also add other custom controls to the default Toolbox . For example, you can add a type of control that is a template for... KB#213770. Keywords: Fonts, Command Button, UserForm, VBA, Templates, Window

 
502. A macro to control Word from Excel Thanks to Jake who provided this week's question: How can I write a macro which will take Excel spreadsheet data and create a Word file for each row of data. Jake - what a great idea! I often have sales results for the entire company and it would be great to be able to send each rep just his or her... Keywords: Sales, Clipboard, VBA, Macro, Object, File Name, Templates, Web

 
503. How to Programmatically Find the Nth Word in a Text String In Microsoft Excel, you can create a Microsoft Visual Basic for Applications function that can extract a specific word from a text string. This article describes how to create and use a custom function to find a specific word in a text... KB#213268. Keywords: Formulas, VBA, Web

 
504. Use the Operation choices in Paste Special I was talking to Steve on the telephone the other day when I came up with the idea for this tip. Steve had a table of monthly sales figures. Because two product lines had been merged, Steve needed to add all of the sales figures from product D into the sales figures for product C. As I listened... Keywords: Days, Sales, Errors, Formulas, Clipboard, VBA, Web

 
505. How to Extract Information from Excel Sheet with DAO The Excel ISAM driver does not dynamically convert... KB#190195. Keywords: DAO, Defined Name, VBA

 
506. How to convert an Addin File into a Workbook in Excel 2000 This step-by-step article describes how to convert an add-in (.xla) file into a normal workbook (.xls) file that you can view and edit. Convert Add-in to Workbook To convert an add-in file back into a normal workbook, follow these steps: On the... KB#211561. Keywords: Add-In, Charts, Password, VBA, File Name, Files, Window

 
507. How to eliminate redundant data in an Excel list "Stuck in NJ" asked this week's question. I am creating a workbook that contains charts of sales figures. The first worksheet corresponds to the first sales territory in the company (eg. FL01.) The worksheet's tab name (as well as cell A1) is the sales territory number FL01. The data that drives... Keywords: Charts, Sales, Format, Errors, Printing, VBA, Macro, Web, VLOOKUP

 
508. How to Run a Macro When Certain Cells Change In Microsoft Excel, you can create a macro that is called only when a value is entered into a cell in a particular sheet or in any sheet that is currently open. Note, however, that you should not call macros unnecessarily because they slow down... KB#213612. Keywords: VBA, Macro, Web

 
509. Advanced Pivot Table Operations Thanks to Katherine who sent the question for this week's tip: I want to place a form button on a worksheet that, when clicked, will email the same worksheet to someone. To send the whole book to ask@mrexcel.com with a particular subject line, you would use this... Keywords: AutoFilter, Sorting, Sales, Command Button, VBA, Macro, Toolbar, Web, Comments

 
510. How to Control the Appearance of the Mouse Pointer While a Macro Runs In Microsoft Excel 2000, you can use the Microsoft Visual Basic for Applications Cursor property to control the appearance of the mouse pointer while a macro is running. In versions of Excel earlier than 7.0, you do not have the ability to change... KB#213424. Keywords: VBA, Macro, Web

 
511. Using a Worksheet Function in a Visual Basic Macro You can call most built-in Microsoft Excel worksheet functions directly from a Microsoft Visual Basic for Applications macro. This article describes how to use a built-in worksheet function in a... KB#213765. Keywords: Errors, Macro, Object, VBA, Web, ACOS

 
512. New Workbooks Open Repeatedly Starting Excel When you start Microsoft Excel, new workbooks may open... KB#242417. Keywords: Third-Party, Startup, VBA, Files, Web

 
513. How to Create a Microsoft Office Smart Tag List You can create custom smart tags for use in Microsoft Office XP by using Extensible Markup Language (XML). Smart tags created in this way are called Smart Tag Lists. These Smart Tag Lists can only recognize a static list of terms and make use... KB#287698. Keywords: AutoCorrect, DLL, XML, VBA, File Name, Smart Tag, Web

 
514. How to Use the SelectionChange Event to Make the Active Row Bold When you work in a Microsoft Excel worksheet, you may want to make the active item bold so that it is easier to read. This article contains a sample Microsoft Visual Basic for Applications macro that makes the font of the current row... KB#213193. Keywords: Fonts, Styles, VBA, Macro, Object, Web, Window

 
515. How to Populate an Array with a Discontiguous Range To populate an array using Microsoft Visual Basic for Applications in Microsoft Excel, you need to iterate through the range object to populate an array with the data if the range object is discontiguous (whether it is a selection, range name, or... KB#213797. Keywords: Arrays, VBA, Iteration, Macro, Object, Web, Discontiguous

 
516. Multi-Line Ascii Report You can easily customize this procedure to deal with any variety of ASCII reports. You need to figure out how many physical printed lines make up a single logical record on the report. MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed... Keywords: Sorting, Formulas, VBA, Web

 
517. Visual Basic Code to Use Instead of DIRECTORIES() This article contains sample Visual Basic for Applications code that you can use to duplicate the behavior of the DIRECTORIES() function that is included in the Microsoft Excel 4.0 Filefns.xla add-in. NOTE : The Filefns.xla add-in is not included... KB#213474. Keywords: Add-In, Query, Arrays, Errors, VBA, Macro, Web

 
518. How to Control the AutoFilter with a Macro In Microsoft Excel, you can use the AutoFilter feature to filter a list based on simple criteria. This article contains several sample Microsoft Visual Basic for Applications macros ( Sub procedures) for controlling the AutoFilter feature in... KB#213628. Keywords: AutoFilter, Criteria, VBA, Macro, Web

 
519. How to Create Unique Random Integers Between Two Numbers In Microsoft Excel, you can create a Microsoft Visual Basic Sub procedure (macro) to create a list of unique (non-repeating) random integers between two other numbers. This articles describes a sample macro that prompts the user for a starting... KB#213290. Keywords: VBA, Macro, Web

 
520. How to determine Office Document and Managed Code Extension Paths at Run Time This article describes how to programmatically determine the path of a Microsoft Office Word document or a Microsoft Office Excel workbook, and the path of the associated Microsoft .NET Framework assembly (managed code... KB#824000. Keywords: VBA

 
521. Removing the Control Menu and Application Window Controls In Microsoft Excel, you can create a Visual Basic for applications, macro to disable or remove the application window and worksheet... KB#117855. Keywords: Errors, VBA, Macro, Web, Window

 
522. Visual Basic Macro to Break Chart Links Charts in Microsoft Excel are, by default, linked to the range of cells on the worksheets that they were created from. You can break the links by converting the cell references to values in the SERIES formula for the chart. This article describes... KB#213443. Keywords: Charts, SERIES, Formulas, Links, VBA, Macro, Toolbar, Web

 
523. Macro to Loop Through All Worksheets in a Workbook This article contains a Microsoft Visual Basic for Applications macro ( Sub procedure) that loops through all the worksheets in the active workbook. This macro also displays the name of each... KB#213621. Keywords: VBA, Macro, Object, Web

 
524. How to Determine the Key Pressed Along with Mouse Button Visual Basic for Applications in Microsoft Excel 2000 incorporates many more events for activating macros. Some of the new events include MouseDown, MouseUp, KeyDown, and KeyUp. One of the arguments returned by these particular events, Shift,... KB#213736. Keywords: Command Button, VBA, Macro, UserForm, Web, Window

 
525. How to Use the TextColumn Property You can use the TextColumn property of the ListBox or ComboBox control to display one set of values to your user in a list, but to return another value based on the selection that the user... KB#213722. Keywords: Format, VBA, UserForm, Web, Window

 
526. How To Delete a Sub Procedure After It Runs Once This article demonstrates how to programmatically delete a Visual Basic for Applications procedure after you run it one... KB#172109. Keywords: VBA, Macro, Object, VBE, Toolbar, Web, Window

 
527. How to use Lotus 123 Commands to Attach Text Data Labels to a Chart in Excel 2000 This step-by-step article shows you how to use Lotus 1-2-3 commands to attach text data labels to a chart in Excel 2000. To create text data labels in Microsoft Excel 2000, you must attach them to data points by using the Attach Text command on... KB#214040. Keywords: Charts, SERIES, Styles, Macro, Object

 
528. How to Programmatically Reset a Workbook to Default Styles The following Microsoft Visual Basic for Applications Sub procedure removes all styles in a Microsoft Excel workbook and then adds back the default styles you see in a new workbook. This macro may be helpful for removing extra styles added to... KB#291321. Keywords: Styles, Startup, VBA, Macro, Templates, Web

 
529. How to Programmatically Change the Project Name Property By default, when you open a new workbook in Microsoft Excel 2000, the Name property for the workbook project is VBAProject (without the quotation marks). This naming convention may cause a problem if you manually or programmatically attempt... KB#213764. Keywords: Errors, VBA, Macro, Object, VBE, Error Message, Web, Window

 
530. How to Determine the Key Pressed Along with Mouse Button Visual Basic for Applications in Microsoft Excel 97 incorporates many more events for activating macros. Some of the new events include MouseDown, MouseUp, KeyDown, and KeyUp. One of the Arguments returned by these particular events, Shift,... KB#161903. Keywords: VBA, Macro, UserForm, Web

 
531. How to change connection information for a query in Excel After you change the folder location or server name of a database that a query or PivotTable is using, you cannot manually change the connection information to the new folder or server. This article offers a programmatic... KB#269619. Keywords: Query, Arrays, Errors, PivotTables, VBA, Web

 
532. BUG: StyleSheets Parameter of the OpenXML Method Ignored When Automating Excel When you call the OpenXML method, your code may be interrupted with the Excel Import XML dialog box. User intervention is required for your code to continue. This problem occurs when both of the following conditions are met: You are calling... KB#307230. Keywords: XML, Arrays, Printing, VBA, Macro, Object, Files, HTML

 
533. Creating Macros for Different Language Versions In Microsoft Excel 2000, you can determine the country code that corresponds to the version of Excel that you are running. These country codes can be helpful in creating custom... KB#213833. Keywords: VBA, Macro, Web

 
534. How to Create a Template for a Toolbox Control The UserForm Toolbox in the Visual Basic Editor contains all the default controls that you can add to a UserForm. You can add other custom controls that are installed on your computer. For example, you can add a third type of control that is... KB#160473. Keywords: Fonts, UserForm, VBA, Templates, Window

 
535. Write a Calendar File We have a sacred duty in our department; probably in yours. Somebody has to bring donuts to staff meeting on Friday. I was determined to find a way to generate Calendar Files that I could send to the interested party each week. In the end, I created a monster (that I will share with you. The... Keywords: Days, Holidays, Printing, VBA, Macro, Files, Web

 
536. How to Create Yes and No Buttons for a Custom Message Box By default, the MsgBox function in a Microsoft Visual Basic for Applications procedure contains a single OK button. However, you can display other buttons in the message box. This article provides a sample Visual Basic macro that displays a... KB#142142. Keywords: VBA, Macro, Web

 
537. How to Display the Open and Save As Dialog Boxes When you use Microsoft Excel, you may want to display the Open or the Save As dialog boxes using Microsoft Visual Basic for Applications procedures. This article explains several ways to accomplish this task. Note that the Open and Close methods... KB#139723. Keywords: Macro, VBA, File Name, Save A File, Toolbar, Web

 
538. How to Programmatically Insert Data from a List Box into the Active Cell This article describes a sample Microsoft Visual Basic for Applications Sub procedure (macro) that enters a selected item from a list box into the active cell on a... KB#213363. Keywords: Format, VBA, Macro, Object, Web

 
539. How to Copy the Text Within a Text Box to a Cell This article contains a sample Microsoft Visual Basic for Applications macro that copies the text within a text box and pastes it into a spreadsheet. After following the steps outlined in this article, you should see the text appear in the... KB#213793. Keywords: Text Box, VBA, Macro, Toolbar, Web

 
540. How to Determine If the Active Cell Contains a Comment Microsoft Excel 97 no longer uses cell notes; instead Excel 97 uses cell comments. Consequently, the macro code you use to determine whether a cell contains a comment is different. This article discusses ways to programmatically determine whether... KB#158246. Keywords: VBA, Macro, Object, Web, Comments

 
541. How to Modify the Number of Undo Levels By default Microsoft Excel keeps track of 16 undo levels, which provide an undo history for most actions you perform in Microsoft Excel. You can change the number of undo levels by adding an entry in the Microsoft Windows registry.... KB#211922. Keywords: Macro, VBA, Registry

 
542. How to Use FileSearch to Fill a Listbox with File Names In Microsoft Excel 97 for Windows, you can use the FileSearch object to locate files on a local or network drive. This article provides an example of how to use the FileSearch object in Microsoft Visual Basic for Applications to fill a list box... KB#155837. Keywords: Arrays, VBA, Macro, Object, UserForm, File Name, Files, Web

 
543. How to Sum a Range of Cells Based on a Number Format This article includes a sample Microsoft Visual Basic for Applications custom function that sums the values in a range of cells that are formatted with a specific custom number... KB#213728. Keywords: Custom Number Formats, VBA, Web

 
544. Registry Keys That Control the File Repair Feature Microsoft Excel 2002 contains a new feature that allows you to recover data from corrupted workbooks. This article describes how to use the registry to override the default behavior when Excel attempts to recover... KB#289840. Keywords: Formulas, Object, VBA, Corrupted, Registry

 
545. How to Create a GIF File from a Microsoft Excel Chart To programmatically create a graphics file from a Microsoft Excel chart, use the Export method. This article contains a sample Microsoft Visual Basic for Applications macro that creates a .gif file from a chart in a Microsoft Excel workbook. NOTE:... KB#163103. Keywords: Charts, Graphic, Export, VBA, Macro, Files, Toolbar, HTML, Web

 
546. Excel Hall of Fame Tushsar Meta's set of user defined function was nominated as the Answer of the Week. The Question: Lucas in London posed this question. He has a series of raw data in column A of his worksheet. He would like a function that would (a) calculate the percentage difference between each data point, then... Keywords: SERIES, Arrays, Formulas, VBA, Object, User Defined Function, Web

 
547. How to Programmatically Sum Only the Visible Cells in a Range In Microsoft Excel, you can use a Microsoft Visual Basic for Applications custom function to sum only the visible cells in a range. The SUM() worksheet function totals all the cells in a range, including hidden cells. The SUBTOTAL() worksheet... KB#213313. Keywords: Advanced Filter, AutoFilter, Format, Formulas, VBA, Web, SUBTOTAL

 
548. How to Make Sure That Only Numbers Are Entered in a Column This article contains a sample Microsoft Visual Basic for Applications macro (Sub procedure) that you can use to ensure that a certain type of data entry occurs. This macro requires a user to enter only numbers in a specific column on a... KB#141181. Keywords: Add-In, Errors, VBA, Macro, Web, Validation

 
549. How to Disable Shortcut Menus Delete Rename Move or Copy... Select All Sheets View Code... KB#161440. Keywords: Charts, Plot, SERIES, AutoShapes, Query, ActiveX, OLE, Formulas, PivotTables, VBA, Macro, Object, Web

 
550. How to Use a VBA Macro to Sum Only Visible Cells If you have a range of cells that includes hidden rows or hidden columns, Microsoft Excel does not provide an easy way to sum only the visible cells. The SUM function totals all the cells in a range, including hidden cells. The SUBTOTAL... KB#150363. Keywords: Advanced Filter, AutoFilter, Formulas, VBA, Macro, Toolbar, Web, SUBTOTAL

 

<< (Page 10)  Previous              Next  (Page 12) >>