Sub TableofContentDim i As LongOn Error Resume NextApplication.DisplayAlerts = FalseWorksheets('Table of Content').DeleteApplication.DisplayAlerts = TrueOn Error GoTo Before:=ThisWorkbook.Worksheets(1)ActiveSheet.Name = 'Table of Content'For i = 1 To Sheets.CountWith Anchor:=ActiveSheet.Cells(i, 1), Address:=', SubAddress:=' & Sheets(i).Name & '!A1', ScreenTip:=Sheets(i).Name, TextToDisplay:=Sheets(i).NameEnd WithNext iEnd Sub. Sub SortWorksheetsDim i As IntegerDim j As IntegerDim iAnswer As VbMsgBoxResultiAnswer = MsgBox('Sort Sheets in Ascending Order?' & Chr(10) & 'Clicking No will sort in Descending Order', vbYesNoCancel + vbQuestion + vbDefaultButton1, 'SortWorksheets')For i = 1 To Sheets.CountFor j = 1 To Sheets.Count - 1If iAnswer = vbYes ThenIf UCase$(Sheets(j).Name) UCase$(Sheets(j + 1).Name) ThenSheets(j).Move After:=Sheets(j + 1)End IfElseIf iAnswer = vbNo ThenIf UCase$(Sheets(j).Name). , 'Error'Exit SubEnd IfSelection.PrintOutFrom:=startpage, To:=endpage, Copies:=1Collate:=TrueEnd Sub. Please try again.', 'Error'Exit SubEnd Ifendpage= InputBox('Please Enter End Page number.', 'EnterValue')If Not WorksheetFunction.IsNumber(endpage) ThenMsgBox'Invalid End Page number. Sub printCustomSelectionDim startpageAs IntegerDim endpageAs Integerstartpage= InputBox('Please Enter Start Page number.', 'EnterValue')If Not WorksheetFunction.IsNumber(startpage) ThenMsgBox'Invalid Start Page number. Close VBE and you are done.Remember that, by applying this macro you will not able to edit the cell by double click. Paste the code into it and select the “BeforeDoubleClick” from event drop down menu. Select your workbook & double click on the name of a particular worksheet in which you want to activate the macro. Go to Project Explorer (Ctrl + R, If hidden). I really love to use this macro code whenever I have to analyze a data table.Here are the quick steps to apply this code. They should be all saved to the same folder on your hard drive. The reason it uses the WorksheetCalculate Event and not the WorksheetChange is because a Validation list will not fire the WorksheetChange Event in Excel 97.This download contains four Workbooks. The code has been set so it works in both Excel. This Workbook shows how you can use the WorksheetCalculate Event and a Validation list to have formulas automatically reference different Workbooks.
0 kommentar(er)
