Excel FAQ - Excel 101
Excel FAQ - Excel 101
Excel FAQ - Excel 101
Excel FAQ - Excel 101
Excel FAQ - Excel 101
Excel FAQ - Excel 101 Excel FAQ - Excel 101 Excel FAQ - Excel 101 Excel FAQ - Excel 101 Excel FAQ - Excel 101 Excel FAQ - Excel 101 Excel FAQ - Excel 101 Excel FAQ - Excel 101
Excel FAQ - Excel 101 Excel FAQ - Excel 101
Excel FAQ - Excel 101
Go Back  Xtreme Visual Basic Talk > > > > Excel FAQ - Excel 101


Closed Thread
 
Thread Tools Display Modes
  #1  
Old 09-12-2002, 02:57 PM
MillExcel FAQ - Excel 101 Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default Excel FAQ - Excel 101


I'm going to use this thread similar to the Tutorials forum, but it will all be about Excel, so I'm putting it here.

I'm mainly doing this because a lot of questions get asked over and over again, so, rather than retyping things, we can just link to this thread.

Table of Contents
Lesson 1 - The WONDERFUL Validation Menu Item

Lesson 2 - What's the last cell with data in a column?

Lesson 3 - Having items in one ComboBox filter items in another ComboBox

Lesson 4 - Same as Lesson 3, only actually using ComboBoxes

Lesson 5 - Allow the user to select a file or a directory

Lesson 6 - Public Functions

Lesson 7 - Find Errors in Formulas of a Sheet

Lesson 8 - Finding the Last Row in a Sheet

Lesson 9 - ScrollRow and ScrollColumn

Lesson 10 - Adding your own CommandBar

Lesson 11 - Adding your own menus

Lesson 12 - Comparing the contents of two Worksheets

Lesson 13 - A generic function to find and count specified values in a Range

Lesson 14 - How to harness the exposed events for the Application object

Additional Resources:
Everything you wanted to know about userforms but were afraid to ask
A glossary of other web-resources and handy publications
A great tutorial on everything about array formulas
How to Delete Rows Using Excel VBA
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey

Last edited by Colin Legg; 10-06-2008 at 05:28 PM.
  #2  
Old 09-12-2002, 03:03 PM
MillExcel FAQ - Excel 101 Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default Lesson 1 - The WONDERFUL Validation Menu Item

People often ask if there's an easy way to force users to limit what users enter into a cell.

It IS easy, if you goto Data->Validation.

Choosing that option lets you do the following:
  • Allow only whole numbers, with the option of forcing the entry to fall within a certain range
  • Allow decimal numbers between a certain range
  • Select an item from (or type an item in) a list. This works similar to a combobox, but without the control.
    The list must be on the same sheet as the validated cell, but the list can be in a hidden column.
    Edit: Apparently, the list can be from a different sheet if it is a named range. That sheet can be hidden.
  • A Date or Time between certain values
  • Text of a maximum and/or minimum length

Play with this. It's worth your time to get familiar with it.
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey

Last edited by Mill; 03-21-2003 at 02:16 PM.
  #3  
Old 09-12-2002, 03:11 PM
MillExcel FAQ - Excel 101 Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default Lesson 2 - What's the last cell with data in a column?

Here's an easy way to do this oft-asked question:
Code:
Function LastRowInColumn(intCol As Integer) As Integer On Error GoTo LRICError Application.Volatile 'This will make sure that this function is called if anything is changed on the Worksheet LastRowInColumn = Cells(Rows.Count, intCol).End(xlUp).Row ExitFnxn: Exit Function 'If there's an error in this function, then return an error to Excel LRICError: LastRowInColumn = CVErr(xlErrNA) Resume ExitFnxn End Function

For example, if you want to determine the last row in column B that has data, just call it like this:

Code:
Dim X as Integer X = LastRowInColumn(2)

Thanks to Russel Hauf for the code suggestion!
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey

Last edited by Mill; 12-20-2002 at 07:29 AM.
  #4  
Old 09-12-2002, 03:30 PM
MillExcel FAQ - Excel 101 Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default Lesson 3 - Having items in one ComboBox filter items in another ComboBox

For this example, I'm not really using a ComboBox. I'm actually using the Validation option programatically.

When the user changes the company in cell B1, cell C1 will automatically have a Validation to make sure that only employee names from that company are shown.

