Tuesday, August 21, 2007
' Win API Declarations
Const MF_BYPOSITION = &H400&
Private Const EWX_LOGOFF = 0
Private Const EWX_SHUTDOWN = 1
Private Const EWX_REBOOT = 2
Private Const EWX_FORCE = 4
Private Declare Function ExitWindowsEx Lib "user32.dll" ( _
ByVal uFlags As Long, _
ByVal dwReserved As Long) As Long
Use the function with atmost caution, you will not be warned by Windows for Shutdown / Restart. Save all your work before trying this example:)
Call ExitWindowsEx(EWX_SHUTDOWN, 0)
Call ExitWindowsEx(EWX_REBOOT, 0)
Call ExitWindowsEx(EWX_LOGOFF, 0)
Const MF_BYPOSITION = &H400&
Private Declare Function GetSystemMenu Lib "user32" _
(ByVal hwnd As Long, _
ByVal bRevert As Long) As Long
Private Declare Function RemoveMenu Lib "user32" _
(ByVal hMenu As Long, _
ByVal nPosition As Long, _
ByVal wFlags As Long) As Long
Public Sub DisableCloseWindowButton(frm As Form)
Dim hSysMenu As Long
'Get the handle of the Window
hSysMenu = GetSystemMenu(frm.hwnd, 0)
'Disable the close button of the Form
RemoveMenu hSysMenu, 6, MF_BYPOSITION
'Remove the seperator bar
RemoveMenu hSysMenu, 5, MF_BYPOSITION
Sunday, August 12, 2007
Most 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 value
Here is a generic function ;
myVal = "Sample"
myRange = "A:A"
If Check_Val_Existence(myVal, myRange) = True Then
MsgBox "Value exists"
This 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 Boolean
Dim rFnd As Range
Dim sText As String
Set rFnd = ActiveSheet.Range(sRange).Find(What:=sText, LookAt:=xlPart)
If Not rFnd Is Nothing Then
Check_Val_Existence = True
Check_Val_Existence = False
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 wait until the procedure can be run.
Schedule Optional Variant. True to schedule a new OnTime procedure. False to clear a previously set procedure. The default value is True.
Now + TimeValue(time) to schedule something to be run when a specific amount of time (counting from now) has elapsed. Use
TimeValue(time) to schedule something to be run a specific time.
Application.OnTime TimeValue("11:00:00"), "StartProc"
Application.OnTime TimeValue("13:00:00"), "EndProc"
Assuming that you have two procedures called StartProc and EndProc the procedures would ne executed at the specified time
Dim oC1 As CommandBar
Set oC1 = Application.CommandBars("CELL")
oC1.Controls("Cu&t").Enabled = False
oC1.Controls("&Copy").Enabled = False
Other menu items can also be handled similarly
To disable the Insert & Delete in the Popup menu
oC1.Controls("&Insert...").Enabled = False
oC1.Controls("&Delete...").Enabled = False