Posts

Es werden Posts vom August, 2008 angezeigt.

Formula - Relatively - Absolutely!

With this code the formulas can be set relatively/absolutely - absolutely/relatively within a marked range in a worksheet. The code can be started over the combination of keys CTRL+F2. The files at the end of the article are Excelfiles of the version 2003 and 2007.
The following code belonged in "ThisWorkbook"

Option Explicit
PrivateSub Workbook_Open()
Application.OnKey "^{F2}","Module1.Relative_Absolute"
EndSub
PrivateSub Workbook_Deactivate()
Application.OnKey "^{F2}"
EndSub

The following code belonged In "Module1"

Option Explicit
PublicSub Relative_Absolute()
Dim rngRange As Range
Dim rngCell As Range
OnErrorGoTo Relative_Absolute_Error
Set rngRange = Application.InputBox _
(Prompt:="Mark a range!", Type:=8)
SelectCaseInStr(ActiveCell.Formula,"$")
Case0
ForEach rngCell In rngRange
rngCell.Formula = Application.ConvertFormula _
(Formula:=rngCell.Formula,_
FromReferenceStyle:=xlA1,_

Character font - Small - Large!

Over a key (F11 more largely - F12 smaller) the character font within the marked range is changed, between sizes 6 and 120.
The following code belonged in "ThisWorkbook"
Option Explicit
PrivateSub Workbook_Open()
Application.OnKey "{F11}","Module1.Large"
Application.OnKey "{F12}","Module1.Small"
EndSub
PrivateSub Workbook_Deactivate()
Application.OnKey "{F11}"
Application.OnKey "{F12}"
EndSub

The following code belonged In "Module1"

Option Explicit
PublicSub Large()
Dim bytWriting AsByte
OnErrorGoTo Large_Error
With Selection.Font
bytWriting =.Size
bytWriting = bytWriting +1
If bytWriting >120ThenExitSub
.Size = bytWriting
EndWith
OnErrorGoTo0
ExitSub
Large_Error:
MsgBox"Error "&Err.Number &" ("&Err.Description &")"
EndSub
PublicSub Small()
Dim bytWriting AsByte
OnErrorGoTo Small_Error
With Selection.Font
bytWriting =.Size
bytWriting = bytWr…

Marked comments save!

All comments within a marked range are stored in a file in the temp directory. You can select between data attach or file overwrite. The file is opened at the end. The following code belonged in "Module1"
Option Explicit
Private Declare Function ShellExecute Lib "shell32.dll"_
Alias "ShellExecuteA"(ByVal hwnd AsLong,ByVal lpOperation AsString,_
ByVal lpFile AsString,ByVal lpParameters AsString,_
ByVal lpDirectory AsString,ByVal nShowCmd AsLong)AsLong

Private Declare Function GetTempPath Lib "kernel32" Alias _
"GetTempPathA"(ByVal nBufferLength AsLong,ByVal_
lpBuffer AsString)AsLong
PublicSub Comment_TXT()
Dim intFileNumber AsInteger
Dim strComment AsString
Dim rngRange As Range
Dim rngCell As Range
OnErrorGoTo Comment_TXT_Error
intFileNumber =FreeFile
Reset
Set rngRange = Application.InputBox("Mark a range!",,"$C$1:$L$9", Type:=8)
SelectCaseMsgBox_
("Comments attach (Click YES), or file overwrite (Click NO)?",_

Sheet - Name - Index - CodeName!

Bild
Often in forums the question is asked, how a worksheet in VBA can be addressed. There are different possibilities. With the name, the index or the code name. The name is in the brackets - the code name before the brackets. In my example thus name (One) and code name (Sheet1). The index goes from top to bottom. Pay attention to it whether it in a "For Next loop" "Sheets" or "Worksheets" use, because "Sheets" means all sheets inclusive chart sheets. If you work with the name of the worksheet you have problems, if the worksheet is renamed, and if you work with the index you have problems, if the worksheet is shifted. If you work with the CodeName of the worksheet, it can be renamed and shifted. The files at the end of the article are Excelfiles of the version 2003 and 2007.
The following code belonged in "Module1"
Option Explicit
'Problem, if the worksheet is renamed.
PublicSub Name_Sheet_Name()
Dim wksSheet As Worksheet
'The code in …

Insert file with hyperlink - multiple choice!

With double or right-click can files - also several - with hyperlink be inserted. The API function "GetCurrentDirectory" and "SetCurrentDirectory" prevents change over the current directory since as indicated in the VBA help "Application.FileDialog" always change the current directory. In the second code the file with path is written into the comment. The files at the end of the article are Excelfiles of the version 2003 and 2007.
The following code belonged In "Sheet1"

Option Explicit
Private Declare Function GetCurrentDirectory Lib "kernel32"_
Alias "GetCurrentDirectoryA"(ByVal nBufferLength&,_
ByVal lpBuffer$)AsLong
Private Declare Function SetCurrentDirectory Lib "kernel32"_
Alias "SetCurrentDirectoryA"(ByVal lpPathName$)AsLong
PrivateSub Worksheet_BeforeDoubleClick(ByVal Target As Range,_
Cancel AsBoolean)
Dim strDirOld AsString
Dim strFile AsString
OnErrorGoTo Worksheet_Change_Error
strDirOld$…

Log a file - print, save, change, close!

Logs seems a popular topic to be. In the following example is logged open, close, save and print of a file. In addition changes on all worksheets are logged. However with multiple choice only up to a certain limit. Can be amended however in the code. If no limit is given here, perhaps then the macro runs itself to death - e.g. with mark a whole column, or evenly several columns. The files received depending upon event other names and are stored in determining temp directory. With "CTRL+ALT+F12" can ALL LOGS - which are stored in determined TEMP directory - be deleted. Possibly the file could with reaching a certain size or a certain number of lines to be deleted and/or a new file begin - that is your part. The files at the end of the article are Excelfiles of the version 2003 and 2007. The following code belonged in "ThisWorkbook".

Option Explicit
Dim varOldValue AsVariant
Private Declare Function GetTempPath Lib "kernel32" Alias _
"GetTempPathA"(By…