To do this, I the following code in the code of Sheet1:
Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$1" Then FilterList End If End Sub
Attached Files
File Type: zip extreme validation example.zip (11.2 KB, 665 views)
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
  #5  
Old 09-18-2002, 03:59 PM
MillExcel FAQ - Excel 101 Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default Lesson 4 - Same as Lesson 3, only actually using ComboBoxes

Here's one quick and easy way of accomplishing this task which is asked so often.

On this worksheet, clicking the button brings up a form. Selecting a different option in the first ComboBox changes what is available in the second ComboBox.
Attached Files
File Type: zip linked comboboxes.zip (8.3 KB, 1182 views)
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey

Last edited by Mill; 09-20-2002 at 07:34 AM.
  #6  
Old 10-08-2002, 07:19 AM
MillExcel FAQ - Excel 101 Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default Lesson 5 - Allow the user to select a file or a directory

Here is code to allow a user to pick a directory using Windows' dialogue boxes:

Code:
Public Type BROWSEINFO hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type '32-bit API declarations Declare Function SHGetPathFromIDList Lib "shell32.dll" _ Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long Declare Function SHBrowseForFolder Lib "shell32.dll" _ Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long 'This function will bring up a form to let the user select a directory Function GetDirectory(Optional Msg) As String Dim bInfo As BROWSEINFO Dim path As String Dim r As Long, X As Long, i As Integer ' Root folder = Desktop bInfo.pidlRoot = 0& ' Title in the dialog If IsMissing(Msg) Then bInfo.lpszTitle = "Select a folder." Else bInfo.lpszTitle = Msg End If ' Type of directory to return bInfo.ulFlags = &H1 ' Display the dialog X = SHBrowseForFolder(bInfo) ' Parse the result path = Space$(512) r = SHGetPathFromIDList(ByVal X, ByVal path) If r Then i = InStr(path, Chr$(0)) GetDirectory = Left(path, i - 1) Else GetDirectory = "" End If End Function

That code is not my own. I believe it came from this site but I'm not positive.

And here is code to allow the user to select a file using Windows' dialogue boxes:

Code:
FileName = Application.GetOpenFilename()

Obviously, that one is a built-in Excel function. Note that it only returns a filename. You have to do the actual reading of the file yourself.
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey

Last edited by Timbo; 04-21-2005 at 02:48 PM. Reason: Edit request by author
  #7  
Old 10-09-2002, 03:39 PM
MillExcel FAQ - Excel 101 Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default Lesson 6 - Public Functions

If you make a function Public in Excel, you can call it from a cell.

Here is an example of a public function:

Code:
'Return the name of the Sheet from which the function was called Public Function GetSheetName() As String GetSheetName = Application.Caller.Parent.Name End Function

If you want to force your function to be called every time the sheet that calls it is calculated, just add the line Application.Volatile to the function.
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
  #8  
Old 10-09-2002, 03:41 PM
MillExcel FAQ - Excel 101 Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default Lesson 7 - Find Errors in Formulas of a Sheet

Here is a handy procedure to find errors in a sheet. It's the same as using the Goto method.

Code:
Sub FindErrors() 'If no errors are found, it would cause an error on the Select statement On Error GoTo FEError ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors).Select Exit Sub FEError: MsgBox "No Errors Found", , "Clear!" Exit Sub End Sub
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
  #9  
Old 12-02-2002, 07:32 AM
MillExcel FAQ - Excel 101 Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default Lesson 8 - Finding the Last Row in a Sheet

Here is a quick and easy function to get the last row that contains data in a worksheet.

Code:
Function GetLastRow(SheetID) As Long Dim LastRow As Long If Application.WorksheetFunction.CountA(Worksheets(SheetID).Cells) = 0 Then LastRow = 1 Else LastRow = Worksheets(SheetID).UsedRange.Rows.Count + Worksheets(SheetID).UsedRange.Row While Application.WorksheetFunction.CountA(Worksheets(SheetID).Rows(LastRow)) = 0 LastRow = LastRow - 1 Wend End If GetLastRow = LastRow End Function

Thanks to Beryl for the tip about blank rows at the top of a worksheet. I also changed the argument of the function to allow either the name or the number of the sheet.

Error-checking should probably be added, but it's good enough for now.
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey

Last edited by Colin Legg; 08-17-2009 at 06:08 PM.
  #10  
Old 01-10-2003, 07:54 AM
MillExcel FAQ - Excel 101 Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default Lesson 9 - ScrollRow and ScrollColumn

