VBA

Question

Hallo,

Ich bin absolut der fast NULL KENNER in VBA

Ich habe eine Bestellliste in der Form in Excel

Markus Paul Simon und Peter bestellen Bier und Wurst pro Tag. Falls einer sich als
Abwesend meldet (ComoboBox auf Ja) soll automatisch seine Bestellung Bier und
Wurst auf 0 (NULL) gestzt werden. und Damit wird auch die Summe aktualisert.

Bier Wurst Abwesend(ComboBoxen "Ja" oder "Nein")
Markus 3 7 Nein
Paul 5 5 Nein
Simon 0 0 Ja
Peter 9 6 Nein
--------------------------------------
Summe X Y

mit fertigen Skripten habe ich mir die ComboBoxen mit schleifen eingebaut,
gebastelt

So sieht es aus
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
' Liegt in Modul

Sub NewCboBox()
Dim cbo As OLEObject
Dim i As Integer

For i = 1 To 5
Set cbo = ActiveSheet.OLEObjects.Add( _
ClassType:="Forms.ComboBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=Range("H1").Left + 1, _
Top:=55 + 15.75 * i, _
Width:=59, _
Height:=15.6)
cbo.Object.AddItem "Ja"
cbo.Object.AddItem "Nein"

cbo.LinkedCell = Cells(i, 1).Value '---------------> geht nicht (1)
cbo.Object.ListIndex = 1
Next i

End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Mein Problem:
Wie kann es bewirken, dass bei einer Veränderung des CBox auf Ja die
Entsprechende Zellen auf die jeweiligen Zeilen zu 0 (NULL) gestzt werden.

Ich habe den Code ohne Schleife probiert und es läuft aber nur für bestimme Zelle

++++++++++++++++++++++++++++++++++++++++++++++++++++
' In Tabellenblatt
Private Sub ComboBox1_Change()

If ComboBox1.Value = "Ja" Then

Cells(1, 2) = 0
End If
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

für alle dh in Schleife geht nicht. Es ergibt fehler
BITTE wo ist der Fehler in dem folgenden Code

+++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub ComboBox1_Change()

Dim i As Integer

For i = 1 To 5
If ComboBox & i.Value = "Ja" Then

Cells(i, 2) = 0
End If
Next i
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++


Vielen Vielen Dank im Vorraus

Answer 1

ich baue Deine Datei nicht nacgh, die Zeit hast Dui schon investiert. Bei mir würde dass so aussehen.

Private Sub ComboBox1_Change()
If ComboBox1 = "Ja" Then
Range("A1:E23").ClearContents
End If
End Sub

Gruß Hajo

Answer 2

Hallo Hajo,

Vielen Dank schon mal für die schnelle Antwort.

Bei deiner Bedingung wird nur die ComboBox1 geprüft. Ich habe aber meine CBox
schleifenweise erzeugt und bei jedem Teilnehmer soll durch Cbox "Ja" oder "Nein
ausgewählt werden". Dh ich habe vile Cboxen.

Die Auswahl eines Cbox soll nur auf die entsprechende Zeile wirken.
Die Prüfung soll für jede Cbox passieren. Und Ich glaube es geht Schleifenweise.
Ich weiss aber leider nicht, wie ich es hinbekomme

Answer 3

dann hast Du also eine Klasse Programmiert. Dann benutze in der Klasse doch das Click Ereignis. Ich hätte dazu auf die Tag Eigenschaft den Bereich für die entsprechenden Combobox geschrieben.
Das ich die Datei nichtv nachbaue hatte ich schon geschrieben.
Ich sehe mir das auch nicht an. Klassenprogrammierung ist aufwendig.

Grußb Hajo

Answer 4

Hi,

verwende anstelle von ActiveX-Steuerelementen ein Formularsteuerelement. Schreibe dazu in ein allgemeines Moduls diesen Code:

Sub Ausfuehren()
With ActiveSheet.Shapes(ActiveSheet.Application.Caller)
If .ControlFormat.Value = 1 Then Cells(.TopLeftCell.Row, 2) = 0
End With
End Sub

der ausgeführt wird, wenn im Kombinationsfeld "Ja" ausgewählt wird.

Für das Erstellen der Kombinationsfelder kannst du diesen Code verwenden:

Sub KombinationsfelderErstellen()
Dim i As Integer
Dim cbbElement As DropDown
For i = 1 To 5
With ActiveSheet.DropDowns.Add(Range("H1").Left + 1, Rows(i + 5).Top, 59, 15, 6)
.ListFillRange = "$H$1:$H$2" '<== in diesen beiden Zellen steht "Ja" und "Nein" - bitte anpassen
.ListIndex = 1
.DropDownLines = 2
.OnAction = "Ausfuehren"
End With
Next i
End Sub

