How Do I... in Excel

This topic links to programming tasks (how-to and walkthrough topics) for common Excel scenarios.

"How Do I ..." is your gateway to key task-based topics about programming and application development using Microsoft Office Excel 2007. The essential categories for what you can do with Excel are listed in this topic. The links provide pointers to important, procedure-based Help topics.

Workbooks and Worksheets

How to: Create a Workbook
Describes how to create a workbook using Excel 2007.
How to: Refer to More Than One Sheet
Describes how to refer to more than one worksheet in Excel 2007.
How to: Refer to Sheets by Index Number
Describes how to refer to worksheets by index number.
How to: Refer to Sheets by Name
Describes how to refer to worksheets by name.

Cells and Ranges

How to: Reference Cells and Ranges
Describes how to reference cells and ranges in order to do enter formulas or change formats.
How to: Refer to All the Cells on the Worksheet
Describes how to refer to all cells on a worksheet.
How to: Refer to Cells and Ranges by Using A1 Notation
Describes how to refer to cells and ranges by using A1 notation.
How to: Refer to Cells by Using a Range Object
Describes how to refer to cells by using a Range object.
How to: Refer to Cells by Using Index Numbers
Describes how to refer to cells by using index numbers.
How to: Refer to Cells by Using Shortcut Notation
Describes how to refer to cells by using shortcut notation.
How to: Refer to Cells Relative to Other Cells
Describes how to refer to cells relative to other cells.
How to: Refer to Multiple Ranges
Describes how to refer to multiple ranges.
How to: Refer to Named Ranges
Describes how to refer to named ranges.
How to: Refer to Rows and Columns
Describes how to refer to rows and columns in Excel.

Controls, Dialog Boxes, and Forms

How to: Add Controls to a Document
Describes how to add controls to a document.
How to: Add Controls to a User Form
Describes how to add controls to a user form.
How to: Create a Custom Dialog Box
Describes how to create a custom dialog box.
How to: Create a User Form
Describes how to create a user form.

See Also

How Do I... in Excel




Community Content

Thomas Lee
Custom Right Click does not work in all columns
Hi All:

I am trying to write a custom right click function on cells (Excel 2007) using:

Application.CommandBars["Cell"].Controls["SomeControl"].Add(MsoControlButton, missing, missing, missing, true);

It looks like it creates the right click fine however there is some complications. I copied worksheets from different workbook to the activeworkbook (programmatically). Just for discussion purposes, as a result I have one sheet with two columns on the worksheet that has just been copied.

When I tried the right click, the menu only appears on the column other than the columns that just been copied. Is there property that I need to setup other than setting the caption and the handler to get it appears on all cell? I tried the Application.CommandBars.Reset() and Enabled = true before adding the right click, but that does not seem to help it.

Please help or let me know if you need any clarification.

Thank you,
Sandi

I got a fix for this issue please refer to:
http://social.technet.microsoft.com/Forums/en-US/office2010general/thread/6a65f2eb-b695-48f5-9372-cf2b5f67d10f/
Tags : excel 2007

Thomas Lee
Plot Latitude and Longitude in a graph to scale
Attempting to plot Latitude and Longitude in a scatter graph to scale, got the format to show degrees minutes seconds
but when plotting on the table it is not to scale. Plotted the same points mapquest to compare chart in excel.

Any help would be appreciated

[tfl - 02 08 09] Hi - and thanks for your post. You should post questions like this to the MSDN Forums at http://forums.microsoft.com/msdn or the MSDN Newsgroups at http://www.microsoft.com/communities/newsgroups/en-us/. You are much more likely get a quicker response using the forums than through the Community Content. For specific help about:
Visual Studio : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.vstudio%2C&
SQL Server : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.sqlserver%2C&
.NET Framework : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public.dotnet.framework
PowerShell : http://groups.google.com/group/microsoft.public.windows.powershell/topics?pli=1
All Public : http://groups.google.com/groups/dir?sel=usenet%3Dmicrosoft.public%2C&

PhilJC1952
Excel 2007 Custom Toolbar Changes
Some time back, I developed a spreadsheet in Excel 2003 and provided a custom toolbar, which I attached to the spreadsheet, to launch macros. Client was using Excel 2007 so subsequent development was done there.
Since then I have added macros and searched for ways to add buttons to the custom toolbar. I have found nothing. I also looked at whether I could set up a custom ribbon but the only information I found said ribbon can't be used to launch macros.

Is there a way to do either? It's pretty ugly adding Macros to the Quick Launch bar.

Phil
Tags : excel 2007

Clarkyboy420
VBA/ADO Problems accessing Memo datatypes from Excel
Hi All,

I've been working on a problem for some time now involving VBA Extraction from Access into Excel, using VBA and ADO.

I have found dozens of different means and methods of pulling the data from my Access db into an Excel reporting sheet, but have been consistently been receiving #VALUE results.

Extensive testing has finally pinpointed the problem as the Memofield datatype. I have proved the theory to myself beyond any doubt, unfortunately it was the last thing I looked at. By changing my datatype to Text field, every extraction method I tried works perfectly.

Unfortunately, this is an Software Bug/Incident reporting system I have created and the description of the Incident in 99/100 cases is a lot larger than the 255 character limit of a text datatype. My Director requires an automated reporting sheet, which I have implemented, but the VBA/MemoField issue means I cannot provide this report with the description and he absolutely requires that information for him to use the report effectively.

This brings up the following question please;

1) Has anyone experienced this problem themselves and found any workaround/resolution? I've done a lot of research and have seen people with issues where VBA only pulls c1800 characters from the Memo field. Frankly, I would be happy to get that many characters, since I get nothing back from any table's fields, if even a single memo field is part of my VBA SQL SELECT statement. If I remove the Memo field from the VBA SELECT statement, everything works perfectly.

If I can't get this operating in this manner, then my report is useless. My Director does not wish to use Access queries or Reports as he's an Excel Junkie. He has also stated that it's impractical for me to write queries and export into Excel as he wishes the information to be available any time of the day at the touch of a button. Therefore, unless he takes me everywhere around the world with him in a Jack-In-The-Box, this is currently impossible...

FYI Running 2007 Office Professional versions of Access, Excel and VBA/ADO.

Many Thanks,

Chris Clark

I would greatly appreciate any help or advice on this issue.
Tags :

Page view tracker