Every now and then someone will ask how to position a certain cell in the upper left corner of the screen. There IS a way to do it, but finding it in the VBA help is not easy.

Here's how to do it:

Code:
'Position the worksheet so that cell M14 is in the upper left corner Worksheets(1).Select ActiveWindow.ScrollRow = 14 ActiveWindow.ScrollColumn = 13

In my opinion, these two properties are VERY underdocumented.

Russel Hauf also suggests this method:

Code:
Application.GoTo Range("G10"), True
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey

Last edited by Mill; 01-13-2003 at 11:58 AM.
  #11  
Old 03-13-2003, 08:11 AM
MillExcel FAQ - Excel 101 Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default Lesson 10 - Adding your own CommandBar

Here is the code to allow you to add a CommandBar (floating/dockable toolbar) in Excel:

Code:
Sub AddToolBar() Dim cmdbar As CommandBar Dim CmdBtn1 As CommandBarButton Dim strTBName As String strTBName = "MyToolbar" 'If the toolbar is already there, then don't try to add it again If CheckForToolbar(strTBName) Then Exit Sub Set cmdbar = CommandBars.Add(Name:=strTBName, Position:=msoBarTop, Temporary:=True) cmdbar.Visible = True With cmdbar 'We'll put the toolbar at the right edge of the "Standard" toolbar .Left = CommandBars("Standard").Width .RowIndex = CommandBars("Standard").RowIndex Set CmdBtn1 = .Controls.Add(msoControlButton, , , , True) With CmdBtn1 .Style = msoButtonCaption .Caption = "MyToolbar" .TooltipText = "This is my sample toolbar" .OnAction = "HelloWorld" End With End With Set cmdbar = Nothing Set CmdBtn1 = Nothing End Sub Function CheckForToolbar(argName As String) As Boolean Dim bar As CommandBar, Result As Boolean Result = False For Each bar In CommandBars If bar.Name = argName Then Result = True End If Next bar CheckForToolbar = Result End Function Sub HelloWorld() Msgbox "Hello World!" End Sub

This toolbar is temporary, which means it won't stay after you close Excel. It WILL stay if you just close the workbook, however. Therefore, I've used the CheckForToolbar function to make sure that we don't add the same toolbar again.

The AddToolbar procedure should be called from the Workbook_Open event.
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
  #12  
Old 03-13-2003, 08:34 AM
MillExcel FAQ - Excel 101 Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default Lesson 11 - Adding your own menus

Here is some sample code that will allow you to add your own menu items in Excel. As in my last post, I've put a check function to make sure that we don't add the menu if it already is there.

Code:
Option Explicit Sub AddMenuItems() 'Our menu item as it will appear on the menu bar Dim mnuNew As CommandBarControl 'Some properties of our new menu Dim strMenuName As String, strMenuCaption As String 'Some dummy sub-menu items Dim mnuTest1 As CommandBarControl, mnuTest2 As CommandBarControl, mnuTest3 As CommandBarControl 'The position where we want to put our new menu item Dim intPosition As Integer strMenuName = "MyMenu" strMenuCaption = "&My Menu" 'Don't add the things twice If CheckForMenu(strMenuCaption) Then Exit Sub With CommandBars("Worksheet Menu Bar") 'Let's put our new menu to the immediate left of the "Window" menu For Each mnuNew In .Controls If mnuNew.Caption = "&Window" Then intPosition = mnuNew.Index Next mnuNew 'See VBA help file for CommandBarControls' Add method for more info Set mnuNew = .Controls.Add(msoControlPopup, , , intPosition, True) With mnuNew .Caption = strMenuCaption .Visible = True Set mnuTest1 = .Controls.Add(msoControlButton, , , , True) With mnuTest1 .Caption = "Test&1" .OnAction = "MySub1" End With Set mnuTest2 = .Controls.Add(msoControlPopup, , , , True) 'mnuTest2 will actually be a parent menu with mnuTest3 as its own sub-menu With mnuTest2 .Caption = "Test&2" .BeginGroup = True Set mnuTest3 = .Controls.Add(msoControlButton, , , , True) With mnuTest3 .Caption = "Test&3" .OnAction = "MySub2" End With End With End With End With 'Kill all of the object variables Set mnuNew = Nothing Set mnuTest1 = Nothing Set mnuTest2 = Nothing Set mnuTest3 = Nothing End Sub Function CheckForMenu(argCaption) As Boolean Dim bar As CommandBarPopup, Result As Boolean Result = False With CommandBars("Worksheet Menu Bar") For Each bar In .Controls If bar.Caption = argCaption Then Result = True End If Next bar End With CheckForMenu = Result End Function Sub MySub1() MsgBox "MySub1" End Sub Sub MySub2() MsgBox "MySub2" End Sub
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey
  #13  
