AW: variable Textbox-Nummerierung
11.03.2008 10:35:47
Stephan
Hier ist der Code:
Sub userform_activate()
Set datei3 = Excel.Application.Workbooks("Berechnung.xls").Sheets("tabelle1")
Set datei6 = Excel.Application.Workbooks("Berechnung.xls").Sheets("tabelle2")
With datei3
With datei6
Rem ---------------------------------------------------------------------------------------- _
Rem Textbox "Wertpapier" setzen
Rem ---------------------------------------------------------------------------------------- _
For Spalte = 1 To datei6.Cells(1, Columns.Count).End(xlToLeft).Column
Spalte = Spalte + 8
Set Textbox = Wertpapierabfrage.Frame1.Controls.Add("Forms.Textbox.1", True)
With Textbox
.Top = (Spalte / 9 - 1) * 16
.Left = 0
.Width = 240
.Height = 16
.Font.Size = 8
.Font.Bold = False
.Value = datei6.Cells(1, Spalte - 8)
.BackStyle = transparent
End With
Rem ---------------------------------------------------------------------------------------- _
Rem Textbox "ISIN" setzen
Rem ---------------------------------------------------------------------------------------- _
Set Textbox = Wertpapierabfrage.Frame1.Controls.Add("Forms.Textbox.1", True)
With Textbox
.Top = (Spalte / 9 - 1) * 16
.Left = 240
.Width = 80
.Height = 16
.Font.Size = 8
.Font.Bold = False
.Value = datei6.Cells(1, Spalte - 8)
.Value = datei6.Cells(1, Spalte - 7)
.TextAlign = 2
.BackStyle = transparent
End With
Rem ---------------------------------------------------------------------------------------- _
Rem Textbox "Stück" setzen
Rem ---------------------------------------------------------------------------------------- _
Set Textbox = Wertpapierabfrage.Frame1.Controls.Add("Forms.Textbox.1", True)
With Textbox
.Top = (Spalte / 9 - 1) * 16
.Left = 320
.Width = 50
.Height = 16
.Font.Size = 8
.Font.Bold = False
.Value = datei6.Cells(1, Spalte - 8)
.Value = Format(datei6.Cells(datei6.Cells(Rows.Count, Spalte - 4).End(xlUp).Row, _
Spalte - 4), "##,##0.000")
.TextAlign = 3
.BackStyle = transparent
End With
Rem ---------------------------------------------------------------------------------------- _
Rem Textbox "Depotführendes Institut" setzen
Rem ---------------------------------------------------------------------------------------- _
Set Textbox = Wertpapierabfrage.Frame1.Controls.Add("Forms.Textbox.1", True)
With Textbox
.Top = (Spalte / 9 - 1) * 16
.Left = 370
.Width = 150
.Height = 16
.Font.Size = 8
.Font.Bold = False
.Value = datei6.Cells(1, Spalte - 8)
.Value = datei6.Cells(1, Spalte - 6)
.BackStyle = transparent
End With
Rem ---------------------------------------------------------------------------------------- _
Rem Textbox "Depotinhaber" setzen
Rem ---------------------------------------------------------------------------------------- _
Set Textbox = Wertpapierabfrage.Frame1.Controls.Add("Forms.Textbox.1", True)
With Textbox
.Top = (Spalte / 9 - 1) * 16
.Left = 520
.Width = 100
.Height = 16
.Font.Size = 8
.Font.Bold = False
.Value = datei6.Cells(1, Spalte - 8)
.Value = datei6.Cells(1, Spalte - 5)
.TextAlign = 2
.BackStyle = transparent
End With
Rem ---------------------------------------------------------------------------------------- _
Rem Textbox "Depotnummer" setzen
Rem ---------------------------------------------------------------------------------------- _
Set Textbox = Wertpapierabfrage.Frame1.Controls.Add("Forms.Textbox.1", True)
With Textbox
.Top = (Spalte / 9 - 1) * 16
.Left = 620
.Width = 80
.Height = 16
.Font.Size = 8
.Font.Bold = False
.Value = datei6.Cells(1, Spalte - 8)
.Value = datei6.Cells(1, Spalte - 4)
.TextAlign = 2
.BackStyle = transparent
End With
Rem ---------------------------------------------------------------------------------------- _
Rem Textbox "Kurs" setzen
Rem ---------------------------------------------------------------------------------------- _
Set Textbox = Wertpapierabfrage.Frame1.Controls.Add("Forms.Textbox.1", True)
With Textbox
.Top = (Spalte / 9 - 1) * 16
.Left = 700
.Width = 80
.Height = 16
.Font.Size = 8
.Font.Bold = False
End With
Next Spalte
Rem --------------------------------------------------------------------------------------- _
Rem Formular dimensionieren
Rem --------------------------------------------------------------------------------------- _
Me.Top = 200
If (Spalte / 9 - 1) * 16 > 340 Then Me.Frame1.ScrollBars = fmScrollBarsVertical
If (Spalte / 9 - 1) * 16 > 340 Then Me.Frame1.ScrollHeight = (Spalte / 9) * 16 - 1
If (Spalte / 9 - 1) * 16 > 340 Then Wertpapierabfrage.Frame0.Width = 795
If (Spalte / 9 - 1) * 16 > 340 Then Wertpapierabfrage.TextBox7.Width = 92
If (Spalte / 9 - 1) * 16 > 340 Then Wertpapierabfrage.Frame1.Height = 340 Else _
Wertpapierabfrage.Frame1.Height = Spalte / 9 * 16 + 2
If (Spalte / 9 - 1) * 16 > 340 Then Me.Height = 420 Else Me.Height = Spalte / 9 * 16 + 83
If (Spalte / 9 - 1) * 16 > 340 Then Wertpapierabfrage.Frame1.Width = 795 Else _
Wertpapierabfrage.Frame1.Width = 783
If (Spalte / 9 - 1) * 16 > 340 Then Me.Width = 815 Else Me.Width = 798
If (Spalte / 9 - 1) * 16 > 340 Then Wertpapierabfrage.CommandButton1.Top = 370 Else _
Wertpapierabfrage.CommandButton1.Top = Spalte / 9 * 16 + 33
End With
End With
End Sub
Ziel ist es, den einzugebenden jeweiligen Kurs auszulesen.
Danke.
Stephan