Posts

Es werden Posts vom 2008 angezeigt.

Range - InputBox - PowerPoint...

Bild
Mit folgendem Code wird ein Bereich (Auswahl per InputBox) nach PowerPoint kopiert. Eingefügt als Link und als Bild. Automatisch im ermittelten TMP-Ordner gespeichert. Die beiden Bilder oben sind aus dem Objektexplorer (F2 in VBE) von PowerPoint und zeigen die Möglichkeiten des einfügens. Die Dateien am Ende des Beitrages sind in der Version für Excel 2003 und >=2007.
OptionExplicitPrivateDeclareFunctionGetTempPathLib"kernel32"Alias_"GetTempPathA"(ByValstrBufferLengthAsLong,ByVal_lpBufferAsString)AsLongConststrPPSaveAsString="Test.ppt"'--------------------------------------------------------------------------' Module : Module1' Procedure : Test' Author : © Case (Ralf Stolzenburg)' Date : 25.12.2008' Purpose : Range - InputBox - nach PowerPoint...'--------------------------------------------------------------------------PublicSubTest()DimstrFileNameAsStringDimobjPPRangeAsObjectDimobjPPAppAsObjectDimobjSlideAsObjec…

Search Word!

All files (*.doc) of a selectable folder - with subfolders - ar scanned for a term. If the term is found, the files are linked and listed. The files at the end of the article are Excelfiles of the version 2003 and 2007.
Link:
http://vbanet.blogspot.com/2011/03/worddateien-durchsuchen-auch-mit.html
The following code belonged in "Module1"

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
Const strSearchTMP AsString="Calculation"
Const strEXT AsString="*.doc"
Private strList()AsString
Private objWDApp AsObject
Private lngCount AsLong
Private objFSO AsObject
PublicSub Test()
Dim strListing AsString
Dim strDirOld AsString
lngCount =0
OnErrorGoTo Fin
strDirOld$ =String(255,0)
Call GetCurrentDirectory(255, str…

TextBoxes/ComboBoxes by Tab change!

With class programming you can change by "Tab" from a TextBox OR a ComboBox to the next, even if you new TextBoxes or ComboBoxes insert. In this case you must store the file, close and start again, or start the Sub "Private Sub Workbook_Open ()" again. If you "Shift" keep pressed you changed backwards. The TextBoxes or the ComboBoxes are in a worksheet NOT in a UserForm. The files at the end of the article are Excelfiles of the version 2003 and 2007.
The following code belonged In "ThisWorkbook"

Option Explicit
Private objTextBox()As clsTextBox
PrivateSub Workbook_Open()
Dim objOLEObject As OLEObject
ForEach objOLEObject In Worksheets("Sheet1").OLEObjects 'adapt
If objOLEObject.progID ="Forms.TextBox.1"Then
intIndex = intIndex +1
Redim Preserve objTextBox(1To intIndex)
Set objTextBox(intIndex)=New clsTextBox
Set objTextBox(intIndex).mobjTextBox =_
objOLEObject.Object
EndIf
Next objOLEObject
Sheet1.TextBo…

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