Posts

Es werden Posts vom November, 2008 angezeigt.

Cell - Read - Closed Files!

From closed Workbooks certain cells are selected and summed up. The cells which can be selected are indicated in Sheet2 in column A. Some lines in the code must be adapted. These are characterized. In the ZIP file are example files. It functions immediately, if the file with the code is in the same folder as the files with the cells which must be read in. Subfolders are considered. The files at the end of the article are Excelfiles of the version 2003 and 2007.
The following code belonged In "Module1"

Option Explicit
OptionPrivateModule
Const strSheet AsString="Sheet1"'adapt
PublicSub Files_Read()
Dim stCalc As XlCalculation
Dim strDir AsString
Dim objFSO AsObject
Dim objDir AsObject
OnErrorGoTo Fin
With Application
.ScreenUpdating =False
.AskToUpdateLinks =False
.EnableEvents =False
stCalc =.Calculation
.Calculation = xlCalculationManual
.DisplayAlerts =False
EndWith
Set objFSO =CreateObject("Scripting.FileSystemObject")
strDir = ThisWorkbook.Path 'ada…

Pictures centers!

Cliparts in one worksheet, or in all worksheets are centered vertically and horizontal in the cell. The pictures can be aligned also again left above. The individual examples differed only in the kind, how the worksheet is addressed. The files at the end of the article are Excelfiles of the version 2003 and 2007.
The following code belonged In "Module1"

Option Explicit
PublicSub Picture_Center_Index()
Dim shpPicture As Shape
With ThisWorkbook.Worksheets(1)
ForEach shpPicture In .Shapes
If shpPicture.Type = msoPicture Then
shpPicture.Left= shpPicture.Left+_
(shpPicture.TopLeftCell.Width -_
shpPicture.Width)/2
shpPicture.Top = shpPicture.Top +_
(shpPicture.TopLeftCell.Height -_
shpPicture.Height)/2
EndIf
Next shpPicture
EndWith
EndSub
PublicSub Picture_Center_Name()
Dim shpPicture As Shape
With ThisWorkbook.Worksheets("Sheet1")
ForEach shpPicture In .Shapes
If shpPicture.Type = msoPicture Then
shpPictur…

Check Boxes from Form Controls!

Information about check boxes from form controls. You can check, reset, move, create and other things. The "Create Button" is to demonstrates that also. e.g. for a COMMANDBUTTON (ActiveX Controls) in the worksheet the code to set or reset the hook has to be changed. The files at the end of the article are Excelfiles of the version 2003 and 2007.
The following code belonged in "Module1"
Option Explicit
Sub CHECK_Formular_CheckBox()
Dim shpBox As Shape
ForEach shpBox In ActiveSheet.Shapes
If shpBox.Type <> msoOLEControlObject Then
If shpBox.FormControlType = xlCheckBox Then
shpBox.ControlFormat.Value =1
EndIf
EndIf
Next
EndSub
Sub Reset_Formular_CheckBox()
Dim shpBox As Shape
ForEach shpBox In ActiveSheet.Shapes
If shpBox.AlternativeText Like "Box*"Then
shpBox.ControlFormat.Value =False
EndIf
Next
EndSub
Sub Reset_Formular_CheckBox_1()
Dim shpBox As Shape
ForEach shpBox In ActiveSheet.Shapes
If shpBox.FormControlType = xlCheckBox Then