Old 07-17-2003, 08:47 AM
Timbo's Avatar
TimboExcel FAQ - Excel 101 Timbo is offline
Green-Eyed

Super Moderator
* Guru *
 
Join Date: May 2001
Location: Bangkok, Thailand
Posts: 10,261
Default Compare the contents of two worksheets

I've seen a few worksheet comparison routines, but most seem pretty resource-hungry as they loop through all the cells in the worksheets being tested - here's an attempt to get Excel to do the work
Code:
'creates a new worksheet in the workbook specified, populating it with an array of formula's which compare ' the contents of the worksheets specified. ' Matched blank cells show no result. Other matches return TRUE OR FALSE 'Arguments:: 'strWorkbookName$ : Name of workbook containing worksheets to compare 'strSheetName1$ : Name of first sheet to compare 'strSheetName2$ : Name of second sheet to compare Sub WorksheetValuesCompare(strWorkbookName$, strSheetName1$, strSheetName2$) Dim shtNew As Worksheet Dim rngFormula As Range Dim strFormula$ Dim lngRow1&, lngRow2&, lngCol1&, lngCol2& Dim lngRow&, lngCol& Const FORMULA_STRING$ = "=IF(AND(ISBLANK(INDIRECT(ADDRESS(ROW(),COLUMN(),1,,""One"")))," & _ "ISBLANK(INDIRECT(ADDRESS(ROW(),COLUMN(),1,,""Two"")))),""""," & _ "AND(NOT(ISNA(MATCH(INDIRECT(ADDRESS(ROW(),COLUMN(),1,,""One""))," & _ "INDIRECT(ADDRESS(ROW(),COLUMN(),1,,""Two"")),0)))))" With Workbooks(strWorkbookName$) 'return row/column counts lngRow1& = UBound(.Sheets(strSheetName1$).UsedRange.Value) lngRow2& = UBound(.Sheets(strSheetName2$).UsedRange.Value) lngCol1& = UBound(.Sheets(strSheetName1$).UsedRange.Value, 2) lngCol2& = UBound(.Sheets(strSheetName2$).UsedRange.Value, 2) 'retain highest counts If lngRow1& > lngRow2& Then lngRow2& = lngRow1& If lngCol1& > lngCol2& Then lngCol2& = lngCol1& 'return row/column counts lngRow1& = LBound(.Sheets(strSheetName1$).UsedRange.Value) lngRow& = LBound(.Sheets(strSheetName2$).UsedRange.Value) lngCol1& = LBound(.Sheets(strSheetName1$).UsedRange.Value, 2) lngCol& = LBound(.Sheets(strSheetName2$).UsedRange.Value, 2) 'retain lowest counts If lngRow1& > lngRow& Then lngRow1& = lngRow& If lngCol1& > lngCol& Then lngCol1& = lngCol& Set shtNew = .Sheets.Add(.Sheets(1)) End With 'substitute required worksheet names strFormula$ = Replace(FORMULA_STRING$, """One""", """" & strSheetName1$ & """") strFormula$ = Replace(strFormula$, """Two""", """" & strSheetName2$ & """") 'populate new analysis worksheet with formula With shtNew .Range(.Cells(lngRow1&, lngCol1&), .Cells(lngRow2&, lngCol2&)).Formula = strFormula$ .Calculate End With Finish: On Error Resume Next Set rngFormula = Nothing Set shtNew = Nothing Exit Sub End Sub
And if you're wondering what all the "$" are about:
Data Type Symbols

Last edited by Timbo; 08-11-2004 at 05:21 AM. Reason: Formatting lost in forum transfer
  #14  
Old 08-21-2003, 10:37 AM
Timbo's Avatar
TimboExcel FAQ - Excel 101 Timbo is offline
Green-Eyed

Super Moderator
* Guru *
 
Join Date: May 2001
Location: Bangkok, Thailand
Posts: 10,261
Default

There are lots of examples of how to use the Find method of the Range object around, but only a few offer a compact generic solution.
The following function is intended to search a specified range for a list of values, then return an array containing the count of each occurrance... not 100% generic but you get the idea!
Code:
'function arguments are: a variant array of values to find 'aryFind', and ' a range object to search in 'rngSearch' 'returns an array of type Long containing the count of each array item found in the range Function Get_ValueCount(aryFind As Variant, rngSearch As Range) Dim FindThis Dim rngFound As Range Dim aryFound() Dim lngCountFind&, lngCountFound& Dim strFirstAddress$ 'loop each element of the array for values to find For Each FindThis In aryFind 'resize the array to hold the count of the values found ReDim Preserve aryFound(lngCountFind&) With rngSearch 'position the search at the start of the range Set rngFound = rngSearch.Cells(1, 1) 'reset variables lngCountFound& = 0 strFirstAddress$ = "" Do 'use the Find method to locate the value in the range Set rngFound = .Find(What:=FindThis, After:=rngFound) If Not rngFound Is Nothing Then 'the value was found, halt the process if the method has "wrapped" ' to the start of the range If rngFound.Address = strFirstAddress$ Then Exit Do 'retain the address of the first value found If strFirstAddress$ = "" Then strFirstAddress$ = rngFound.Address 'increment counter for found values lngCountFound& = lngCountFound& + 1 End If 'Find method returns Nothing if there was no matching value Loop While Not rngFound Is Nothing aryFound(lngCountFind&) = lngCountFound& End With lngCountFind& = lngCountFind& + 1 Next FindThis 'assign the array to the function return value Get_ValueCount = aryFound Finish: Erase aryFound End Function
__________________
"He's not the Messiah. He's a very naughty boy!" - Brian's mum

Can't find the answer? >> Try something new!
Become a Professional
  #15  
Old 09-25-2003, 11:07 AM
Timbo's Avatar
TimboExcel FAQ - Excel 101 Timbo is offline
Green-Eyed

Super Moderator
* Guru *
 
Join Date: May 2001
Location: Bangkok, Thailand
Posts: 10,261
Default Creating Application level event procedures (and other classes!)

Classes, we all need them from time to time and by the time I've found one of the many threads on how to make one, I may as well have re-written it myself!
I stumbled upon Mike_R's tutorial-esque example and decided it deserved a place in here. Credits to Mike_R!
Quote:
(1) Start by creating a Class Module and call it, say, "clsMyAppEventTrap".

(2) In the 'clsMyAppEventTrap' class module, put the following code, keeping in mind that the "xlAppEvent" in the following could be named whatever you wanted, as long as you were consistent in all uses, including in the name "xlAppEvent_SheetActivate":
Code:
Option Explicit Public WithEvents xlAppEvent As Excel.Application Private Sub xlAppEvent_SheetActivate(ByVal SH As Object) ' Yourcode goes here. ' ' You can use the provided ByVal SH parameter. ' For example: ' (1) SH.Name would return the Sheet's Name ' (2) SH.Parent.Name would return the Sheet's Workbook Name. ' End Sub
By the way, at this point, you could click on the Proceedure Drop-Down box, and see a listing of all the possible application events, e.g., "SheetCalculate", "SheetChange", "WindowResize", etc... Choosing one will create a stub for that function, which you can then fill in.


(3) In a standard module you will need to put the following. In the code below, "xlApplicationEvent" and the "TurnMyTrappingOn()" sub can be named anything you want, but the reference to "As New clsMyAppEventTrap" and the call to ".xlAppEvent" must be consistent with the names you chose in step 2, above.
Code:
Option Explicit Public xlApplicationEvent As New clsMyAppEventTrap Sub TurnMyTrappingOn() Set xlApplicationEvent.xlAppEvent = Excel.Application End Sub
(4) Lastly, some code, somewhere, must call the TurnMyTrappingOn() subroutine. Until it is called, nothing will happen. Typically this call would be put in the WB's Auto_Open or Workbook_Open() sub.
Then there's a list on another handy site, of all the available events:
http://www.cpearson.com/excel/events.htm
__________________
"He's not the Messiah. He's a very naughty boy!" - Brian's mum

Can't find the answer? >> Try something new!
Become a Professional
  #16  
Old 12-08-2003, 09:01 AM
MillExcel FAQ - Excel 101 Mill is offline
Ultimate Contributor

Retired Moderator
* Guru *
 
Join Date: Feb 2000
Location: Michigan, USA
Posts: 2,643
Default How do I stop a workbook from asking to be saved after Application.Quit command?

There's three ways of doing this.

1. Specify in your code whether you want to save changes or not. For example:
Code:
Workbooks(x).Close Savechanges:=True

2. Turn off Excel's warning messages.
This is equivalent to saying No, so changes to the workbook will not be saved. This approach can be somewhat dangerous, since you might miss genuine warnings, so it's good practice to turn them off immediately before the line that otherwise triggers the warning, and turn them back on immediately after.
Code:
Application.DisplayAlerts = False Workbooks(x).Close Application.DisplayAlerts = True

3. Make Excel believe that the workbook has been saved already.
Again, this means that changes you've made until now won't be saved. However if you make further changes to the workbook after this, and then try to close the workbook, the warning will come up again.
Code:
Workbooks(x).Saved = True Workbooks(x).Close

Credit for this code (and the idea to post it here) goes to Herilane. The blame can go to her too. - Mill
__________________
"The face of a child can say it all, especially the mouth part of the face." - Jack Handey

Last edited by Mill; 12-08-2003 at 09:11 AM.
  #17  
Old 03-25-2004, 01:12 AM
Timbo's Avatar
TimboExcel FAQ - Excel 101 Timbo is offline
Green-Eyed

Super Moderator
* Guru *
 
Join Date: May 2001
Location: Bangkok, Thailand
Posts: 10,261
Default Further Excel Resources & Links

Quote:
Here are some Books and URL Links as recommended by the *Experts* here at Extreme VB Talk. We've even graded the books on a scale of 1-10.

Keep in mind that most of the books are available in older versions such as for Excel 2000 or Excel '97. In these cases, if you do not need the absolute latest information available, you can often save a good deal of money by buying an older, out-of-print or used edition.

Note, especially, the Excel Worksheet Function Reference, which is completely unchanged since Excel '95. Buying the newer versions will only cost you more money, whereas if one can locate a copy of the Excel'97 version, it can typically be had for no more than $US 10.00.

Books on Excel VBA
(1) Excel 2002 VBA by John Green, Stephen Bullen, et. al. (10.0)
(2) Definitive Guide to Excel VBA by Michael Kofler. (10.0)
(3) Excel 2002 Power Programming with VBA by John Walkenbach. (9.5)
(4) Microsoft Office XP Developers Guide (8.0)

Books on Excel usage (no VBA)
(1) Microsoft Excel Inside Out Version 2002 by Stinson & Dodge (10.0)
(2) Microsoft Excel 2002 Bible by John Walkenbach (10.0)
(3) Excel 2002 (or 2000 or '97) Worksheet Function Reference (8.0)

Recommended URL Links
(1) The Spreadsheet Page (J-Walk)
(2) Pearson Software Consulting, LLC
(3) Stephen Bullen's Excel Page
(4) Erlandsen Data Consulting
(5) MSDN: Writing Custom Classes in VBA
(6) Microsoft: All about Custom Classes in VBA
Thanks to XVBT Expert, Mike_R for initiating this glossary!

Last edited by Timbo; 12-05-2004 at 12:33 PM. Reason: Content update
  #18  
Old 08-17-2009, 05:53 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default Excel 2010

As we near the release of Excel 2010 I thought it would be a good idea to have some official links to the exciting news and previews.


The Microsoft Excel Team Blog
http://blogs.msdn.com/excel/archive/...0/default.aspx

In particular, have a look at:
Project Gemini
Sparklines
Conditional Formatting Improvements


The Microsoft Office Product Development Group Blog
http://blogs.technet.com/office2010/

There's plenty of information about the new Backstage view and security improvements in Office 2010.


The Microsoft Office Web Apps Development Team Blog
http://blogs.msdn.com/officewebapps/


Introducing Microsoft Office 2010 Technical Preview
http://www.microsoft.com/office/2010/

Last edited by Colin Legg; 08-18-2009 at 05:13 AM.
Closed Thread


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Excel FAQ - Excel 101
Excel FAQ - Excel 101
Excel FAQ - Excel 101 Excel FAQ - Excel 101
Excel FAQ - Excel 101
Excel FAQ - Excel 101
Excel FAQ - Excel 101 Excel FAQ - Excel 101 Excel FAQ - Excel 101 Excel FAQ - Excel 101 Excel FAQ - Excel 101 Excel FAQ - Excel 101 Excel FAQ - Excel 101
Excel FAQ - Excel 101
Excel FAQ - Excel 101
 
Excel FAQ - Excel 101
Excel FAQ - Excel 101
 
-->