Owner: VBA Tips & Tricks URL:http://vbadud.blogspot.com/ Join Date: Fri, 20 Jul 2007 21:52:46 -0500 Rating:0 Site Description: BA related information will be posted on this blog. Of late, VBA has been disregarded by many software professionals for .Net, c# and other technologies. This blog will also post articles related to them too Site statistics:Click here
Get the Height & Width of Shapes / Figures in Word Document (Word VBA) 2007-07-08 07:57:00 Height & Width
of Shapes / InlineShapes in Word Document (Word VBA)Figures
are embedded in the word document and when they move they are a nemesis. Programmers are often given the job of aligning, resizing etc. Here is a simple example to get the height of the Shapes. Here you will notice that a particular inline shape has been singled out. You will get to know this if you run this in the document:)Sub Figure_Attributes() Dim sRep sRep = "" For I = 1 To ActiveDocument.InlineShapes.Count If ((ActiveDocument.InlineShapes(I).Type > 0 And ActiveDocument.InlineShapes(I).Type 7 And ActiveDocument.InlineShapes(I).Type < 18)) Then Height = ActiveDocument.InlineShapes(I).Height Width1 = ActiveDocument.InlineShapes(I).Width ActiveDocument.InlineShapes(I).Select If Selection.Fields.Count = 0 Then sRep = sRep & fname & vbTab & Height & vbTab & Width1 & vbCr End If End If Next I For I = 1 Read more:Height
Paragraph & Character Styles in Word Document (Word VBA) 2007-07-08 07:46:00 Export CharacterStyles
from Word Document / Export Paragraph Styles from Word DocumentHere is the simple macro for extracting character & paragraph styles from a Word document. This exports the styles to a new word documentSub Export_Styles_In_Document() Dim oSource As Document Dim oRep As Document Dim oPara As Paragraph Set oRep = Documents.Add Set oSource = ActiveDocument For I = 1 To oSource.Styles.Count If oSource.Styles(I).Type = wdStyleTypeParagraph Then SType = "Para" oRep.Bookmarks("EndOfDoc").Select Set oPara = oRep.Paragraphs.Add Set oPara = oRep.Paragraphs.Add oPara.Range.Text = SType & ": " & oSource.Styles(I).NameLocal Else SType = "Char" End If Next I For I = 1 To oSource.Styles.Count If oSource.Styles(I).Type = wdStyleTypeParagraph Then SType = "Para" Else SType = "Char" oRep.Bookmarks("EndOfDoc").Select Set oPara = oRep.Paragraphs.Add
Visual Basic Common Dialog 2007-06-19 03:02:00 Opening Files with CommonDialog
Common Dialog not only replaces three controls (Drive, Directory and FileList), but also is easier to program. It is supported in VisualBasic
and VBA as well. The new VB.NET has the same functionality in the OpenFileDialog classLet us have a small form created for explaining CommonDialog. Let us have a small form with a Text Box and a Command Button. On Clicking the Command Button, the selected file should be displayed in the Text BoxSample Form:To use the CommonDialog you need to include the component to your project. You can do so as follows:Once The component is included, the CommonDialog will be displayed in the ToolBoxDrag the CommonDialog to the form. You will see a small rectangle there. CommonDialog is visible in the Design time only (it is not visible during runtime)Add the following code to show the CommonDialog box and show the selected file in the text boxPrivate Sub Command1_Click()CommonDialog1.DialogTitle = "Select the File..."CommonDialo Read more:Visual Basic
Excel VBA - install an Excel Add-in (XLA or XLL) 2007-06-18 20:54:00 Adding Addins Automatically using VBA Most of today's Excel
VBA code are as Addins (XLA or XLL). As an organization progresses there comes many revisions for the Addin - hence the need to update the program.Here is a simple way to add a new addin:Sub Add_an_Addin()Dim oAddin As AddInDim oTempBk As WorkbookSet oTempBk = Workbooks.AddSet oAddin = oXL.AddIns.Add("E:CostBenefit1.0.xla", True)oAddin.Installed = TrueoTempBk.CloseEnd SubIf you wonder why a temporary workbooks is added - it is because to avoid the Run-time error '1004': Unable to get the Add property of the AddIns class or Run-time error '1004': Add method of addins class failed exceptions that are raised when there are no workbooks. Just be safe!! Read more:install
Show All Processes using VBA 2007-06-18 20:49:00 Get All Processes using Win API Functions'DeclarationsConst TH32CS_SNAPHEAPLIST = &H1Const TH32CS_SNAPPROCESS = &H2Const TH32CS_SNAPTHREAD = &H4Const TH32CS_SNAPMODULE = &H8Const TH32CS_SNAPALL = (TH32CS_SNAPHEAPLIST Or TH32CS_SNAPPROCESS Or TH32CS_SNAPTHREAD Or TH32CS_SNAPMODULE)Const TH32CS_INHERIT = &H80000000Const MAX_PATH As Integer = 260Private Type PROCESSENTRY32 dwSize As Long cntUsage As Long th32ProcessID As Long th32DefaultHeapID As Long th32ModuleID As Long cntThreads As Long th32ParentProcessID As Long pcPriClassBase As Long dwFlags As Long szExeFile As String * MAX_PATHEnd TypePrivate Declare Function CreateToolhelp32Snapshot Lib "kernel32" (ByVal lFlags As Long, ByVal lProcessID As Long) As LongPrivate Declare Sub CloseHandle Lib "kernel32" (ByVal hPass As Long)' API Functions to get the processesPrivate Declare Function Process32First Lib "kernel32" (ByVal hSnapShot As Long, uProcess As PROCESSENTRY32) As LongPrivate Declare Func
Run a VB6.0 Executable from Excel/Word 2007-06-13 21:40:00 Run an Executable from Excel
VBA / Word VBAIf you need to use some grid for showing data / use the feautres in Visual Basic 6.0 that arenot available in VBA, you can create the application in VB6.0 or anyother program and show the User Interface in VBA codeSub Run_VB6App_FromWord()--- Some VBA Code heresCmd = "C:Program FilesMyFile.exe"vntResult = OpenProcess(PROCESS_QUERY_INFORMATION, False, Shell(sCmd, 1))GetExitCodeProcess vntResult, lngExitCode' -----------------------------------------------------------' Coded by Shasur for http://vbadud.blogspot.com' -----------------------------------------------------------Do GetExitCodeProcess vntResult, lngExitCode DoEventsLoop While lngExitCode = STILL_ACTIVE--- some more VBA CodeEnd SubThe above program will show the MyFile executable till the user clicks OK/Cancel. Once the application is closed the control will return to the calling VBA programThis used WinAPI FunctionsPublic Declare Function GetExitCodeProcess Lib "kernel32" (By
VBA Read Text Files (With Leading & Trailing Spaces) 2007-06-13 21:29:00 Read Data frm Text Files (VBA)Reading Text Files using VBA is one of the major development activity of programmers. There are multiple ways to read a file1. Input # Statement2. Input Function3. Get Function4. File System Object FunctionsInput # StatementDim MyString, MyNumberOpen "c: est.txt" For Input As #1 ' Open file for input.Do While Not EOF(1) ' Loop until end of file. Input #1, MyString, MyNumber ' Read data into two variables. Debug.Print MyString, MyNumber ' Print data to the Immediate window.LoopClose #1 ' Close file.However, the bug here is Input # does not take the leading or trailing spaces with it. That is, ' My Name is ' becomes 'My Name is'. This will not be the correct one as we need to get the spaces alsoThen Input function comes handyDim MyCharOpen "c: est.txt" For Input As #1 ' Open file.Do While Not EOF(1) ' Loop until end of file. MyChar = Input(1, #1) ' Get one character. Debug.Print MyChar ' Print to the Immediate wind Read more:Leading
Extract Procedure Names from all Modules - VBA 2007-06-13 21:18:00 Count No of Programs in a WorkbookWorkbooks have Macros, If you need to know if the Workbook contains macros and then the following proc will get you the macros in the workbookSub Extract_Program()Dim VBP As VBProjectDim VBModule As CodeModuleDim VBProc As VBComponentDim sLastProcName As StringDim arProcName() As StringDim iProcCount As IntegerSet VBP = ThisWorkbook.VBProjectFor Each VBM In VBP.VBComponents Set VBModule = VBM.CodeModule i = 1 Do Until i >= VBModule.CountOfLines procname = VBModule.ProcOfLine(i, vbext_pk_Proc) i = i + 1 If LenB(procname) 0 Then If procname sLastProcName Then iProcCount = iProcCount + 1 ReDim Preserve arProcName(iProcCount) arProcName(iProcCount) = procname sLastProcName = procname End If End If LoopNext' List all proceduresFor i = 1 To UBound(arProcName) MsgBox arProcName(i)Next iEnd Sub Read more:Procedure
, Names
, Modules
ColorIndex - Coloring Excel Sheet Cells 2007-06-13 21:08:00 Highlight Color in Excel
CellsColorindex is used to color the background of Excel Cells Cells(1, 2).Interior.ColorIndex = 30etcHere is the entire list of colors you can use: Read more:Coloring
Excel VBA - Delete Empty Rows 2007-06-13 21:04:00 Delete Rows without ValuesHere is a primitive simple function to delete rows that does not contain any value (I have taken Cols 1 to 10) for consideration.Sub Delete_UnWanted_Rows()For Each SHT In Sheets SHT.Activate iMax = SHT.Cells.SpecialCells(xlCellTypeLastCell).Row For i2 = 2 To iMax For i1 = 1 To 10 If LenB(SHT.Cells(i2, i1)) 0 Then GoTo TakeNextRow End If Next i1 SHT.Rows(i2).EntireRow.Delete TakeNextRow: Application.StatusBar = SHT.Name & " " & i2 Next i2TakeNextSht: Next SHTApplication.StatusBar = FalseEnd SubYou can do the same with Special Cells - LastCell also Read more:Excel
, Empty
Setting Default & Cancel Buttons in VBA/Visual Basic 2007-06-08 21:58:00 VB/ VBA SettingDefault
& CancelButtons
through codePrivate Sub Form_Load()'Sets cmdOK as the button control that is clicked when the user presses the Enter key.cmdOK.Default = True'Sets cmdCancel as the button control that is clicked when the user presses the ESC key.cmdCancel.Cancel = TrueEnd SubFor doing the same in VB.Net Refer : http://dotnetdud.blogspot.com/2007/06/vbnet-setting-default-cancel-buttons.html Read more:Visual
, Basic
, Visual Basic
Assigning Shortcut Keys - Excel Macros 2007-06-02 22:03:00 Shortcut Key Assignment for SubroutinesIt is always nice to have keyboard shortcuts for executing functions/subroutines rather than having to click the menu and its commandOne way will to link the macro to a command button and assign the shortcut for the button. The other one is to assign the shortcut to the function using Application.OnKeyOnKey method executes a specified procedure when a particular key or key combination is pressedApplication.OnKey "%b", "ToWord"is used to trigger the "ToWord" subroutine whenever Alt+b is pressed . Percentage symbol is used to substitute Alt keyUse the Caret (^) to symbol for Ctrl key and plus (+) for ShiftkeyApplication.OnKey "^b", "ToWord"Is for Ctrl + bOther keys are : Key Code BACKSPACE {BACKSPACE} or {BS} BREAK {BREAK} CAPS LOCK {CAPSLOCK} CLEAR {CLEAR} DELETE or DEL {DELETE} or {DEL} DOWN ARROW {DOWN} END {END} ENTER (numeric keypad) {ENTER} ENTER ~ (tilde) ESC {ESCAPE} or {ESC} HELP {HELP} HOME {HOME} INS {INSERT} LEFT ARROW {LEF Read more:Excel
Using Function Keys in Visual Basic Forms 2007-06-02 21:35:00 Function keys as Shortcut Keys in VBFunction keys are a boon for assigning shortcuts. They have more advantage than the regular Alt + or Ctrl + combination.Function keys can be assigned to command buttons using simple tricks as explained below. For that we need to instuct the VB to handle them in the Keydown event by setting the Keypreview = TrueThen you can have the necessary shortcut keys on the formIn the Form_KeyDown event redirect to necessary functions/procs based on the keyPrivate Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)Select Case KeyCode Case vbKeyF2 Call Proc_Fund_Transfer Case vbKeyF3 Call Proc_Credit_Card End SelectEnd Sub Read more:Visual
, Basic
, Visual Basic
, Forms
Save copy of the workbook 2007-05-27 02:41:00 Sub Saving_A_Copy_Of_Workbook()'Saves a copy of the workbook to a file but doesn't modify the open workbook in memoryDim bAchievedTarget As BooleanIf bAchievedTarget = True Then ActiveWorkbook.SaveCopyAs "c:CompletedFinalCopy.xls"End If' This function can be used to take the copy of the file while working on it. The current file remains the same howeverEnd Sub
Print Multiple Sheets using VBA 2007-05-27 02:40:00 'VBA to Select MultipleSheets
Sub Print
_Selected_Sheets() ' Remove Multiple Selections ActiveWorkbook.Sheets(1).Select ' Select Multiple Sheets - Optional False is to extend the current selection to include any previously selected objects and the specified object ActiveWorkbook.Sheets(1).Select False ActiveWorkbook.Sheets(2).Select False ActiveWorkbook.Sheets(4).Select False' -----------------------------------------------------------' Coded by Shasur for http://vbadud.blogspot.com' ----------------------------------------------------------- ActiveWindow.SelectedSheets.PrintOut Copies:=1 ' Remove Multiple Selections ActiveWorkbook.Sheets(1).SelectEnd Sub
VBA Dir Function to Get Sub Directories 2007-05-27 02:38:00 Get Sub Directories
using VBA Dir Function The below function is used to get the immediate sub-directories for a given directory. If you want to dig deep into the directory structure then you need to iterate the sub-directories as wellSub Get_All_SubDirectories()Dim arSubDir() As StringDim sSubDir As StringsSubDir = GetSubDir("d: rash")' -----------------------------------------------------------' Coded by Shasur for http://vbadud.blogspot.com' -----------------------------------------------------------If LenB(sSubDir) 0 Then arSubDir = Split(sSubDir, ";") For i1 = 0 To UBound(arSubDir) Debug.Print arSubDir(i1) Next i1End IfEnd SubFunction GetSubDir(ByVal sPath As String, Optional ByVal sPattern As Variant) As VariantDim sDir As StringDim sDirLocationForText As StringOn Error GoTo Err_ClkIf Right$(sPath, 1) "" Then sPath = sPath & ""If IsMissing(sPattern) Then sDir = Dir$(sPath, vbDirectory)Else sDir = Dir$(sPath & sPattern, vbDirectory)End If' --------
Insert Procedure to a Module Using VBComponents 2007-05-26 21:47:00 Insert Procedure
to a Module
Using VBComponentsSub Insert_PRocedure_To_BasModule()' This program will need reference to Microsoft Visual Basic for Extensibility LibraryDim VBP As VBProjectDim VBC As VBComponentDim VBMod As CodeModule' -----------------------------------------------------------' Coded by Shasur for http://vbadud.blogspot.com' -----------------------------------------------------------Workbooks.AddSet VBP = ActiveWorkbook.VBProjectSet VBC = VBP.VBComponents.Add(vbext_ct_StdModule)VBC.Name = "MyMacro"Set VBMod = VBC.CodeModuleVBMod.InsertLines 3, "Sub NewProc()" & Chr(13) & _" Msgbox ""Welcome to VB Component Programming"" " & Chr(13) & _"End Sub"End SubDynamic Insertion of Procedure Bas Module, Dynamic Creation of Function / Procedure in BAS/Class Module, Automatic Creation of Function / Procedure in BAS/Class Module, Create New Module using VBA, VBA Create Module, VBA Attach Module to Workbook
Delete Module on the Fly using VBA 2007-05-26 21:45:00 VBA Delete Bas Module
/ Class Modules Sub Delete_BasModule_To_WorkBook()' This program will need reference to Microsoft Visual Basic for Extensibility LibraryDim VBP As VBProjectDim VBC As VBComponentDim VBMod As CodeModule' -----------------------------------------------------------' Coded by Shasur for http://vbadud.blogspot.com' -----------------------------------------------------------Set VBP = ActiveWorkbook.VBProjectSet VBC = VBP.VBComponents("MyMacro")' Delete the moduleActiveWorkbook.VBProject.VBComponents.Remove VBCEnd Sub Dynamic Deletion of Bas Module, Dynamic Deletion of Bas Module, Automatic Deletion of Bas Module, Delete Module using VBA, VBA Delete Module, VBA Detach Module to Workbook
Insert User Form on the Fly 2007-05-26 21:43:00 Automatic Creation of User FormSub Insert_Form_To_WorkBook()' This program will need reference to Microsoft Visual Basic for Extensibility LibraryDim VBP As VBProjectDim VBC As VBComponentDim VBMod As CodeModule' -----------------------------------------------------------' Coded by Shasur for http://vbadud.blogspot.com' -----------------------------------------------------------Workbooks.AddSet VBP = ActiveWorkbook.VBProjectSet VBC = VBP.VBComponents.Add(vbext_ct_MSForm)VBC.Name = "frmMyForm"End Sub Dynamic Insertion of User Form, Dynamic Creation of User Form, Automatic Creation of User Form, Dynamically Create New User Form using VBA, VBA Create Module, VBA Attach Module to Workbook
Insert Class Module on the Fly 2007-05-26 21:41:00 Dynamic Insertion of Class Module
Sub Insert_Class_Module_To_WorkBook()' This program will need reference to Microsoft Visual Basic for Extensibility LibraryDim VBP As VBProjectDim VBC As VBComponentDim VBMod As CodeModule' -----------------------------------------------------------' Coded by Shasur for http://vbadud.blogspot.com' -----------------------------------------------------------Workbooks.AddSet VBP = ActiveWorkbook.VBProjectSet VBC = VBP.VBComponents.Add(vbext_ct_ClassModule)VBC.Name = "clsMyClass"End Sub Dynamic Insertion of Class Module, Dynamic Creation of Class Module, Automatic Creation of Class Module, Create New Module using VBA, VBA Create Module, VBA Attach Module to Workbook
Insert Module on the Fly 2007-05-26 21:38:00 Dynamic Insertion of Bas Module
using VBComponentsMany a times we need to create Workbook through program and add some code to the workbook. If the both workbook and code are standard and do not change, we could use some template. On the other hand, if your requirement has constantly changing workbook and a relatively unstable code you can't use templates.You need to rely on the VBComponent programming to dynamically create the Module in the WorkbookSub Insert_BasModule_To_WorkBook()' This program will need reference to Microsoft Visual Basic for Extensibility LibraryDim VBP As VBProjectDim VBC As VBComponentDim VBMod As CodeModule' -----------------------------------------------------------' Coded by Shasur for http://vbadud.blogspot.com' -----------------------------------------------------------Workbooks.AddSet VBP = ActiveWorkbook.VBProjectSet VBC = VBP.VBComponents.Add(vbext_ct_StdModule)VBC.Name = "MyMacro"End SubDynamic Insertion of Bas Module, Dynamic Creation of Bas Modul
Match Excel Column against a Standard Range 2007-05-14 21:16:00 Replace Values of a Column
when it Match
esIf you want to match the names in column2 against
the standard names in column 1 & replace by the standard names the following should help:Sub Replace_TExt() For i = 1 To ActiveSheet.Range
("B:B").Cells.SpecialCells(xlCellTypeLastCell).Row If Trim(ActiveSheet.Range("B" & i)) "" Then ActiveSheet.Range("A:A").Replace What:=ActiveSheet.Range("B" & i), Replacement:=ActiveSheet.Range("B" & i).Value, _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ False, ReplaceFormat:=False End If Next iEnd Sub Read more:Excel
, Standard
Dynamic Copy of Matching Excel Data 2007-05-14 20:58:00 Copy specific data in cells from Master Sheet to Current SheetMost often we would have the entire data in Excel
and would require data corresponding to the cell value taken from the master sheet and populated in the current one dynamically.In the following example the master sheet is named as "DB" and contains all records with the primary key being the first column.Function Snippet_For_Copy(sSearchString) If Trim(sSearchString) = "" Then Exit Function With Sheets("DB").Columns("A:A") Set rFindCell = .Find(sSearchString, LookIn:=xlValues, LookAt:=xlWhole) If Not rFindCell Is Nothing Then Sheets("DB").Rows(rFindCell.Row).EntireRow.Copy _ Destination:=Range("A" & ActiveCell.Row) End If End WithEnd FunctionIf the user enters a data in the first column of the current sheet, the above function will check the data in the DB sheet and transfer entire row if a match is foundYou can trigger the function using Worksheet_SelectionChange eventPr Read more:Dynamic
Add ToolTipText in CommandBar Controls 2007-05-07 21:09:00 Display ToolTipText in CommandBar ControlsSub Show_ToolTipText_In_Controls()Dim oCB As CommandBarDim oCtl As CommandBarControlOn Error Resume Next' Delete Existing Command BarCommandBars("MyProject").Delete'Create New Command BarSet oCB = CommandBars.AddoCB.Name = "MyProject"oCB.AdaptiveMenu = TrueSet oCtl = oCB.Controls.Add(Type:=msoControlButton)oCtl.Caption = "Show Message Box"oCtl.TooltipText = "This is a sample"oCtl.OnAction = "Display_Msg_Box"oCtl.SetFocus' Show the Command BaroCB.Visible = True' Place the CommandBar at the bottom of the screenoCB.Position = msoBarBottomEnd SubSub Display_Msg_Box()MsgBox "You have clicked me!!!"End Sub
Add Combo Box to the command Bar 2007-05-07 21:05:00 Add Combo
Box to the command
BarSub Show_Combo_CommandBar()Dim oCB As CommandBarDim oCtl As CommandBarComboBoxOn Error Resume Next'Delete Control From CommandBarCommandBars("Sample Command Bar").DeleteSet oCB = CommandBars.AddoCB.Name = "Sample Command Bar"oCB.AdaptiveMenu = True'Add Control to CommandBarSet oCtl = oCB.Controls.Add(Type:=msoControlComboBox)oCtl.Caption = "ComboSamp"'Link Macro to CommandBar,oCtl.OnAction = "Change_Header_Background"'Add list Item to Combo Box Control oCtl.AddItem "NoColor"oCtl.AddItem "Blue"oCtl.AddItem "Yellow"' Show the Command BaroCB.Visible = True' Place the CommandBar at the bottom of the screenoCB.Position = msoBarBottom End SubSub Change_Header_Background()' Acts based on the value in the Combo BoxDim oCB As CommandBarDim oCtl As CommandBarComboBoxOn Error Resume NextSet oCB = CommandBars("Sample Command Bar")Set oCtl = oCB.Controls("ComboSamp")If oCtl.ListIndex -1 Then Select Case oCtl.ListIndex Case 1
Excel Height of Row - RowHeight using Excel VBA 2007-07-25 21:35:00 RowHeight
property is used to get the HeightReturns the height of all the rows in the range specified, measured in points (point: Unit of measure referring to the height of a printed character. A point equals 1/72 of an inch, or approximately 1/28 of a centimeter.). Returns null if the rows in the specified range aren’t all the same height. Read/write Variant.You can use the Height property to return the total height of a range of cells.Sub Change_Header_Row_Height()MsgBox "RowHeight = " & Range("A1").RowHeight _& vbCrLf & "Height = " & Range("A1").HeightRange("A1").RowHeight = 90End SubDifferences between RowHeight and Height include the following:Height is read-only.If you return the RowHeight property of several rows, you will either get the row height of each of the rows (if all the rows are the same height) or null (if they’re different heights). If you return the Height property of several rows, you will get the total height of all the rows.Range("A1").Height = 90 woul Read more:Excel
Detecting duplicate values (Excel VBA) 2007-08-12 21:36:00 Check presence of values in a column/range using Excel
VBAMost often a programmer would be given a job of Insert/Update scenarion in EXcel. That is, Insert a new row if a specific value does not exist; if it does then update some. So the process is to check for existence of a specific valueHere is a generic function ;Sub CheckForExistence()myVal = "Sample"myRange = "A:A"If Check_Val_Existence(myVal, myRange) = True Then MsgBox "Value exists"End IfEnd SubThis used the Find Method. This method finds specific information in a range, and returns a Range object that represents the first cell where that information is found. Returns Nothing if no match is found.Function Check_Val_Existence(ByVal sText, ByVal sRange) As BooleanDim rFnd As RangeDim sText As StringSet rFnd = ActiveSheet.Range(sRange).Find(What:=sText, LookAt:=xlPart)If Not rFnd Is Nothing Then Check_Val_Existence = TrueElse Check_Val_Existence = FalseEnd IfEnd Function
Automatically Event Repeat in Excel VBA (OnTime Method) 2007-08-12 21:27:00 VBA Code that can run at a fixed time can be done using the OnTime Method.OnTime Method schedules a procedure to be run at a specified time in the future (either at a specific time of day or after a specific amount of time has passed).expression.OnTime(EarliestTime, Procedure, LatestTime, Schedule) expression Required. An expression that returns an Application object. EarliestTime Required Variant. The time when you want this procedure to be run. Procedure Required String. The name of the procedure to be run. LatestTime Optional Variant. The latest time at which the procedure can be run. For example, if LatestTime is set to EarliestTime + 30 and Microsoft Excel
is not in Ready, Copy, Cut, or Find mode at EarliestTime because another procedure is running, Microsoft Excel will wait 30 seconds for the first procedure to complete. If Microsoft Excel is not in Ready mode within 30 seconds, the procedure won’t be run. If this argument is omitted, Microsoft Excel will wa Read more:Automatically
, Repeat
Disable Cut & Copy from Popup menu (Excel VBA/Word VBA) 2007-08-12 21:22:00 Here is a simple way to disable the Cut & Copy in the Popup
menuSub Disable
_Buttons()Dim oC1 As CommandBarSet oC1 = Application.CommandBars("CELL")oC1.Controls("Cu&t").Enabled = FalseoC1.Controls("&Copy").Enabled = FalseEnd SubOther menu items can also be handled similarlyTo disable the Insert & Delete in the Popup menuoC1.Controls("&Insert...").Enabled = FalseoC1.Controls("&Delete...").Enabled = False Read more:Excel
ShutDown Windows using VBA 2007-08-21 09:45:00 VBA Function to Logoff /VBA Function to Restart Windows
Option Explicit' Win API DeclarationsConst MF_BYPOSITION = &H400&Private Const EWX_LOGOFF = 0Private Const EWX_SHUTDOWN = 1Private Const EWX_REBOOT = 2Private Const EWX_FORCE = 4Private Declare Function ExitWindowsEx Lib "user32.dll" ( _ ByVal uFlags As Long, _ ByVal dwReserved As Long) As LongUse the function with atmost caution, you will not be warned by Windows for Shutdown / Restart. Save all your work before trying this example:)Function Common_ShutDown_Logoff()'Shutdown WindowsCall ExitWindowsEx(EWX_SHUTDOWN, 0)'Restart WindowsCall ExitWindowsEx(EWX_REBOOT, 0)'logoff WindowsCall ExitWindowsEx(EWX_LOGOFF, 0)End Function
Recommend Site To A Friend