Den Inhalt der Auswahlliste "Ja" und "Nein" musst du in 2 Zellen hinterlegen (im Beispiel H1 und H2)

Bis später,
Karin

Answer 5

Hallo,

Karin ich muss dir mein Grosses Respekt gegen.
Mein erstes Versuch war erstmal mit DropDowns. Ich hatte aber zwei Wochen gekämpft wegen eines Codes
zum Löschen der Zellen, hier im dem Fall war der Modul Ausfuehren.

Ich habe aber einige Fragen zum Verständnis.

1 OnAction = "Ausfuehren"
Ist es das einen Aufruf? falls Ja wieso bleibt er überwachend. Das Skript Sub KombinationsfelderErstellen()
wurde doch längs per click ausgeführt und schläft wieder. Er sollte auch doch nicht nachträglich reagieren.
Falls nicht was bewirkt die OnAction.

2 a) With ActiveSheet.Shapes(ActiveSheet.Application.Caller)
Was bewirkt Application.Caller ?

b) If .ControlFormat.Value = 1 Then Cells(.TopLeftCell.Row, 2) = 0
Bei der Zeile erkenne ich nur If ...... Then den Rest nicht

Was ist das .ControlFormat


3 Wie die Variable TopLeftCell.Row spielt ist mir etwas hier wie Magie. Wie weis diese Variable, dass sie an
die gleiche Zeile wie der erste DropDown, die Links stehende Zelle manipulieren soll. Irgendwie hätte sie
den Wert "Rows(i + 5).Top" übernommen. Wie geht das?


4 Zum Schluss: Wenn ich "Ja" wähle wird die entsprechende Zelle zu NULL. Das ist schon mal gut. Jedoch
kann ich in die Zelle trotzdem schreiben was ich will, obwohl die Auswahl in DropDown noch auf "Ja" steht.

Meine Frage: Ist es möglich nachdem die Zelle zu Null gebracht wurde sie zu sperren, und sie wieder zu
entsperren sobald man "Nein" ausgewählt hat?


Vielen vielen Dank Karin

Answer 6

Hi,

zu Frage 1: mit OnAction wird dem Steuerelement das Makro zugewiesen welches ausgeführt werden soll - das ist quasi dasselbe, als wenn du von Hand dem Steuerelement ein Makro zuweist, nur dass es eben per VBA gemacht wird.

zu Frage 2a: Application.Caller gibt wieder, wie Code aufgerufen wurde - im konkreten Fall ist es das Steuerelement, welches als Shape angesprochen wird

zu Frage 2b: .ControlFormat ist eine Eigenschaft des Steuerelementes, über das man den ausgewählten ListIndex des Steuerelementes feststellen kann

zu Frage 3: .TopLeftCell ist die linke obere Ecke des Steuerelementes und mit .TopLeftCell.Row kann man die Zeile und mit .TopLeftCell.Column die Spalte der Zelle feststellen, auf der das Steuerelement liegt

zu Frage 4: ein Formular-Steuerelement hat zwar eine Zellverknüpfung, in die schreibt es aber nur den Listindex. Durch Änderung dieser Zelle kann man zwar das Kombinationsfeld "umstellen", aber das Ganze nützt im konkreten Fall nichts, da du ja irgendeinen Zahlenwert, der unabhängig vom ListIndex ist, eintragen willt. Wenn du also das Steuerelement auf Nein stellen willst durch Eintrag in die betreffende Zelle der Spalte B, dann musst du das Worksheet_Change Ereignis nutzen. Dabei musst du in einer Schleife über alle Kombinationsfelder laufen und prüfen, ob .TopLeftCell.Row mit Target.Row übereinstimmt - das ist dann das Steuerelment, für welches du per Code den ListIndex ändern musst

Um die Zelle zu sperren müsstest du den Code für das Steuerelement wie folgt abändern:

Sub Ausfuehren()
With ActiveSheet.Shapes(ActiveSheet.Application.Caller)
ActiveSheet.Unprotect
If .ControlFormat.Value = 1 Then
Cells(.TopLeftCell.Row, 2) = 0
Cells(.TopLeftCell.Row, 2).Locked = True
Else
Cells(.TopLeftCell.Row, 2).Locked = False
End If
ActiveSheet.Protect
End With
End Sub

Bis später,
Karin